BigQueryのSQLいろいろ (5) NULL・その他の型

BigQueryのSQLについて、ドキュメントを読んだり実験したりしながら挙動を解き明かしていこうと思います。第5回はNULLについて紹介します。ここまでで扱わなかったその他の型についても触れます。

今回扱う型

NULL
BOOLEAN
GEOGRAPHY

NULL値

NULL値はSQLではおなじみの値です。BigQueryでは、全ての型に出現の余地があります。

NULLの保存

テーブルではNULLの保存に制約があります。

カラム型が ARRAY の場合は、 REPEATED モードとして扱われるため、 NULL は空配列と同一視されます。
それ以外の場合は、カラムは OPTIONAL モードか REQUIRED モードを選ぶことができます。 REQUIRED の場合は NULL の保存が禁止されます。

NULLの操作

NULLに期待される意味は、それが使われる文脈によってまちまちです。

データが存在しないことを表す NULL
データが不定であることを表す NULL
計算に失敗したことを表す NULL
制約がないことを表す NULL　　　など...

ただし、ほとんどの場面ではNULLは伝播されるため、NULLを (Haskellにおける Maybe Monad のような) 例外の一種とみなすのが実際の挙動に近いといえるかもしれません。

NULLが伝播されない場面としては以下があります。

AND, OR (後述)
COALESCE, IFNULL の最後以外の引数のNULL
CASE 式や IF 中の条件判定で生じたNULL
IN 式のグループに NULL が含まれているが、グループ内の他の値にマッチした場合 (OR のセマンティックスに準拠)
IS (NOT) NULL/FALSE/TRUE/UNKNOWN の引数のNULL
IS (NOT) DISTINCT FROM の引数のNULL
集計関数・ウインドウ関数の一部や、 IGNORE NULLS を指定した場合
RANGE() コンストラクタ

このうち、 AND/OR の振舞いは注目に値します。実は、NULL を ⊥ と考えたとき、SQLのORの演算表は "por" (parallel or) と呼ばれる演算と一致します。言い換えると、これは双方向にショートサーキットする能力を持っています。

NULL の比較

NULL の比較は特殊です。以下のようなケースでは、 NULL 同士は互いに異なるものとして扱われます。

= 演算子による比較 (比較結果が NULL になる点に注意)
<=, >= 演算子による比較 (比較結果が NULL になる点に注意)
- <, > でも比較結果は NULL になる
JOIN USING を使った比較 (JOIN ON で = を明示的に書いた場合と同様)

いっぽう、以下のような場合は NULL 同士は互いに同じものとして扱われます。

GROUP BY, PARTITION BY, DISTINCT による比較
ORDER BY による比較
IS NOT DISTINCT FROM による比較

また、 ORDER BY における NULL の順序は指定により異なります。

NULLS FIRST または NULLS LAST が指定されている場合、その指定に従います。
明示的な指定がなく、かつ DESC の場合は、 NULLS LAST となります。
それ以外の場合 (ASC の場合) は、 NULLS FIRST となります。

NULL 型

BigQuery には NULL 型が存在します。 (NULL 値とは別です)

他の型とそろえる形で言い換えるのであれば、これは「NULLABLE NEVER 型」と呼んだほうが正確かもしれません。

NULL 型をもつ式には以下のものがあります。

NULL リテラル
ERROR(...) 関数呼び出し

NULL と型推論

NULL は実質的に型推論のための型といえます。そこで、ここではBigQueryの型推論について、実際の挙動から推定した内容を説明します。

まず基本的に、 BigQuery の型推論はボトムアップです。つまり、ある式の型を決定するにあたって、その式の外の式の推論結果を使うことは基本的にありません。

たとえば、以下の SELECT は Hindley-Milner 型の型推論では通る可能性がありますが、 BigQuery では通りません。これは BigQuery の型推論が Hindley-Milner 型の高度な推論ではなく、シンプルなボトムアップ型の推論であることを示唆しています。

SELECT ['foo']
UNION ALL
-- NULL は STRING 型になりそうだが、この部分だけ見ても推論できない
SELECT [NULL]

この性質から、 NULL や ERROR(...) のように制約されていないジェネリクスを持つ式はそのままではBigQueryの枠組みで表現できないことになります。

いっぽう、以下の場合は型が通ります。

-- OK
SELECT NULL
UNION ALL
SELECT 'foo'

-- OK
SELECT ['foo']
UNION ALL
SELECT [NULL, 'bar']

このとき、型がわかっている側の式と、型が不明な側の式の順序は問いません。

このことから、 NULL や ERROR(...) などの式は型強制によって適切な型に変換されていると推定されます。そのための中継地点となる型が NULL ということになります。

まとめると、

任意の型になれる式 (NULL や ERROR(...)) には NULL型 が与えられる
NULL型は適当なタイミングで型強制により、他の型に揃えられる

となります。

INT64 へのフォールバック

一部の式では、NULL 型から INT64 へのフォールバックが発生します。

このことは以下のようなクエリで確認できます。

SELECT [NULL] || ['foo'] -- エラー
SELECT [NULL] || [42] -- OK

また、 TYPEOF もこのような振舞いをするため、 TYPEOF で NULL 型を直接観測することはできません。

SELECT TYPEOF(NULL) -- 'INT64'
SELECT TYPEOF(ERROR('foo')) -- 'INT64'

式ごとの振舞い

主要なジェネリックな式について振舞いをまとめると、以下のようになります。

INT64 へのフォールバックは行われない
- WITH 式内で宣言された変数に代入される式
型強制は行われるが、 INT64 にはフォールバックしない
- CASE 式 / IF(...)
- COALESCE(...)
INT64 へのフォールバックが行われる
- TYPEOF(...)
- スカラーサブクエリ ((SELECT ...))
- 配列サブクエリ (ARRAY(SELECT ...))
- INサブクエリ (... IN (SELECT ...))
- CTE
- WITH 式全体
- STRUCTの作成 (STRUCT(...))
- INの左辺式(型強制による統一は行われない)
- INの各右辺式 (型強制による統一は行われない)
型強制と INT64 へのフォールバックが行われる
- UNION ALL, UNION DISTINCT, EXCEPT DISTINCT
- 配列の作成 ([1, 2, 3], ARRAY(1, 2, 3))
型強制時に強制的に INT64 へのフォールバックが行われる
- 比較演算 (=, !=, <, <=, >, >=, BETWEEN)
- LIKE

なお、 UNION ALL が INT64 へのフォールバックを持つことから、以下のように結合性が成立しません。

-- エラー (最初の UNION ALL で INT64 になってしまう)
(
  SELECT NULL
  UNION ALL
  SELECT NULL
)
UNION ALL
SELECT 'foo'

-- OK (1回の UNION ALL でまとめて型強制されるので、まとめて STRING になる)
SELECT NULL
UNION ALL
SELECT NULL
UNION ALL
SELECT 'foo'