sql-tests/inputs/cast.sql

0001 -- cast string representing a valid fractional number to integral should truncate the number
0002 SELECT CAST('1.23' AS int);
0003 SELECT CAST('1.23' AS long);
0004 SELECT CAST('-4.56' AS int);
0005 SELECT CAST('-4.56' AS long);
0006
0007 -- cast string which are not numbers to integral should return null
0008 SELECT CAST('abc' AS int);
0009 SELECT CAST('abc' AS long);
0010
0011 -- cast string representing a very large number to integral should return null
0012 SELECT CAST('1234567890123' AS int);
0013 SELECT CAST('12345678901234567890123' AS long);
0014
0015 -- cast empty string to integral should return null
0016 SELECT CAST('' AS int);
0017 SELECT CAST('' AS long);
0018
0019 -- cast null to integral should return null
0020 SELECT CAST(NULL AS int);
0021 SELECT CAST(NULL AS long);
0022
0023 -- cast invalid decimal string to integral should return null
0024 SELECT CAST('123.a' AS int);
0025 SELECT CAST('123.a' AS long);
0026
0027 -- '-2147483648' is the smallest int value
0028 SELECT CAST('-2147483648' AS int);
0029 SELECT CAST('-2147483649' AS int);
0030
0031 -- '2147483647' is the largest int value
0032 SELECT CAST('2147483647' AS int);
0033 SELECT CAST('2147483648' AS int);
0034
0035 -- '-9223372036854775808' is the smallest long value
0036 SELECT CAST('-9223372036854775808' AS long);
0037 SELECT CAST('-9223372036854775809' AS long);
0038
0039 -- '9223372036854775807' is the largest long value
0040 SELECT CAST('9223372036854775807' AS long);
0041 SELECT CAST('9223372036854775808' AS long);
0042
0043 -- cast string to its binary representation
0044 SELECT HEX(CAST('abc' AS binary));
0045
0046 -- cast integral values to their corresponding binary representation
0047 SELECT HEX(CAST(CAST(123 AS byte) AS binary));
0048 SELECT HEX(CAST(CAST(-123 AS byte) AS binary));
0049 SELECT HEX(CAST(123S AS binary));
0050 SELECT HEX(CAST(-123S AS binary));
0051 SELECT HEX(CAST(123 AS binary));
0052 SELECT HEX(CAST(-123 AS binary));
0053 SELECT HEX(CAST(123L AS binary));
0054 SELECT HEX(CAST(-123L AS binary));
0055
0056 DESC FUNCTION boolean;
0057 DESC FUNCTION EXTENDED boolean;
0058 -- TODO: migrate all cast tests here.
0059
0060 -- cast string to interval and interval to string
0061 SELECT CAST('interval 3 month 1 hour' AS interval);
0062 SELECT CAST(interval 3 month 1 hour AS string);
0063
0064 -- trim string before cast to numeric
0065 select cast(' 1' as tinyint);
0066 select cast(' 1\t' as tinyint);
0067 select cast(' 1' as smallint);
0068 select cast(' 1' as INT);
0069 select cast(' 1' as bigint);
0070 select cast(' 1' as float);
0071 select cast(' 1 ' as DOUBLE);
0072 select cast('1.0 ' as DEC);
0073
0074 -- trim string before cast to boolean
0075 select cast('\t\t true \n\r ' as boolean);
0076 select cast('\t\n false \t\r' as boolean);
0077 select cast('\t\n xyz \t\r' as boolean);