класс ошибок CAST_INVALID_INPUT

SQLSTATE: 22018

Значение <expression> типа <sourceType> нельзя привести к <targetType>, так как оно искажённое. Исправьте значение по синтаксису или измените его целевой тип. Вместо этого используйте try_cast, чтобы допускать неправильные входные данные и возвращать ЗНАЧЕНИЕ NULL. Если необходимо, задайте <ansiConfig> значение "false", чтобы обойти эту ошибку.

Параметры

• выражение: выражение, которое необходимо привести к типу targettype
• sourceType: тип данных expression.
• targetType: целевой тип операции приведения.
• ansiConfig: параметр конфигурации для изменения режима ANSI.

Объяснение

expression нельзя привести к targetType по одной из следующих причин:

• expression слишком велик для домена типа. Например, число 1000 нельзя привести к типу TINYINT, так как диапазон этого домена простирается от -128 до +127.
• expression содержит символы, которые не являются частью типа. Например, a нельзя привести ни к одному числовому типу.
• expression отформатирован таким образом, что операция приведения не может распознать его. Например, 1.0 и 1e1 нельзя привести к любому целочисленному типу.

Тип приведения, возможно, не был явно указан, но мог быть неявно добавлен Azure Databricks.

Сведения о контексте, предоставленные этой ошибкой, изолируют объект и выражение, в котором произошла ошибка.

Определение домена и принятых литеральных форматов см. в определении для типа данныхtyopeName.

Смягчение

Устранение этой ошибки зависит от причины:

• Должен ли value соответствовать домену и формату указанного typeName?

  Проверьте входные данные, создающие значение, и исправьте источник данных.

• Является ли цель слишком узкой?

  Расширяйте тип путем перемещения, например от DATE до TIMESTAMP, INT до BIGINT или DOUBLE.

• Неправильно ли формат value?

  Рассмотрите возможность использования:

  • до_даты
  • to_number
  • в_метку_времени

  Эти функции позволяют использовать широкий спектр форматов, которые можно указать.

  При приведении числовых литералов с десятичными точками (например, 1.0) или научной нотации (например, 1e0) следует сначала выполнить двойное приведение к DECIMAL или DOUBLE, а затем к точному числу.

• Ожидаются ли данные с неправильными значениями, и следует ли их обрабатывать, создавая NULL?

  Измените использование или внедрение выражения try_cast(value AS typeName). Эта функция возвращает NULL, если она вызывается без value, которая соответствует типу.

  Если вы не можете изменить выражение в качестве последнего способа, можно временно отключить режим ANSI с помощью ansiConfig.

Примеры

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

Родственный

• функция приведения типа
• функции to_date
• функция to_number
• функция to_timestamp
• функция try_cast
• функция try_to_number
• ANSI_MODE