BigQueryでint32とint64の型の不一致で苦しんだ話

概要

CASE文を使って条件にマッチすればINT型のデータを、それ以外は0にしてSUMしようとしたら、型が違うと怒られたので、どう対処したかの備忘録。

要はこんな感じのクエリ。

SUM(CASE WHEN 条件 THEN 数(INT) ELSE 0 END) AS cnt

ちなみにデータがない場合、NULL値ではなく0を入れたかったのでこういう書き方をしている。

前提として、BigQueryはLegacySQLで書いている。

ちなみにテーブルの中身は超絶端折ってこんなイメージ。

user_id	created_at	id	count
1	"2017-12-29 10:00:00"	hoge	0
2	"2017-12-28 10:00:00"	fuga	100
3	"2017-12-29 10:00:00"	hoge	90
4	"2017-12-28 10:00:00"	fuga	0
5	"2017-12-29 10:00:00"	hoge	15

怒られた内容

まず、こんな感じのクエリを叩きました。

SELECT
  user_id
, DATE(DATE_ADD(created_at, 9, "HOUR")) AS dt
, SUM(CASE WHEN id = 'hoge' THEN count ELSE 0 END) AS hoge_count
FROM TABLE_NAME
GROUP BY user_id, dt
ORDER BY user_id ASC, dt DESC
;

するとこんなエラーが。THENとELSEの型は揃えてねと怒られる。

Error: CASE expects THEN and ELSE expressions to have similar types, but got different type int32 from ELSE expression which is not compatible with type uint64.

うーん、面倒くさいなーと思いIFNULLで回避しようと試みる。

SELECT
  user_id
, DATE(DATE_ADD(created_at, 9, "HOUR")) AS dt
, IFNULL(SUM(CASE WHEN id = 'hoge' THEN count ELSE NULL END), 0) AS hoge_count
FROM TABLE_NAME
GROUP BY user_id, dt
ORDER BY user_id ASC, dt DESC
;

また怒られる。

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

対処法

どうやらBigQueryのバグらしい。

https://issuetracker.google.com/issues/35905178

そういえば、これUNIONする時にたまーに出るやつや。

BigQueryはINTEGER型しか持たいないが内部的にはint32とint64に分かれているらしいので、そいつが悪さしているのかもしれない。

対処方法は、countをINTEGER()で囲ってあげたら怒られなくなった。

SUM(CASE WHEN id = 'hoge' THEN INTEGER(count) ELSE 0 END) AS hoge_count

ちなみに、今回テーブルは結構複雑なクエリを保存したviewテーブルを読み込んでいたので、何回もネストしているような複雑なクエリ等で内部的な型の不一致が発生しやすいのかも。（よくわかんないけど）