BigQueryではサブクエリのエイリアスがSTRUCT型として扱われる

ちょっとハマった。

例えばMySQLだと次のようなクエリは通る。

ここでSELECT句とWHERE句で参照されているyはFROM句のサブクエリ内でカラムに指定している別名を指している。サブクエリの別名は特に動作に影響しない。

そして、上記のクエリはBigQueryだと通らず、No matching signature for operator = for argument types: STRUCT<y INT64>, INT64. Supported signature: ANY = ANY at [8:7]のようなエラーが表示される。

これは、フィールドとして扱われる文脈でサブクエリの別名を参照すると、サブクエリの結果を含むSTRUCT型として扱われることによっている。サブクエリの別名と同じ名前の別名がサブクエリ内のカラムに存在する場合であっても、サブクエリの別名への参照が優先される。一方、他の一般的なRDBMSではそもそもサブクエリの別名をフィールドとして扱われる文脈で参照できない。サブクエリの別名とサブクエリのカラム名で重複があっても、カラムが参照される。

BigQueryの場合、次のような書き方であれば通る。

-- サブクエリの別名を指定しない
select y
from (
    select x as y
    from sample.test
)
where y = 1
-- サブクエリの別名をカラムの別名と違うものにする
select y
from (
    select x as y
    from sample.test
) z
where y = 1
-- STRUCT型の中身として指定する
select y
from (
    select x as y
    from sample.test
) y
where y.y = 1

また、次の書き方でも通ることは通るが、WHERE句がSTRUCT型同士の比較になるのでクエリの意味が違ってしまう。

select y
from (
    select x as y
    from sample.test
) y
where y = struct(1)

サブクエリの別名はMySQL等では必須のため、サブクエリの別名とサブクエリ内のカラムの別名を同じものにしてしまうということがたまにある。それをそのままBigQueryに移植したりするとSTRUCTに由来するエラーに遭遇することになるので注意が必要。