Condition d’erreur CAST_INVALID_INPUT

SQLSTATE : 22018

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

Paramètres

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

Explanation

Impossible de convertir expression en targetType pour l'une des raisons suivantes :

• expression est trop volumineux pour le domaine du type. Par exemple, le nombre 1000 ne peut pas être converti en TINYINT car ce domaine est compris entre -128+127.
• expression contient des caractères qui ne font pas partie du type. Par exemple a , ne peut pas être converti en type numérique.
• expression est mis en forme d’une manière que l’opération de conversion ne peut pas interpréter. Par exemple 1.0 , et 1e1 ne peut pas être converti en un type numérique intégral.

Le cast n’a peut-être pas été spécifié explicitement, mais il peut avoir é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.

Atténuation

L’atténuation de cette erreur dépend de la cause :

• value Le respect du domaine et du format spécifiés typeNameest-il prévu ?

  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 est-il value incorrect ?

  Envisagez d’utiliser :

  • to_date
  • to_number
  • to_timestamp

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

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

• Est-il prévu que des données avec des valeurs incorrectes soient 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 appelée sans un value qui satisfait le type.

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

Examples

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

Related

• Fonction cast
• Fonction to_date
• Fonction to_number
• Fonction to_timestamp
• Fonction try_cast
• Fonction try_to_number
• ANSI_MODE