cast.sql.out

-- Automatically generated by SQLQueryTestSuite
-- !query
SELECT CAST('1.23' AS int)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '1.23' 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('1.23' AS int)
       ^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('1.23' AS long)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '1.23' 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 CAST('1.23' AS long)
       ^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('-4.56' AS int)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '-4.56' 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('-4.56' AS int)
       ^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('-4.56' AS long)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '-4.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 CAST('-4.56' AS long)
       ^^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('abc' AS int)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value 'abc' 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('abc' AS int)
       ^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('abc' AS long)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value 'abc' 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 CAST('abc' AS long)
       ^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('abc' AS float)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value 'abc' of the type "STRING" cannot be cast to "FLOAT" 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('abc' AS float)
       ^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('abc' AS double)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value 'abc' of the type "STRING" cannot be cast to "DOUBLE" 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('abc' AS double)
       ^^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('1234567890123' AS int)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '1234567890123' 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('1234567890123' AS int)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('12345678901234567890123' AS long)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '12345678901234567890123' 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 CAST('12345678901234567890123' AS long)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('' AS int)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '' 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('' AS int)
       ^^^^^^^^^^^^^^^


-- !query
SELECT CAST('' AS long)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '' 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 CAST('' AS long)
       ^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('' AS float)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '' of the type "STRING" cannot be cast to "FLOAT" 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('' AS float)
       ^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('' AS double)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '' of the type "STRING" cannot be cast to "DOUBLE" 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('' AS double)
       ^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST(NULL AS int)
-- !query schema
struct<CAST(NULL AS INT):int>
-- !query output
NULL


-- !query
SELECT CAST(NULL AS long)
-- !query schema
struct<CAST(NULL AS BIGINT):bigint>
-- !query output
NULL


-- !query
SELECT CAST('123.a' AS int)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '123.a' 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('123.a' AS int)
       ^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('123.a' AS long)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '123.a' 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 CAST('123.a' AS long)
       ^^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('123.a' AS float)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '123.a' of the type "STRING" cannot be cast to "FLOAT" 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('123.a' AS float)
       ^^^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('123.a' AS double)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '123.a' of the type "STRING" cannot be cast to "DOUBLE" 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('123.a' AS double)
       ^^^^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('-2147483648' AS int)
-- !query schema
struct<CAST(-2147483648 AS INT):int>
-- !query output
-2147483648


-- !query
SELECT CAST('-2147483649' AS int)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '-2147483649' 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('-2147483649' AS int)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('2147483647' AS int)
-- !query schema
struct<CAST(2147483647 AS INT):int>
-- !query output
2147483647


-- !query
SELECT CAST('2147483648' AS int)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '2147483648' 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('2147483648' AS int)
       ^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('-9223372036854775808' AS long)
-- !query schema
struct<CAST(-9223372036854775808 AS BIGINT):bigint>
-- !query output
-9223372036854775808


-- !query
SELECT CAST('-9223372036854775809' AS long)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '-9223372036854775809' 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 CAST('-9223372036854775809' AS long)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT CAST('9223372036854775807' AS long)
-- !query schema
struct<CAST(9223372036854775807 AS BIGINT):bigint>
-- !query output
9223372036854775807


-- !query
SELECT CAST('9223372036854775808' AS long)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '9223372036854775808' 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 CAST('9223372036854775808' AS long)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
SELECT HEX(CAST('abc' AS binary))
-- !query schema
struct<hex(CAST(abc AS BINARY)):string>
-- !query output
616263


-- !query
SELECT HEX(CAST(CAST(123 AS byte) AS binary))
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
cannot resolve 'CAST(CAST(123 AS TINYINT) AS BINARY)' due to data type mismatch: 
 cannot cast tinyint to binary with ANSI mode on.
 If you have to cast tinyint to binary, you can set spark.sql.ansi.enabled as false.
; line 1 pos 11


-- !query
SELECT HEX(CAST(CAST(-123 AS byte) AS binary))
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
cannot resolve 'CAST(CAST(-123 AS TINYINT) AS BINARY)' due to data type mismatch: 
 cannot cast tinyint to binary with ANSI mode on.
 If you have to cast tinyint to binary, you can set spark.sql.ansi.enabled as false.
; line 1 pos 11


-- !query
SELECT HEX(CAST(123S AS binary))
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
cannot resolve 'CAST(123S AS BINARY)' due to data type mismatch: 
 cannot cast smallint to binary with ANSI mode on.
 If you have to cast smallint to binary, you can set spark.sql.ansi.enabled as false.
; line 1 pos 11


-- !query
SELECT HEX(CAST(-123S AS binary))
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
cannot resolve 'CAST(-123S AS BINARY)' due to data type mismatch: 
 cannot cast smallint to binary with ANSI mode on.
 If you have to cast smallint to binary, you can set spark.sql.ansi.enabled as false.
; line 1 pos 11


-- !query
SELECT HEX(CAST(123 AS binary))
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
cannot resolve 'CAST(123 AS BINARY)' due to data type mismatch: 
 cannot cast int to binary with ANSI mode on.
 If you have to cast int to binary, you can set spark.sql.ansi.enabled as false.
; line 1 pos 11


-- !query
SELECT HEX(CAST(-123 AS binary))
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
cannot resolve 'CAST(-123 AS BINARY)' due to data type mismatch: 
 cannot cast int to binary with ANSI mode on.
 If you have to cast int to binary, you can set spark.sql.ansi.enabled as false.
; line 1 pos 11


-- !query
SELECT HEX(CAST(123L AS binary))
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
cannot resolve 'CAST(123L AS BINARY)' due to data type mismatch: 
 cannot cast bigint to binary with ANSI mode on.
 If you have to cast bigint to binary, you can set spark.sql.ansi.enabled as false.
; line 1 pos 11


-- !query
SELECT HEX(CAST(-123L AS binary))
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
cannot resolve 'CAST(-123L AS BINARY)' due to data type mismatch: 
 cannot cast bigint to binary with ANSI mode on.
 If you have to cast bigint to binary, you can set spark.sql.ansi.enabled as false.
; line 1 pos 11


-- !query
DESC FUNCTION boolean
-- !query schema
struct<function_desc:string>
-- !query output
Class: org.apache.spark.sql.catalyst.expressions.Cast
Function: boolean
Usage: boolean(expr) - Casts the value `expr` to the target data type `boolean`.


-- !query
DESC FUNCTION EXTENDED boolean
-- !query schema
struct<function_desc:string>
-- !query output
Class: org.apache.spark.sql.catalyst.expressions.Cast
Extended Usage:
    No example/argument for boolean.

    Since: 2.0.1

Function: boolean
Usage: boolean(expr) - Casts the value `expr` to the target data type `boolean`.


-- !query
SELECT CAST('interval 3 month 1 hour' AS interval)
-- !query schema
struct<CAST(interval 3 month 1 hour AS INTERVAL):interval>
-- !query output
3 months 1 hours


-- !query
SELECT CAST("interval '3-1' year to month" AS interval year to month)
-- !query schema
struct<CAST(interval '3-1' year to month AS INTERVAL YEAR TO MONTH):interval year to month>
-- !query output
3-1


-- !query
SELECT CAST("interval '3 00:00:01' day to second" AS interval day to second)
-- !query schema
struct<CAST(interval '3 00:00:01' day to second AS INTERVAL DAY TO SECOND):interval day to second>
-- !query output
3 00:00:01.000000000


-- !query
SELECT CAST(interval 3 month 1 hour AS string)
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.catalyst.parser.ParseException

Cannot mix year-month and day-time fields: interval 3 month 1 hour(line 1, pos 12)

== SQL ==
SELECT CAST(interval 3 month 1 hour AS string)
------------^^^


-- !query
SELECT CAST(interval 3 year 1 month AS string)
-- !query schema
struct<CAST(INTERVAL '3-1' YEAR TO MONTH AS STRING):string>
-- !query output
INTERVAL '3-1' YEAR TO MONTH


-- !query
SELECT CAST(interval 3 day 1 second AS string)
-- !query schema
struct<CAST(INTERVAL '3 00:00:01' DAY TO SECOND AS STRING):string>
-- !query output
INTERVAL '3 00:00:01' DAY TO SECOND


-- !query
select cast(' 1' as tinyint)
-- !query schema
struct<CAST( 1 AS TINYINT):tinyint>
-- !query output
1


-- !query
select cast(' 1\t' as tinyint)
-- !query schema
struct<CAST( 1	 AS TINYINT):tinyint>
-- !query output
1


-- !query
select cast(' 1' as smallint)
-- !query schema
struct<CAST( 1 AS SMALLINT):smallint>
-- !query output
1


-- !query
select cast(' 1' as INT)
-- !query schema
struct<CAST( 1 AS INT):int>
-- !query output
1


-- !query
select cast(' 1' as bigint)
-- !query schema
struct<CAST( 1 AS BIGINT):bigint>
-- !query output
1


-- !query
select cast(' 1' as float)
-- !query schema
struct<CAST( 1 AS FLOAT):float>
-- !query output
1.0


-- !query
select cast(' 1 ' as DOUBLE)
-- !query schema
struct<CAST( 1  AS DOUBLE):double>
-- !query output
1.0


-- !query
select cast('1.0 ' as DEC)
-- !query schema
struct<CAST(1.0  AS DECIMAL(10,0)):decimal(10,0)>
-- !query output
1


-- !query
select cast('1中文' as tinyint)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '1中文' of the type "STRING" cannot be cast to "TINYINT" 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('1中文' as tinyint)
       ^^^^^^^^^^^^^^^^^^^^^^


-- !query
select cast('1中文' as smallint)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '1中文' 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(line 1, position 8) ==
select cast('1中文' as smallint)
       ^^^^^^^^^^^^^^^^^^^^^^^


-- !query
select cast('1中文' as INT)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '1中文' 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('1中文' as INT)
       ^^^^^^^^^^^^^^^^^^


-- !query
select cast('中文1' as bigint)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '中文1' 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 cast('中文1' as bigint)
       ^^^^^^^^^^^^^^^^^^^^^


-- !query
select cast('1中文' as bigint)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value '1中文' 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 cast('1中文' as bigint)
       ^^^^^^^^^^^^^^^^^^^^^


-- !query
select cast('\t\t true \n\r ' as boolean)
-- !query schema
struct<CAST(		 true 

  AS BOOLEAN):boolean>
-- !query output
true


-- !query
select cast('\t\n false \t\r' as boolean)
-- !query schema
struct<CAST(	
 false 	
 AS BOOLEAN):boolean>
-- !query output
false


-- !query
select cast('\t\n xyz \t\r' as boolean)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkRuntimeException
[CAST_INVALID_INPUT] The value '	
 xyz 	
' of the type "STRING" cannot be cast to "BOOLEAN" 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('\t\n xyz \t\r' as boolean)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
select cast('23.45' as decimal(4, 2))
-- !query schema
struct<CAST(23.45 AS DECIMAL(4,2)):decimal(4,2)>
-- !query output
23.45


-- !query
select cast('123.45' as decimal(4, 2))
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkArithmeticException
[CANNOT_CHANGE_DECIMAL_PRECISION] Decimal(expanded, 123.45, 5, 2) cannot be represented as Decimal(4, 2). If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
select cast('123.45' as decimal(4, 2))
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
select cast('xyz' as decimal(4, 2))
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkNumberFormatException
[CAST_INVALID_INPUT] The value 'xyz' of the type "STRING" cannot be cast to "DECIMAL(4,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('xyz' as decimal(4, 2))
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
select cast('2022-01-01' as date)
-- !query schema
struct<CAST(2022-01-01 AS DATE):date>
-- !query output
2022-01-01


-- !query
select cast('a' as date)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkDateTimeException
[CAST_INVALID_INPUT] The value 'a' 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('a' as date)
       ^^^^^^^^^^^^^^^^^


-- !query
select cast('2022-01-01 00:00:00' as timestamp)
-- !query schema
struct<CAST(2022-01-01 00:00:00 AS TIMESTAMP):timestamp>
-- !query output
2022-01-01 00:00:00


-- !query
select cast('a' as timestamp)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkDateTimeException
[CAST_INVALID_INPUT] The value 'a' of the type "STRING" cannot be cast to "TIMESTAMP" 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 timestamp)
       ^^^^^^^^^^^^^^^^^^^^^^


-- !query
select cast('2022-01-01 00:00:00' as timestamp_ntz)
-- !query schema
struct<CAST(2022-01-01 00:00:00 AS TIMESTAMP_NTZ):timestamp_ntz>
-- !query output
2022-01-01 00:00:00


-- !query
select cast('a' as timestamp_ntz)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkDateTimeException
[CAST_INVALID_INPUT] The value 'a' of the type "STRING" cannot be cast to "TIMESTAMP_NTZ" 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 timestamp_ntz)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
select cast(cast('inf' as double) as timestamp)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkDateTimeException
[CAST_INVALID_INPUT] The value Infinity of the type "DOUBLE" cannot be cast to "TIMESTAMP" 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(cast('inf' as double) as timestamp)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
select cast(cast('inf' as float) as timestamp)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkDateTimeException
[CAST_INVALID_INPUT] The value Infinity of the type "DOUBLE" cannot be cast to "TIMESTAMP" 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(cast('inf' as float) as timestamp)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^


-- !query
select cast(interval '1' year as tinyint)
-- !query schema
struct<CAST(INTERVAL '1' YEAR AS TINYINT):tinyint>
-- !query output
1


-- !query
select cast(interval '-10-2' year to month as smallint)
-- !query schema
struct<CAST(INTERVAL '-10-2' YEAR TO MONTH AS SMALLINT):smallint>
-- !query output
-122


-- !query
select cast(interval '1000' month as int)
-- !query schema
struct<CAST(INTERVAL '1000' MONTH AS INT):int>
-- !query output
1000


-- !query
select cast(interval -'10.123456' second as tinyint)
-- !query schema
struct<CAST(INTERVAL '-10.123456' SECOND AS TINYINT):tinyint>
-- !query output
-10


-- !query
select cast(interval '23:59:59' hour to second as smallint)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkArithmeticException
[CAST_OVERFLOW] The value INTERVAL '23:59:59' HOUR TO SECOND of the type "INTERVAL HOUR TO SECOND" cannot be cast to "SMALLINT" due to an overflow. Use `try_cast` to tolerate overflow and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.


-- !query
select cast(interval -'1 02:03:04.123' day to second as int)
-- !query schema
struct<CAST(INTERVAL '-1 02:03:04.123' DAY TO SECOND AS INT):int>
-- !query output
-93784


-- !query
select cast(interval '10' day as bigint)
-- !query schema
struct<CAST(INTERVAL '10' DAY AS BIGINT):bigint>
-- !query output
10


-- !query
select cast(interval '-1000' month as tinyint)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkArithmeticException
[CAST_OVERFLOW] The value INTERVAL '-1000' MONTH of the type "INTERVAL MONTH" cannot be cast to "TINYINT" due to an overflow. Use `try_cast` to tolerate overflow and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.


-- !query
select cast(interval '1000000' second as smallint)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkArithmeticException
[CAST_OVERFLOW] The value INTERVAL '1000000' SECOND of the type "INTERVAL SECOND" cannot be cast to "SMALLINT" due to an overflow. Use `try_cast` to tolerate overflow and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.


-- !query
select cast(1Y as interval year)
-- !query schema
struct<CAST(1 AS INTERVAL YEAR):interval year>
-- !query output
1-0


-- !query
select cast(-122S as interval year to month)
-- !query schema
struct<CAST(-122 AS INTERVAL YEAR TO MONTH):interval year to month>
-- !query output
-10-2


-- !query
select cast(1000 as interval month)
-- !query schema
struct<CAST(1000 AS INTERVAL MONTH):interval month>
-- !query output
83-4


-- !query
select cast(-10L as interval second)
-- !query schema
struct<CAST(-10 AS INTERVAL SECOND):interval second>
-- !query output
-0 00:00:10.000000000


-- !query
select cast(100Y as interval hour to second)
-- !query schema
struct<CAST(100 AS INTERVAL HOUR TO SECOND):interval hour to second>
-- !query output
0 00:01:40.000000000


-- !query
select cast(-1000S as interval day to second)
-- !query schema
struct<CAST(-1000 AS INTERVAL DAY TO SECOND):interval day to second>
-- !query output
-0 00:16:40.000000000


-- !query
select cast(10 as interval day)
-- !query schema
struct<CAST(10 AS INTERVAL DAY):interval day>
-- !query output
10 00:00:00.000000000


-- !query
select cast(2147483647 as interval year)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkArithmeticException
[CAST_OVERFLOW] The value 2147483647 of the type "INT" cannot be cast to "INTERVAL YEAR" due to an overflow. Use `try_cast` to tolerate overflow and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.


-- !query
select cast(-9223372036854775808L as interval day)
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkArithmeticException
[CAST_OVERFLOW] The value -9223372036854775808L of the type "BIGINT" cannot be cast to "INTERVAL DAY" due to an overflow. Use `try_cast` to tolerate overflow and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.


-- !query
select cast(interval '-1' year as decimal(10, 0))
-- !query schema
struct<CAST(INTERVAL '-1' YEAR AS DECIMAL(10,0)):decimal(10,0)>
-- !query output
-1


-- !query
select cast(interval '1.000001' second as decimal(10, 6))
-- !query schema
struct<CAST(INTERVAL '01.000001' SECOND AS DECIMAL(10,6)):decimal(10,6)>
-- !query output
1.000001


-- !query
select cast(interval '08:11:10.001' hour to second as decimal(10, 4))
-- !query schema
struct<CAST(INTERVAL '08:11:10.001' HOUR TO SECOND AS DECIMAL(10,4)):decimal(10,4)>
-- !query output
29470.0010


-- !query
select cast(interval '1 01:02:03.1' day to second as decimal(8, 1))
-- !query schema
struct<CAST(INTERVAL '1 01:02:03.1' DAY TO SECOND AS DECIMAL(8,1)):decimal(8,1)>
-- !query output
90123.1


-- !query
select cast(interval '10.123' second as decimal(4, 2))
-- !query schema
struct<CAST(INTERVAL '10.123' SECOND AS DECIMAL(4,2)):decimal(4,2)>
-- !query output
10.12


-- !query
select cast(interval '10.005' second as decimal(4, 2))
-- !query schema
struct<CAST(INTERVAL '10.005' SECOND AS DECIMAL(4,2)):decimal(4,2)>
-- !query output
10.01


-- !query
select cast(interval '10.123' second as decimal(5, 2))
-- !query schema
struct<CAST(INTERVAL '10.123' SECOND AS DECIMAL(5,2)):decimal(5,2)>
-- !query output
10.12


-- !query
select cast(interval '10.123' second as decimal(1, 0))
-- !query schema
struct<>
-- !query output
org.apache.spark.SparkArithmeticException
[CANNOT_CHANGE_DECIMAL_PRECISION] Decimal(compact, 10, 18, 6) cannot be represented as Decimal(1, 0). If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
select cast(interval '10.123' second as decimal(1, 0))
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^