Condividi tramite

classe di errore CAST_INVALID_INPUT

  • Articolo

SQLSTATE: 22018

Impossibile eseguire il cast del valore <expression> del tipo <sourceType> a <targetType> perché non è valido. Correggere il valore in base alla sintassi o modificarne il tipo di destinazione. Usare try_cast per gestire input malformati e restituire NULL invece. Se necessario, impostare <ansiConfig> su "false" per ignorare questo errore.

Parametri

  • 'espressione: L'espressione che deve essere convertita in targettype
  • sourceType: tipo di dati di expression.
  • targetType: tipo di destinazione dell'operazione di conversione.
  • ansiConfig: impostazione di configurazione per modificare la modalità ANSI.

Spiegazione

Impossibile eseguire il cast del expression al targetType a causa di uno dei motivi seguenti:

  • expression è troppo grande per il dominio del tipo. Ad esempio, non è possibile eseguire il cast del numero 1000 a TINYINT perché tale dominio varia solo da -128 a +127.
  • expression contiene caratteri che non fanno parte del tipo. Ad esempio, a non può essere convertito in alcun tipo numerico.
  • expression è formattato in un modo che l'operazione di cast non può analizzare. Ad esempio, 1.0 e 1e1 non possono essere convertiti in alcun tipo numerico integrale.

Il cast potrebbe non essere stato specificato in modo esplicito, ma potrebbe essere stato inserito in modo implicito da Azure Databricks.

Le informazioni sul contesto fornite con questo errore isolano l'oggetto e l'espressione all'interno della quale si è verificato l'errore.

Per una definizione del dominio e dei formati letterali accettati, vedere la definizione di per il tipo di dati di tyopeName.

Mitigazione

La mitigazione per questo errore dipende dalla causa:

  • È previsto che il value sia conforme al dominio e al formato del typeNamespecificato?

    Verificare il valore generato dall'input e correggere la fonte dati.

  • L'obiettivo del cast è troppo ristretto?

    Ampliare il tipo spostando, ad esempio, da DATE a TIMESTAMP, INT a BIGINT o DOUBLE.

  • Il formato di value non è corretto?

    Prendere in considerazione l'uso di:

    Queste funzioni consentono un'ampia gamma di formati che è possibile specificare.

    Quando si esegue il cast di valori letterali numerici con virgole decimali (ad esempio, 1.0) o in notazione scientifica (ad esempio, 1e0) è consigliabile fare prima un doppio cast a DECIMAL o DOUBLE e quindi al numero esatto.

  • I dati con valori non corretti sono previsti e devono essere tollerati producendo valori NULL?

    Modificare l'uso dell'espressione o inserire try_cast(value AS typeName). Questa funzione restituisce NULL quando viene invocata senza un value che corrisponde al tipo.

    Se non è possibile modificare l'espressione, come ultima risorsa, è possibile disabilitare temporaneamente la modalità ANSI usando ansiConfig.

Esempi

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