Partager via

classe d’erreur CAST_INVALID_INPUT

  • Article

SQLSTATE : 22018

La valeur <expression> du type <sourceType> ne peut pas être castée vers <targetType> car elle est incorrecte. Corrigez la valeur en fonction de la syntaxe, ou modifiez le type de sa cible. Utilisez try_cast pour tolérer une entrée incorrecte et retourner la valeur NULL à la place. Si nécessaire, définissez <ansiConfig> sur « false » pour contourner cette erreur.

Paramètres

  • expression: expression qui doit être castée en targettype
  • sourceType: type de données de expression.
  • targetType: type de cible de l’opération de cast.
  • ansiConfig : paramètre de configuration pour modifier le mode ANSI.

Explication

Leexpression ne peut pas être casté vers le targetType en raison de l’une des raisons suivantes :

  • expression est trop grand pour le domaine du type. Par exemple, le nombre 1000 ne peut pas être casté vers TINYINT étant donné que ce domaine est compris entre -128 et +127.
  • expression contient des caractères qui ne font pas partie du type. Par exemple a, il ne peut pas être casté en n’importe quel type numérique.
  • expression est mis en forme de manière à ce que l’opération de cast ne puisse pas être analysée. Par exemple 1.0 et 1e1 ne peuvent pas être convertis en n’importe quel type numérique intégral.

Le cast n’a peut-être pas été spécifié explicitement, mais il a peut-être été injecté implicitement par Azure Databricks.

Les informations de contexte fournies avec cette erreur isolent l’objet et l’expression dans laquelle l’erreur s’est produite.

Pour obtenir une définition du domaine et des formats littéraux acceptés, consultez la définition du type de données de tyopeName.

Limitation des risques

La manière d’atténuer l’erreur dépendra de sa cause :

  • Est-il attendu que le value soit conforme au domaine et au format du typeNamespécifié ?

    Vérifiez la valeur de production d’entrée et corrigez la source de données.

  • La cible du cast est-elle trop étroite ?

    Élargissez le type en déplaçant, par exemple, de DATE vers TIMESTAMP, INT vers BIGINT ou DOUBLE.

  • Le format devalue est-il incorrect ?

    Envisagez d’utiliser :

    Ces fonctions permettent un large éventail de formats que vous pouvez spécifier.

    Lors de la conversion de littéraux numériques avec des décimales (par exemple 1.0 ou une notation scientifique (par exemple 1e0), envisagez d’abord un double cast vers DECIMAL ou DOUBLE vers le numérique exact.

  • Les données avec des valeurs incorrectes attendues doivent-elles être tolérées en produisant des valeurs NULL ?

    Modifiez l’utilisation de l’expression ou injectez try_cast(valeur AS typeName). Cette fonction retourne NULL lorsqu’elle est passée sans un value qui réponde au type.

    Si vous ne pouvez pas modifier l’expression, en dernier recours, vous pouvez désactiver temporairement le mode ANSI à l’aide de ansiConfig.

Exemples

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