CAST_INVALID_INPUT エラークラス

SQLSTATE:22018

<sourceType> 型の値 <expression> は、形式が正しくないため、<targetType> にキャストできません。構文に従って値を修正するか、ターゲットの種類を変更します。try_cast を使用して、不正な形式の入力を許容し、代わりに NULL を返します。必要に応じて、 <ansiConfig> を "false" に設定して、このエラーを回避します。

パラメーター

• expression : キャスト先の式 targettype
• sourceType : expressionのデータ型。
• targetType : キャスト操作のターゲットタイプ。
• ansiConfig : ANSI モードを変更するための構成設定。

説明

次のいずれかの理由により、 expression を targetType にキャストできません。

• expression は、その型のドメインに対して大きすぎます。 たとえば、1000 という番号は、ドメインの範囲が -128 から +127までしかないため、TINYINT にキャストできません。
• expression タイプの一部ではない文字が含まれています。 たとえば、 a を数値型にキャストすることはできません。
• expression キャスト操作で解析できない方法で書式設定されます。 たとえば、 1.0 と 1e1 は整数数値型にキャストできません。

キャストは明示的に指定されていない可能性がありますが、Databricks によって暗黙的に挿入されている可能性があります。

このエラーで提供されるコンテキスト情報により、エラーが発生したオブジェクトと式が分離されます。

ドメインと受け入れられるリテラル形式の定義については、 tyopeNameのデータタイプの定義を参照してください。

緩和

このエラーの軽減策は、原因によって異なります。

• valueは、指定されたtypeNameのドメインと形式に準拠していることが期待されますか?

  入力生成値を確認し、データソースを修正します。

• キャストのターゲットが狭すぎませんか?

  たとえば、 DATE から TIMESTAMPに移動したり、 INT を BIGINT に移動したり、 DOUBLEに移動したりして、タイプを広げます。

• valueの形式が正しくありませんか?

  次の使用を検討してください。

  • to_date
  • to_number
  • to_timestamp

  これらの関数を使用すると、指定できるさまざまな形式を使用できます。

  小数点を含む数値リテラルをキャストする場合 (例: 1.0 または科学的記数法(例: 1e0) 最初に DECIMAL または DOUBLE にダブルキャストを行い、次に正確な数値にダブルキャストすることを検討してください。

• 正しくない値を持つデータは予期されており、NULL を生成することで許容されるべきですか?

  式の使用を変更するか、 try_cast(value AS typeName) を挿入します。この関数は、型を満たすvalueなしで渡された場合に NULL を返します。

  式を変更できない場合は、最後の手段として、 ansiConfigを使用して ANSI モードを一時的に無効にすることができます。

例

SQL

-- A view with a cast and string literals outside the domain of the target type
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT CAST(a AS SMALLINT) FROM VALUES('100'), ('50000') AS t(a);
> SELECT c1 FROM v;
 [CAST_INVALID_INPUT] The value '50000' of the type "STRING" cannot be cast to "SMALLINT" 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.
 If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
 == SQL of VIEW v(line 1, position 8) ==
 SELECT CAST(a AS SMALLINT) FROM VALUES('100'), ('50000') A...
        ^^^^^^^^^^^^^^^^^^^

-- Widen the target type to match the domain of the input
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT cast(a AS INTEGER) FROM VALUES('100'), ('50000') AS t(a);
> SELECT c1 FROM v;
 100
 50000

-- The input data format does not match the target type
> SELECT cast(a AS INTEGER) FROM VALUES('1.0'), ('1e0') AS t(a);
 [CAST_INVALID_INPUT] The value '1.0' of the type "STRING" cannot be cast to "INT" 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.
 If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
 == SQL(line 1, position 8) ==
 SELECT cast(a AS INTEGER) FROM VALUES('1.0'), ('1e0') AS ...
        ^^^^^^^^^^^^^^^^^^

-- Adjust the target type to the match the format if the format is indicative of the domain.
> SELECT cast(a AS DOUBLE) FROM VALUES('1.0'), ('1e0') AS t(a);
 1.0
 1.0

-- ALternatively double cast to preserver the target type
> SELECT cast(cast(a AS DOUBLE) AS INTEGER) FROM VALUES('1.0'), ('1e0') AS t(a);
 1
 1

-- The format of the numeric input contains display artifacts
> SELECT cast(a AS DECIMAL(10, 3)) FROM VALUES('12,345.30-'), ('12+') AS t(a);
 [CAST_INVALID_INPUT] The value '12,345.30-' of the type "STRING" cannot be cast to "DECIMAL(10,3)" 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.
 If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
 == SQL(line 1, position 8) ==
 SELECT cast(a AS DECIMAL(10, 3)) FROM VALUES('$<123,45.30>'), ('...
        ^^^^^^^^^^^^^^^^^^^^^^^^^

-- Use to_number() to parse formatted values
> SELECT to_number(a, '9,999,999.999S') FROM VALUES('123,45.30-'), ('12+') AS t(a);
 -12345.300
 1.000

-- The format of a date input does not match the default format
> SELECT cast(geburtsdatum AS DATE) FROM VALUES('6.6.2000'), ('31.10.1970') AS t(geburtsdatum);
 [CAST_INVALID_INPUT] The value '6.6.2000' of the type "STRING" cannot be cast to "DATE" 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.
 If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
 == SQL(line 1, position 8) ==
 SELECT cast(geburtsdatum AS DATE) FROM VALUES('6.6.2000'), ('31.1...
        ^^^^^^^^^^^^^^^^^^^^^^^^^^

-- Use to_date to parse the correct input format for a date
> SELECT to_date(geburtsdatum, 'dd.MM.yyyy') FROM VALUES('6.6.2000'), ('31.10.1970') AS t(geburtsdatum);
  2000-06-06
  1970-10-31

-- The type resolution of Databricks did not derive a sufficiently wide type, failing an implicit cast
> SELECT 12 * monthly AS yearly FROM VALUES ('1200'), ('1520.56') AS t(monthly);
 [CAST_INVALID_INPUT] The value '1520.56' 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.
 If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
 == SQL(line 1, position 8) ==
 SELECT 12 * monthly AS yearly FROM VALUES ('1200'),...
        ^^^^^^^^^^^^

-- Explicitly declare the expected type
> SELECT 12 * cast(monthly AS DECIMAL(8, 2)) AS yearly FROM VALUES ('1200'), ('1520.56') AS t(monthly);
 1.00
 1.72

-- The input data is occasionally expected to incorrect
> SELECT cast(salary AS DECIMAL(9, 2)) FROM VALUES('30000'), ('prefer not to say') AS t(salary);
 [CAST_INVALID_INPUT] The value 'prefer not to say' of the type "STRING" cannot be cast to "DECIMAL(9,2)" 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.
 If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT cast(salary AS DECIMAL(9, 2)) FROM VALUES('30000'), ('prefer ...
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-- Use try_cast to tolerate incorrect input
> SELECT try_cast(salary AS DECIMAL(9, 2)) FROM VALUES('30000'), ('prefer not to say') AS t(salary);
 1.00
 NULL

-- In Databricks SQL temporarily disable ANSI mode to tolerate incorrect input.
> SET ANSI_MODE = false;
> SELECT cast(salary AS DECIMAL(9, 2)) FROM VALUES('30000'), ('prefer not to say') AS t(salary);
 1.00
 NULL
> SET ANSI_MODE = true;

-- In Databricks Runtime temporarily disable ANSI mode to tolerate incorrect input.
> SET spark.sql.ansi.enabled = false;
> SELECT cast(salary AS DECIMAL(9, 2)) FROM VALUES('30000'), ('prefer not to say') AS t(salary);
 1.00
 NULL
> SET spark.sql.ansi.enabled = true;

関連

• cast 関数
• to_date 関数
• to_number 関数
• to_timestamp 関数
• try_cast 関数
• try_to_number 関数
• ANSI_MODE