Q. BigQueryでクエリを実行するとNo matching signature for function IFNULLエラーが出る

2024-12-05

BigQuery

エラーが発生する状況#

リクエストに応じてクエリを生成して BigQuery にリクエストを投げるサービスで、次のようなクエリが生成される。

1
MERGE INTO `dataset_id.table_id` AS T
2
USING (
3
    SELECT id, name, updated_at
4
    FROM UNNEST([STRUCT("123" AS id, NULL AS name, NULL AS updated_at)]) AS source_data
5
) AS S
6
ON T.id = S.id
7
WHEN MATCHED THEN UPDATE SET
8
    T.name = IFNULL(S.name, T.name),
9
    T.updated_at = IFNULL(S.updated_at, T.updated_at ),
10
WHEN NOT MATCHED THEN INSERT (id, name, updated_at)
11
    VALUES (S.id, S.name, S.updated_at)

ここで STRING 型や INTEGER 型でない TIMESTAMP 型や RECORD 型のカラムにデータを渡そうとすると、BigQuery 側で次のようなエラーが発生する。

1
No matching signature for function IFNULL
2
  Argument types: INT64, TIMESTAMP
3
  Signature: IFNULL(T1, T1)
4
    Unable to find common supertype for templated argument <T1>
5
      Input types for <T1>: {INT64, TIMESTAMP} at [0:0], invalidQuery

IFNULL の仕様#

BigQuery の IFNULL の仕様は次のようになっている。

1 つ目の引数が NULL である場合、2 つ目の引数を返す。
2 つの引数は同じデータ型に強制変換可能である必要がある。

強制変換可能かはConversion rules | BigQueryで確認できる。

問題の原因#

BigQuery では型を識別する方法がない場合、デフォルトの INT64 型が適用される。

つまり生成されるクエリでは NULL に対しての型の指定がなく INT64 型が適用されるため、INT64 と強制変換可能でないデータ型を操作しようとすると IFNULL でエラーが発生する。

解決策#

次のように NULL を本来のデータ型に明示的にキャストすることで回避できる。

1
CAST(NULL AS TIMESTAMP) AS updated_at

全体のクエリは次のようになる。

1
MERGE INTO `dataset_id.table_id` AS T
2
USING (
3
    SELECT id, name, updated_at
4
    FROM UNNEST([STRUCT("123" AS id, CAST(NULL AS STRING) AS name, CAST(NULL AS TIMESTAMP) AS updated_at)]) AS source_data
5
) AS S
6
ON T.id = S.id
7
WHEN MATCHED THEN UPDATE SET
8
    T.name = IFNULL(S.name, T.name),
9
    T.updated_at = IFNULL(S.updated_at, T.updated_at ),
10
WHEN NOT MATCHED THEN INSERT (id, name, updated_at)
11
    VALUES (S.id, S.name, S.updated_at)