エラーが発生する状況

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

MERGE INTO `dataset_id.table_id` AS T
USING (
    SELECT id, name, updated_at
    FROM UNNEST([STRUCT("123" AS id, NULL AS name, NULL AS updated_at)]) AS source_data
) AS S
ON T.id = S.id
WHEN MATCHED THEN UPDATE SET
    T.name = IFNULL(S.name, T.name),
    T.updated_at = IFNULL(S.updated_at, T.updated_at ),
WHEN NOT MATCHED THEN INSERT (id, name, updated_at)
    VALUES (S.id, S.name, S.updated_at)

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

No matching signature for function IFNULL
  Argument types: INT64, TIMESTAMP
  Signature: IFNULL(T1, T1)
    Unable to find common supertype for templated argument <T1>
      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を本来のデータ型に明示的にキャストすることで回避できる。

CAST(NULL AS TIMESTAMP) AS updated_at

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

MERGE INTO `dataset_id.table_id` AS T
USING (
    SELECT id, name, updated_at
    FROM UNNEST([STRUCT("123" AS id, CAST(NULL AS STRING) AS name, CAST(NULL AS TIMESTAMP) AS updated_at)]) AS source_data
) AS S
ON T.id = S.id
WHEN MATCHED THEN UPDATE SET
    T.name = IFNULL(S.name, T.name),
    T.updated_at = IFNULL(S.updated_at, T.updated_at ),
WHEN NOT MATCHED THEN INSERT (id, name, updated_at)
    VALUES (S.id, S.name, S.updated_at)