概要

Azure Databricks にて、小数点を含む STRING 型のカラム（例: '12.00'）を coalesce などで整数リテラルと組み合わせた際に発生するエラー事象について報告します。ただし、 Azure 東日本リージョンのSQL Warehouse でのみエラーとならなかったため、SQL Warehouse から Compute への切り替え時や、東日本リージョンへの将来のアップデートでエラーに遭遇する可能性があります。

具体的には、coalesce(STRING_COL, 0) のように記述すると、STRING 型のカラムが BIGINT 型へ暗黙的にキャストされる過程で CAST_INVALID_INPUT エラーが発生します。

WITH src AS (
SELECT '12.00' AS NUBMER_COL
UNION ALL
SELECT '45.00' AS NUBMER_COL
UNION ALL
SELECT NULL AS NUBMER_COL
)
SELECT
    NUBMER_COL,
    coalesce(NUBMER_COL, 0) AS NUMBER
    FROM
        src

[CAST_INVALID_INPUT] The value '12.00' of the type "STRING" cannot be cast to "BIGINT" because it is malformed. Correct the value as per the syntax, or change its target type. Use try_cast to tolerate malformed input and return NULL instead. SQLSTATE: 22018 == SQL (line 10, position 5) == coalesce(NUBMER_COL, 0) AS NUMBER ^^^^^^^^^^^^^^^^^^^^^^^

対応方法は try_cast による明示的な型変換です

エラーの再現

以下の SQL を実行するとエラーが発生します。

WITH src AS (
    SELECT '12.00' AS NUMBER_COL
    UNION ALL
    SELECT '45.00' AS NUMBER_COL
    UNION ALL
    SELECT NULL  AS NUMBER_COL
)
SELECT
    NUMBER_COL,
    coalesce(NUMBER_COL, 0) AS NUMBER
FROM
    src

[CAST_INVALID_INPUT] The value '12.00' of the type "STRING" cannot be cast to "BIGINT" because it is malformed. Correct the value as per the syntax, or change its target type. Use try_cast to tolerate malformed input and return NULL instead. SQLSTATE: 22018 == SQL (line 10, position 5) == coalesce(NUBMER_COL, 0) AS NUMBER ^^^^^^^^^^^^^^^^^^^^^^^

エラーへの対応方法

try_cast でいったん decimal 型に変換してから bigint にキャストすることで、小数点を含む文字列を安全に扱うことができます。

WITH src AS (
SELECT '12.00' AS NUBMER_COL
UNION ALL
SELECT '45.00' AS NUBMER_COL
UNION ALL
SELECT NULL AS NUBMER_COL
)
SELECT
    NUBMER_COL,
    coalesce(cast(try_cast(NUBMER_COL AS decimal(19, 0)) as bigint), 0) AS NUMBER
    FROM
        src

環境ごとの実行結果

環境	Compute	SQL Warehouse
Databricks Free Edition	エラー	エラー
Azure `eastus` リージョン	エラー	エラー
Azure `japaneast` リージョン	エラー	正常動作

※ AWS 東京リージョンでは未検証です。

Databricks Free Edition

Compute: エラー

SQL Warehouse: エラー

Azure `eastus` リージョン

Compute: エラー

SQL Warehouse: エラー

Azure `japaneast` リージョン

Compute: エラー

SQL Warehouse: 正常動作

まとめ

小数点を含む STRING 型のカラムを coalesce などで整数リテラルと組み合わせると、暗黙的な BIGINT 変換に失敗してエラーとなる
一部環境（Azure 東日本リージョンの SQL Warehouse）のみで動作するため、環境差異に起因する突然のエラーに注意が必要
対応として try_cast による明示的な型変換を推奨