Freigeben über

CAST_INVALID_INPUT-Fehlerklasse

  • Artikel

SQLSTATE: 22018

Der Wert <expression> des Typs <sourceType> kann nicht in <targetType> umgewandelt werden, da er nicht wohlgeformt ist. Korrigieren Sie den Wert gemäß der Syntax, oder ändern Sie den Zieltyp. Verwenden Sie try_cast, um fehlerhafte Eingaben zu tolerieren und stattdessen NULL zurückzugeben. Legen Sie <ansiConfig> bei Bedarf auf FALSCH fest, um diesen Fehler zu umgehen.

Parameter

  • expression: Der Ausdruck, der in targettype umgewandelt werden muss.
  • sourceType: Der Datentyp von expression.
  • targetType: Der Zieltyp des Umwandlungsvorgangs.
  • ansiConfig: Die Konfigurationseinstellung zur Änderung des ANSI-Modus.

Erklärung

Der expression kann aus einem der folgenden Gründe nicht in den targetType umgewandelt werden:

  • expression ist zu groß für die Domäne des Typs. Beispielsweise kann die Zahl 1000 nicht in TINYINT umgewandelt werden, da diese Domäne nur von -128 bis +127 reicht.
  • expression enthält Zeichen, die nicht Teil des Typs sind. Beispielsweise kann a nicht in einen numerischen Typ umgewandelt werden.
  • expression ist so formatiert, dass der Umwandlungsvorgang ihn nicht analysieren kann. Beispielsweise können 1.0 und 1e1 nicht in einen ganzzahligen numerischen Typ umgewandelt werden.

Die Umwandlung muss nicht explizit angegeben worden sein, sondern kann implizit von Azure Databricks eingefügt worden sein.

Die mit diesem Fehler bereitgestellten Kontextinformationen isolieren das Objekt und den Ausdruck, in dem der Fehler aufgetreten ist.

Eine Definition der Domäne und der akzeptierten Literalformate finden Sie in der Definition für den Datentyp von tyopeName.

Lösung

Die Entschärfung dieses Fehlers hängt von der Ursache ab:

  • Soll der value der Domäne und dem Format des angegebenen typeName entsprechen?

    Überprüfen Sie den Eingabewert und korrigieren Sie die Datenquelle.

  • Ist das Ziel der Umwandlung zu eng gefasst?

    Erweitern Sie den Typ, indem Sie z. B. von DATE zu TIMESTAMP, INT zu BIGINT oder DOUBLE wechseln.

  • Ist das Format von value falsch?

    Mögliche Verwendung von:

    Diese Funktionen ermöglichen eine Vielzahl von Formaten, die Sie angeben können.

    Beim Umwandeln von numerischen Literalen mit Dezimalpunkten (z. B. 1.0) oder wissenschaftlicher Notation (z. B. 1e0) sollten Sie die doppelte Umwandlung zuerst auf DECIMAL oder DOUBLE und dann auf die exakte Zahl anwenden.

  • Werden Daten mit falschen Werten erwartet und sollten sie durch die Erzeugung von NULL-Werten toleriert werden?

    Ändern Sie die Verwendung des Ausdrucks oder fügen Sie try_cast(value AS typeName) ein. Diese Funktion gibt NULL zurück, wenn sie ohne value übergeben wird, der den Typ erfüllt.

    Wenn Sie den Ausdruck nicht ändern können, können Sie als letzten Ausweg den ANSI-Modus vorübergehend deaktivieren, indem Sie ansiConfig verwenden.

Beispiele

-- 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
 12.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);
 14400.00
 18246.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);
 30000.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);
 30000.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);
 30000.00
 NULL
> SET spark.sql.ansi.enabled = true;