5

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?

fragilewindows
  • 1,394
  • 1
  • 15
  • 26
Theo
  • 131,503
  • 21
  • 160
  • 205
  • 1
    You should only need the `INTEGER` around your `COUNT`, correct? Still not ideal as an implicit conversion should be easy enough for the engine, but so it goes... BTW, this might cast some light as well: http://stackoverflow.com/questions/11059778/convert-unsigned-int-to-signed-int-through-google-bigquery – Tom H Feb 11 '16 at 20:27
  • True, don't know why I didn't consider that, but should still be unnecessary. Thanks for the link – seems like the bug hasn't been fixed in a while :) – Theo Feb 11 '16 at 20:29
  • 2
    These bug reports seems relevant (1) https://code.google.com/p/google-bigquery/issues/detail?id=121 , (2) https://code.google.com/p/google-bigquery/issues/detail?id=375 – Kinaan Khan Sherwani Feb 11 '16 at 20:29
  • @KinaanKhanSherwani thanks, I'll have to keep an eye out for the fixes mentioned in those reports. – Theo Feb 11 '16 at 20:32
  • I've added an answer along with @Kinaan's link so that people searching for this in the future should be able to find an answer quickly instead of rooting through comments. – Tom H Feb 11 '16 at 20:47
  • 2
    In which case will `COUNT` return a NULL instead of 0? – dnoeth Feb 11 '16 at 21:16
  • @dnoeth good point. The application that generates the SQL wraps aggregate functions like this since many of them can return null, it doesn’t make an exception for `COUNT` beacause it would just complicate things. I should probably have used `SUM` as an example but I didn’t think of that. – Theo Feb 11 '16 at 21:55

1 Answers1

10

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.

fragilewindows
  • 1,394
  • 1
  • 15
  • 26
Tom H
  • 46,766
  • 14
  • 87
  • 128
  • Wouldn't it be better to cast both as BIGINT instead, 2.1 billion is not a large number considering BigQuery/BigData? – dnoeth Feb 11 '16 at 21:18
  • I don't believe that Bigquery has a "BIGINT" data type. Just the 64-bit INTEGER. I don't work with it very much, so I could be mistaken, but I believe that the original poster found the same thing. – Tom H Feb 11 '16 at 21:32