BigQuery COALESCE / IFNULL fuzziness with literals

Question

In SQL, I usually use COALESCEand IFNULLto provide numbers, and not NULLwhen my queries contain aggregate functions, such as COUNTand SUM, for example:

SELECT IFNULL(COUNT(foo), 0) AS foo_count FROM …

However, in BigQuery, I ran into an error:

Argument type mismatch in the IFNULL function: 'f0_' - type uint64, '0' - type int32.

Is there a way to make BigQuery understand that literal 0 should be interpreted as unit64in this context?

I tried using CAST, but there is no unit64type to which I can add, so I try INTEGER:

SELECT IFNULL(COUNT(foo), CAST(0 AS INTEGER)) AS foo_count FROM …

This gives me basically the same error, but at least I successfully got a 64-bit zero instead of 32-bit:

Argument type mismatch in IFNULL function: 'f0_' - type uint64, '0' - type int64.

The same thing happens if I use INTEGER(0).

I can make it work if I apply both arguments to INTEGER:

SELECT IFNULL(INTEGER(COUNT(foo)), INTEGER(0)) AS foo_count FROM …

But now it begins to be detailed. Is this really how you should do it in BigQuery?

Answer 1

This is a bug in BigQuery that has been around for quite some time. For now, you need to force the conversion COUNT, but you do not need to do this for your "0".

The following should work:

SELECT IFNULL(INTEGER(COUNT(foo)), 0) AS foo_count FROM

Thanks to @Kinaan Khan Sherwani for the link to the official bug report .