BigQuery COALESCE/IFNULL type mismatch with literals

Question

In SQL I usually use COALESCE and IFNULL to ensure that I get numbers and not NULL when my queries contain aggregate functions like COUNT and SUM, for example:

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

However, in BigQuery I run into an error:

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

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

I've tried using CAST, but there's no unit64 type I can cast to, so I try INTEGER:

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

That gives me basically the same error, but at least I've successfully gotten a 64-bit zero instead of a 32-bit:

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

The same happens if I use INTEGER(0).

I can get it to work if I cast both arguments to INTEGER:

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

But now it starts to be verbose. Is this really how you're supposed to do it in BigQuery?

Answer 1

This is a bug in BigQuery which has been around for quite some time. For the time being you need to force the conversion of the COUNT, but you shouldn't need to do it for your "0".

The following should work:

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

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