Correct use of COALESCE() in BigQuery

Question

I'm running the following script:

select COALESCE(test1,test2,test3)
from (select null as test1,'' as test2,'fdsda' as test3)

and receive the following error:

No matching signature for function COALESCE for argument types: INT64, STRING, STRING. Supported signature: COALESCE([ANY, ...]) at [1:8]

Any idea for the reason of this error?

Answer 1

When using COALESCE, you have to provide arguments of the same type. For example, if the first argument is a STRING, the other ones should also be STRINGs. In your case, you are trying to provide a INTEGER as the first parameter and STRINGs for the other ones.

Hope it helps

Answer 2

Use

select COALESCE(test1,test2,test3) from (select cast(null as string) as test1,'' as test2,'fdsda' as test3)

Anyway, I agree with rmesteves - different types in coalesce do not make sense.