CODEWITHSUNDEEP
google-bigquery
Nikolay Novozhlov
Nikolay Novozhlov

Reputation: 365

BigQuery count(*) throws type mismatch error

While using results of count(*) in subquery BigQuery can return a mistake. Here is an example: 

SELECT
  SUM(IF(year%2=0, children, 0))
FROM (
  SELECT year, COUNT(1) AS children
  FROM [publicdata:samples.natality]
  GROUP BY year 
)

It will return:

Error: Argument type mismatch in function IF: 'children' is type uint64, '0' is type int32.

Somewhere inside the guts of BigQuery count(1) and 0 have different types and it returns a mistake.

Upvotes: 1

Views: 382

Answers (3)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

This is a bug in BigQuery type coercion. While several workarounds are possible - we have fixed the underlying issue inside BigQuery, and the fix will become available during next BigQuery release (I will update this answer when it is rolled out).

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

above example is out of practical context so sorry if below doesnt make sense to you - but you asked for options :o)

In this particular case you can use NULL. Of course it will not work if default value is not 0 

SELECT  
  SUM(IF(YEAR%2=0, children, NULL))  
FROM (  
  SELECT YEAR, COUNT(1) AS children  
  FROM [publicdata:samples.natality]  
  GROUP BY YEAR   
)

Another option to rewrite you query as below:

SELECT count(1) AS children   
FROM [publicdata:samples.natality]  
WHERE year%2=0

Upvotes: 1

Nikolay Novozhlov
Nikolay Novozhlov

Reputation: 365

Use sum(1) instead!

Always was fixing it hardcore way:

SELECT
  SUM(IF(year%2=0, INTEGER(children), 0))
FROM (
  SELECT year, COUNT(*) AS children
  FROM [publicdata:samples.natality]
  GROUP BY year
)

Just now realised that the best way is:

SELECT
  SUM(IF(year%2=0, children, 0))
FROM (
  SELECT year, sum(1) AS children
  FROM [publicdata:samples.natality]
  GROUP BY year 
)

Any concerns / better options?

Upvotes: 0

Related Questions