github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_cast.result (about) 1 select CAST(1-2 AS UNSIGNED); 2 Data truncation: data out of range: data type uint64, 3 select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER); 4 Data truncation: data out of range: data type uint64, 5 select CAST('10 ' as unsigned integer); 6 cast(10 as integer unsigned) 7 10 8 select CAST('10x' as unsigned integer); 9 invalid argument cast to uint64, bad value 10x 10 select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1; 11 Data truncation: data out of range: data type uint64, 12 select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1; 13 Data truncation: data out of range: data type uint64, 14 select cast(5 as unsigned) -6.0; 15 cast(5 as unsigned) -6.0 16 -1.0 17 select cast(NULL as signed); 18 cast(NULL as signed) 19 null 20 select cast(1/2 as signed); 21 cast(1 / 2 as signed) 22 1 23 select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME); 24 cast("2001-1-1" as DATE) cast("2001-1-1" as DATETIME) 25 2001-01-01 2001-01-01 00:00:00 26 select 10+'10'; 27 10+'10' 28 20.0 29 select 10.0+'10'; 30 10.0+'10' 31 20.0 32 select 10E+0+'10'; 33 10E+0+'10' 34 20.0 35 select CAST(0xb3 as signed); 36 CAST(0xb3 as signed) 37 179 38 select CAST(0x8fffffffffffffff as signed); 39 Data truncation: data out of range: data type int, 40 select CAST(0xffffffffffffffff as unsigned); 41 CAST(0xffffffffffffffff as unsigned) 42 18446744073709551615 43 select CAST(0xfffffffffffffffe as signed); 44 Data truncation: data out of range: data type int, 45 select cast('-10a' as signed integer); 46 invalid argument cast to int, bad value -10a 47 select cast('a10' as unsigned integer); 48 invalid argument cast to uint64, bad value a10 49 select 10+'a'; 50 invalid argument cast to int, bad value a 51 select 10.0+cast('a' as decimal); 52 10.0+cast('a' as decimal) 53 10.0 54 select 10E+0+'a'; 55 10E+0+'a' 56 10.0 57 select cast('18446744073709551616' as unsigned); 58 Data truncation: data out of range: data type uint64, value '18446744073709551616' 59 select cast('18446744073709551616' as signed); 60 Data truncation: data out of range: data type int, value '18446744073709551616' 61 select cast('9223372036854775809' as signed); 62 Data truncation: data out of range: data type int, value '9223372036854775809' 63 select cast('-1' as unsigned); 64 invalid argument cast to uint64, bad value -1 65 select cast('abc' as signed); 66 invalid argument cast to int, bad value abc 67 select cast('1a' as signed); 68 invalid argument cast to int, bad value 1a 69 select cast('' as signed); 70 invalid argument cast to int, bad value 71 select cast("2001-1-1" as date) = "2001-01-01"; 72 cast("2001-1-1" as date) = "2001-01-01" 73 true 74 select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00"; 75 cast("2001-1-1" as datetime) = "2001-01-01 00:00:00" 76 true 77 select cast(NULL as DATE); 78 cast(NULL as DATE) 79 null 80 select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour); 81 date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour) 82 2004-12-30 00:00:00 83 select cast(18446744073709551615 as unsigned); 84 cast(18446744073709551615 as unsigned) 85 18446744073709551615 86 select cast(18446744073709551615 as signed); 87 Data truncation: data out of range: data type int64, 88 select cast('18446744073709551615' as unsigned); 89 cast('18446744073709551615' as unsigned) 90 18446744073709551615 91 select cast('18446744073709551615' as signed); 92 Data truncation: data out of range: data type int, value '18446744073709551615' 93 select cast('9223372036854775807' as signed); 94 cast('9223372036854775807' as signed) 95 9223372036854775807 96 select cast(concat_ws('184467440','73709551615') as unsigned); 97 cast(concat_ws('184467440','73709551615') as unsigned) 98 73709551615 99 select cast(concat_ws('184467440','73709551615') as signed); 100 cast(concat_ws('184467440','73709551615') as signed) 101 73709551615 102 select cast(1.0e+300 as signed int); 103 Data truncation: data out of range: data type int64, value '1e+300' 104 CREATE TABLE t1 (f1 double); 105 INSERT INTO t1 SET f1 = -1.0e+30 ; 106 INSERT INTO t1 SET f1 = +1.0e+30 ; 107 SELECT f1 AS double_val, CAST(f1 AS SIGNED INT) AS cast_val FROM t1; 108 Data truncation: data out of range: data type int64, value '-1e+30' 109 DROP TABLE t1; 110 SELECT CAST(cast('01-01-01' as date) AS UNSIGNED); 111 [42883]unsupported parameter types [DATE BIGINT UNSIGNED] for operator 'cast' 112 SELECT CAST(cast('01-01-01' as date) AS SIGNED); 113 [42883]unsupported parameter types [DATE BIGINT] for operator 'cast' 114 select cast('1.2' as decimal(3,2)); 115 cast('1.2' as decimal(3,2)) 116 1.20 117 select 1e18 * cast('1.2' as decimal(3,2)); 118 1e18 * cast('1.2' as decimal(3,2)) 119 1.2E18 120 select cast(cast('1.2' as decimal(3,2)) as signed); 121 cast(cast('1.2' as decimal(3,2)) as signed) 122 1 123 select cast(-1e18 as decimal(22,2)); 124 cast(-1e18 as decimal(22,2)) 125 -1000000000000000000.00 126 create table t1(s1 timestamp); 127 insert into t1 values ('2020-12-03 11:11:11'); 128 select cast(s1 as decimal(7,2)) from t1; 129 cast(s1 as decimal(7,2)) 130 99999.99 131 drop table t1; 132 CREATE TABLE t1 (v varchar(10), tt char(255), t char(255),mt char(255), lt char(255)); 133 INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05'); 134 SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL), CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1; 135 CAST(v AS DECIMAL) CAST(tt AS DECIMAL) CAST(t AS DECIMAL) CAST(mt AS DECIMAL) CAST(lt AS DECIMAL) 136 1 2 3 4 5 137 DROP TABLE t1; 138 select cast(NULL as decimal(6)) as t1; 139 t1 140 null 141 CREATE TABLE t1 (d1 datetime); 142 INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL), ('2007-07-19 08:34:00'), (NULL), ('2007-07-19 08:36:00'); 143 SELECT cast(date(d1) as signed) FROM t1; 144 cast(date(d1) as signed) 145 20070719 146 null 147 20070719 148 null 149 20070719 150 drop table t1; 151 SELECT CAST(1/3 AS FLOAT) as float_col,CAST(1/3 AS DOUBLE) as double_col, CAST(1/3 AS REAL) as real_col; 152 float_col double_col real_col 153 0.333333 0.333333333 0.333333333 154 SELECT CAST(DATE'2000-01-01' AS FLOAT), CAST(DATE'2000-01-01' AS DOUBLE); 155 invalid argument operator cast, bad value [DATE FLOAT] 156 SELECT CAST(TIMESTAMP'2000-01-01 23:59:59' AS FLOAT), CAST(TIMESTAMP'2000-01-01 23:59:59' AS DOUBLE); 157 invalid argument operator cast, bad value [TIMESTAMP FLOAT] 158 SELECT CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS FLOAT), CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS DOUBLE); 159 invalid argument operator cast, bad value [TIMESTAMP FLOAT] 160 SELECT CAST(NULL AS REAL), CAST(NULL AS FLOAT), CAST(NULL AS DOUBLE); 161 CAST(NULL AS REAL) CAST(NULL AS FLOAT) CAST(NULL AS DOUBLE) 162 null null null 163 CREATE TABLE t (col_datetime datetime, col_date date, col_char char); 164 insert into t values ('2013-03-15 18:35:20', '2013-03-15','L'),('2003-01-10 00:00:23', '2003-01-10', NULL); 165 SELECT CAST(col_char AS DATETIME) FROM t; 166 invalid input: invalid datatime value L 167 SELECT col_char <> col_datetime FROM t; 168 invalid input: invalid datatime value L 169 SELECT CAST(col_char AS DATE) FROM t; 170 invalid argument parsedate, bad value L 171 SELECT col_char <> col_date FROM t; 172 col_char <> col_date 173 1 174 null 175 DROP TABLE t; 176 CREATE TABLE `BB` (`col_char_key` char(1)); 177 CREATE TABLE `CC` ( `pk` int, `col_datetime_key` datetime); 178 INSERT INTO `BB` VALUES ('X'); 179 INSERT INTO `CC` VALUES (1,'2027-03-17 00:10:00'), (2,'2004-11-14 12:46:43'); 180 SELECT COUNT(table1.pk) FROM `CC` table1 JOIN `BB` table3 JOIN `CC` table2 181 WHERE (table3.col_char_key < table2.col_datetime_key); 182 COUNT(table1.pk) 183 4 184 DROP TABLE `BB`; 185 DROP TABLE `CC`; 186 create table t1(f1 date, f2 timestamp, f3 datetime); 187 insert into t1 values ("2006-01-01", "2006-01-01 12:01:01", "2006-01-01 12:01:01"); 188 insert into t1 values ("2006-01-02", "2006-01-02 12:01:02", "2006-01-02 12:01:02"); 189 select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date); 190 invalid argument operator cast, bad value [BIGINT DATE] 191 select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date); 192 invalid argument parsedate, bad value 2006.1.1 193 select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date); 194 invalid argument parsedate, bad value 2006.1.1 195 select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); 196 f3 197 2006-01-01 12:01:01 198 select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); 199 f3 200 2006-01-01 12:01:01 201 select f1 from t1 where cast("2006-1-1" as date) between f1 and f3; 202 f1 203 2006-01-01 204 select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3); 205 f1 206 2006-01-01 207 select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date); 208 invalid argument parsedate, bad value zzz 209 drop table t1; 210 create table t1 (field DATE); 211 insert into t1 values ('2006-11-06'); 212 select * from t1 where field < '2006-11-06 04:08:36.0'; 213 field 214 2006-11-06 215 select * from t1 where field = '2006-11-06 04:08:36.0'; 216 field 217 select * from t1 where field = '2006-11-06'; 218 field 219 2006-11-06 220 select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0'; 221 field 222 2006-11-06 223 select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0'; 224 field 225 2006-11-06 226 drop table t1; 227 create table t1 (a int(11) unsigned, b int(11) unsigned); 228 insert into t1 values (1,0), (1,1), (4294967295,1); 229 select a-b from t1 order by 1; 230 a-b 231 0 232 1 233 4294967294 234 select a-b , (a-b < 0) from t1 order by 1; 235 a - b a - b < 0 236 0 false 237 1 false 238 4294967294 false 239 select any_value(a)-b as d, (any_value(a)-b >= 0), b from t1 group by b having d >= 0; 240 SQL syntax error: column "d" must appear in the GROUP BY clause or be used in an aggregate function 241 select cast((a - b) as unsigned) from t1 order by 1; 242 cast((a - b) as unsigned) 243 0 244 1 245 4294967294 246 drop table t1; 247 select if(1, cast(1111111111111111111 as unsigned), 1) i, case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, coalesce(cast(1111111111111111111 as unsigned), 1) co; 248 i c co 249 1111111111111111111 1111111111111111111 1111111111111111111