Delen via

foutklasse CAST_INVALID_INPUT

  • Artikel

SQLSTATE: 22018

De waarde <expression> van het type <sourceType> kan niet worden omgezet in <targetType> omdat deze ongeldig is. Corrigeer de waarde volgens de syntaxis of wijzig het doeltype. Gebruik try_cast om onjuiste invoer te tolereren en in plaats daarvan NULL te retourneren. Stel indien nodig <ansiConfig> in op 'false' om deze fout te omzeilen.

Parameters

  • Expressie: de expressie die moet worden gecast naar targettype
  • sourceType: het gegevenstype van expression.
  • targetType: het doeltype van de cast-bewerking.
  • ansiConfig: de configuratie-instelling voor het wijzigen van de ANSI-modus.

Uitleg

De expression kan om een van de volgende redenen niet naar de targetType worden gecast:

  • expression is te groot voor het domein van het type. Het aantal 1000 kan bijvoorbeeld niet worden gecast naar TINYINT omdat dat domein alleen varieert van -128 tot +127.
  • expression bevat tekens die geen deel uitmaken van het type. a kan bijvoorbeeld niet worden omgezet in een numeriek type.
  • expression is opgemaakt op een manier waarop de cast-bewerking niet kan worden geparseerd. Zo kunnen 1.0 en 1e1 niet worden omgezet in een integraal numeriek type.

De cast is mogelijk niet expliciet opgegeven, maar kan impliciet geïnjecteerd zijn door Azure Databricks.

De contextinformatie van deze fout isoleert het object en de expressie waarin de fout is opgetreden.

Zie voor een definitie van het domein en geaccepteerde letterlijke indelingen de definitie voor het gegevenstype van tyopeName.

Mitigatie

De oplossing voor deze fout is afhankelijk van de oorzaak:

  • Wordt verwacht dat de value voldoet aan het domein en de indeling van de opgegeven typeName?

    Controleer de invoer die de waarde produceert en corrigeer de gegevensbron.

  • Is het doel van de cast te beperkt?

    Verbreed het type door bijvoorbeeld te verplaatsen van DATE naar TIMESTAMP, INT naar BIGINT of DOUBLE.

  • Is de indeling van value onjuist?

    Overweeg het gebruik van:

    Deze functies bieden een groot aantal indelingen die u kunt opgeven.

    Bij het casten van numerieke literals met decimale punten (bijvoorbeeld 1.0) of in wetenschappelijke notatie (bijvoorbeeld 1e0), overweeg om eerst dubbel te casten naar DECIMAL of DOUBLE en vervolgens naar de exacte numerieke waarde.

  • Worden gegevens met onjuiste waarden verwacht en moeten ze worden getolereerd door NULL's te produceren?

    Wijzig het gebruik van de expressie of injecteer try_cast(waarde AS typeName). Deze functie retourneert NULL wanneer deze wordt doorgegeven zonder een value die voldoet aan het type.

    Als u de expressie niet als laatste redmiddel kunt wijzigen, kunt u de ANSI-modus tijdelijk uitschakelen met behulp van ansiConfig.

Voorbeelden

-- 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;