modules/sql-engine/src/integrationTest/sql/group1/cast/test_cast_decimal.test

# name: test/sql/cast/test_cast_decinal.test
# description: CAST DECIMAL
# feature: SQL F201(CAST function)
# group: [cast]

statement ok
SELECT CAST('100.0' AS DECIMAL);

query T
SELECT CAST(1e1 AS DECIMAL);
----
10

query T
SELECT CAST('1e2' AS DECIMAL);
----
100

# overflow

statement error: Numeric field overflow. A field with precision 5, scale 0 must round to an absolute value less than 10^5.
SELECT CAST(1e20 AS DECIMAL(5));

statement error: Numeric field overflow. A field with precision 5, scale 0 must round to an absolute value less than 10^5.
SELECT CAST(-1e20 AS DECIMAL(5));

statement error: Numeric field overflow. A field with precision 3, scale 5 must round to an absolute value less than 10^-2.
SELECT 0.3::DECIMAL(3, 5);

statement error: Numeric field overflow. A field with precision 3, scale 3 must round to an absolute value less than 1.
SELECT -1::DECIMAL(3, 3);

statement error: Numeric field overflow. A field with precision 3, scale 3 must round to an absolute value less than 1.
SELECT 1::DECIMAL(3, 3);

statement error: Numeric field overflow. A field with precision 32767, scale 32767 must round to an absolute value less than 1.
SELECT 1::DECIMAL(32767, 32767);

# the first cast produces must report an error
statement error: Numeric field overflow. A field with precision 3, scale 0 must round to an absolute value less than 10^3.
SELECT 9999999::DECIMAL(3)::DECIMAL(4, 2);

for val in ['10000', 10000::SMALLINT, 10000::INT, 10000::BIGINT, 10000::REAL, 10000::FLOAT, 10000::DOUBLE, 10000::DECIMAL, 10000::DECIMAL(10)]

statement error: Numeric field overflow. A field with precision 4, scale 2 must round to an absolute value less than 10^2.
SELECT ${val}::DECIMAL(4, 2);

statement error: Numeric field overflow. A field with precision 2, scale 4 must round to an absolute value less than 10^-2.
SELECT ${val}::DECIMAL(2, 4);

statement error: Numeric field overflow. A field with precision 2, scale 2 must round to an absolute value less than 1.
SELECT ${val}::DECIMAL(2, 2);

statement error: Numeric field overflow. A field with precision 3, scale 0 must round to an absolute value less than 10^3.
SELECT ${val}::DECIMAL(3);

endfor

for val in [100::TINYINT, 100::SMALLINT, 100::INT, 100::BIGINT, 100::DECIMAL, 100::DECIMAL(3)]

query T
SELECT CAST(${val} AS DECIMAL);
----
100

query T
SELECT CAST(100.1::DECIMAL(4, 1) AS DECIMAL);
----
100

query T
SELECT CAST(100.1::REAL AS DECIMAL);
----
100

query T
SELECT CAST(100.1::FLOAT AS DECIMAL);
----
100

query T
SELECT CAST(100.1::DOUBLE AS DECIMAL);
----
100

query T
SELECT CAST(100.1234::DOUBLE AS DECIMAL(7, 2));
----
100.12

statement error: Character x is neither a decimal digit number, decimal point, nor "e" notation exponential mark.
SELECT CAST('100x' AS DECIMAL);

statement error: Cast function cannot convert value of type BINARY(1) to type DECIMAL
SELECT CAST(x'00' AS DECIMAL);

statement error: Cast function cannot convert value of type DATE to type DECIMAL
SELECT CAST('2000-01-01'::DATE AS DECIMAL);

statement error: Cast function cannot convert value of type TIME(0) to type DECIMAL
SELECT CAST('00:00:00'::TIME AS DECIMAL);

statement error: Cast function cannot convert value of type TIMESTAMP(6) to type DECIMAL
SELECT CAST('2000-01-01 00:00:00'::TIMESTAMP AS DECIMAL);

statement error: Cast function cannot convert value of type UUID to type DECIMAL
SELECT CAST('c4a0327c-44be-416d-ae90-75c05079789f'::UUID AS DECIMAL);

statement error: Cast function cannot convert value of type BINARY(1) to type DECIMAL
SELECT CAST(x'00'::BINARY AS DECIMAL);

statement error: Cast function cannot convert value of type VARBINARY to type DECIMAL
SELECT CAST(x'00'::VARBINARY AS DECIMAL);

for interval_type in [YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS]

query T
SELECT CAST(INTERVAL '11' ${interval_type} AS DECIMAL);
----
11

endfor

# Comparison ignores trailing zeros. It treats 11 as 11.00

for interval_type in [YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS]

query T
SELECT CAST(INTERVAL '11' ${interval_type} AS DECIMAL(5,2));
----
11

endfor

statement error: Cast function cannot convert value of type INTERVAL DAY TO HOUR to type DECIMAL
SELECT CAST(INTERVAL '0 01' DAYS TO HOURS AS DECIMAL(5));

statement error: Cast function cannot convert value of type INTERVAL DAY TO MINUTE to type DECIMAL
SELECT CAST(INTERVAL '0 01:02' DAYS TO MINUTES AS DECIMAL(5));

statement error: Cast function cannot convert value of type INTERVAL DAY TO SECOND to type DECIMAL
SELECT CAST(INTERVAL '0 01:02:03' DAYS TO SECONDS AS DECIMAL(5));

statement error: Cast function cannot convert value of type INTERVAL HOUR TO MINUTE to type DECIMAL
SELECT CAST(INTERVAL '00:01' HOURS TO MINUTES AS DECIMAL(5));

statement error: Cast function cannot convert value of type INTERVAL HOUR TO SECOND to type DECIMAL
SELECT CAST(INTERVAL '00:01' HOURS TO SECONDS AS DECIMAL(5));

statement error: Cast function cannot convert value of type INTERVAL MINUTE TO SECOND to type DECIMAL
SELECT CAST(INTERVAL '00:01' MINUTES TO SECONDS AS DECIMAL(5));

statement error: Cast function cannot convert value of type INTERVAL YEAR TO MONTH to type DECIMAL
SELECT CAST(INTERVAL '0-0' YEARS TO MONTHS AS DECIMAL(5));

statement error: Cast function cannot convert value of type INTERVAL YEAR TO MONTH to type DECIMAL
SELECT CAST(INTERVAL '0-0' YEARS TO MONTHS AS DECIMAL(5));

statement error: Cast function cannot convert value of type INTERVAL YEAR TO MONTH to type DECIMAL
SELECT CAST(INTERVAL '1-0' YEARS TO MONTHS AS DECIMAL(5));

statement error: Cast function cannot convert value of type INTERVAL YEAR TO MONTH to type DECIMAL
SELECT CAST(INTERVAL '1-1' YEARS TO MONTHS AS DECIMAL(5));

for interval_type in [YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS]

statement error: Numeric field overflow
SELECT CAST(INTERVAL '11' ${interval_type} AS DECIMAL(1));

endfor

statement error: Cast function cannot convert value of type INTERVAL DAY TO HOUR to type DECIMAL
SELECT CAST(INTERVAL '0 01' DAYS TO HOURS AS DECIMAL(1));

statement error: Cast function cannot convert value of type INTERVAL DAY TO MINUTE to type DECIMAL
SELECT CAST(INTERVAL '0 01:02' DAYS TO MINUTES AS DECIMAL(1));

statement error: Cast function cannot convert value of type INTERVAL DAY TO SECOND to type DECIMAL
SELECT CAST(INTERVAL '0 01:02:03' DAYS TO SECONDS AS DECIMAL(1));

statement error: Cast function cannot convert value of type INTERVAL HOUR TO MINUTE to type DECIMAL
SELECT CAST(INTERVAL '00:01' HOURS TO MINUTES AS DECIMAL(1));

statement error: Cast function cannot convert value of type INTERVAL HOUR TO SECOND to type DECIMAL
SELECT CAST(INTERVAL '00:01:02' HOURS TO SECONDS AS DECIMAL(1));

statement error: Cast function cannot convert value of type INTERVAL MINUTE TO SECOND to type DECIMAL
SELECT CAST(INTERVAL '00:01' MINUTES TO SECONDS AS DECIMAL(1));

statement error: Cast function cannot convert value of type INTERVAL YEAR TO MONTH to type DECIMAL
SELECT CAST(INTERVAL '1-1' YEARS TO MONTHS AS DECIMAL(1));