BigQueryの関数の返す型を調べる

Last updated at 2019-12-10Posted at 2019-12-09

BigQueryを使っていて気持ち悪いのは、関数の返り値の型がマニュアルに書いていないことが多い点です。

たとえばSIGN関数の説明は次のようなものです。

SIGN(X)

負、ゼロ、正の引数に対してそれぞれ -1、0、+1 を返します。浮動小数点引数では正と負のゼロは区別されません。NaN 引数に対しては NaN を返します。

これだけでは SIGN(0.0) の返り値の型が何なのかはわかりません。INT64を返すのかFLOAT64を返すのか考えると夜も眠れませんね。また、引数の型がNUMERICの場合はどうなるのでしょうか。

そもそもSQL言語全般の特徴として暗黙の型変換により型を意識しなくても不便しないことが多いわけですが、未知のバグを作らないためにも型を調べておくことは大事だと思います。

型の調べ方

関数の返り値の型が何かは次のような方法で調べられます（絶対もっといい方法があると思うんだけどわかりませんでした…）。

SELECT 1 AS id, [1] feature UNION ALL
SELECT 2 AS id, SIGN(0.0) AS feature

これを実行すると次のようなエラーが表示されます。

Column 2 in UNION ALL has incompatible types: ARRAY, DOUBLE at [2:1]

暗黙の型変換ができない型とUNIONしてエラーを出せば、エラーメッセージから関数の返り値の型がわかるというわけです。この場合ならSIGN(0.0)はFLOAT64型を返すとわかります。

また、次のようにSIGN関数の引数にINT64型とNUMERIC型を引数に取ってみると、返り値の型がそれぞれINT64とNUMERICだとわかります。

SELECT 1 AS id, [1] feature UNION ALL
SELECT 2 AS id, SIGN(0) AS feature

Column 2 in UNION ALL has incompatible types: ARRAY, INT64 at [2:1]

SELECT 1 AS id, [1] feature UNION ALL
SELECT 2 AS id, SIGN(CAST('0.0' AS NUMERIC)) AS feature

Column 2 in UNION ALL has incompatible types: ARRAY, NUMERIC at [2:1]

SIGN関数は引数の型と返り値の型が同じになることがわかりました。やりましたね。

ちなみに、BigQueryの数学関数の多くはINT64型とFLOAT64型が引数のときはFLOAT64型を返し、NUMERIC型が引数のときはNUMERIC型を返すようです¹（例：ROUND()）。

JSON_EXTRACT_SCALAR()というJSON文字列から値を取り出す関数があるんですが、こいつの型も調べてみました（というかこれを調べたくてこんなことをしていた）

SELECT JSON_EXTRACT_SCALAR('{"num": "1"}', '$.num')
SELECT JSON_EXTRACT_SCALAR('{"num": 1}', '$.num')

上の結果って前者がSTRING、後者がINT64を返しそうな気がしませんか？実際はどちらもSTRINGが返ってきます。冷静に考えたらそりゃそうよねって話でした。