CAST_INVALID_INPUT 错误类
类型 <sourceType> 的值 <expression> 无法强制转换为 <targetType>，因为其格式错误。
根据语法更正值，或更改其目标类型。
使用 try_cast 来容许格式错误的输入并改为返回 NULL。
如有必要，请将 <ansiConfig> 设置为“false”以绕过此错误。
参数
- expression：需要转换为
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的格式不正确吗？请考虑使用：
这些函数允许指定多种格式。
当转换带小数点的数字文本（例如
1.0或科学记数法（例如1e0）时，请首先考虑将双精度数转换为DECIMAL或DOUBLE，然后再转换为确切的数字。是否预计数据具有不正确的值，并且应通过生成 NULL 来允许这种情况？
更改表达式的使用或注入 try_cast(value AS typeName)。 此函数在没有满足类型的
value的情况下传递时返回NULL。如果无法更改表达式，作为最后手段，可以使用
ansiConfig暂时禁用 ANSI 模式。
示例
-- 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;