SET timestamp=unix_timestamp('2001-02-03 10:20:30'); select CAST(1-2 AS UNSIGNED); CAST(1-2 AS UNSIGNED) 18446744073709551615 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER); CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER) -1 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement select CAST('10 ' as unsigned integer); CAST('10 ' as unsigned integer) 10 Warnings: Warning 1292 Truncated incorrect INTEGER value: '10 ' select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1; cast(-5 as unsigned) | 1 cast(-5 as unsigned) & -1 18446744073709551611 18446744073709551611 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement Note 1105 Cast to unsigned converted negative integer to it's positive complement select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1; cast(-5 as unsigned) -1 cast(-5 as unsigned) + 1 18446744073709551610 18446744073709551612 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement Note 1105 Cast to unsigned converted negative integer to it's positive complement select ~5, cast(~5 as signed); ~5 cast(~5 as signed) 18446744073709551610 -6 explain extended select ~5, cast(~5 as signed); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select ~(5) AS `~5`,cast(~(5) as signed) AS `cast(~5 as signed)` select cast(18446744073709551615 as signed); cast(18446744073709551615 as signed) -1 select cast(5 as unsigned) -6.0; cast(5 as unsigned) -6.0 -1.0 select cast(NULL as signed), cast(1/0 as signed); cast(NULL as signed) cast(1/0 as signed) NULL NULL select cast(1 as double(5,2)); cast(1 as double(5,2)) 1.00 select cast("5.2222" as double(5,2)); cast("5.2222" as double(5,2)) 5.22 select cast(12.444 as double(5,2)); cast(12.444 as double(5,2)) 12.44 select cast(cast(12.444 as decimal(10,3)) as double(5,2)); cast(cast(12.444 as decimal(10,3)) as double(5,2)) 12.44 select cast(null as double(5,2)); cast(null as double(5,2)) NULL select cast(12.444 as double); cast(12.444 as double) 12.444 select cast(cast("20:01:01" as time) as datetime); cast(cast("20:01:01" as time) as datetime) 2001-02-03 20:01:01 select cast(cast("8:46:06.23434" AS time) as decimal(32,10)); cast(cast("8:46:06.23434" AS time) as decimal(32,10)) 84606.0000000000 select cast(cast("2011-04-05 8:46:06.23434" AS datetime) as decimal(32,6)); cast(cast("2011-04-05 8:46:06.23434" AS datetime) as decimal(32,6)) 20110405084606.000000 # # Check handling of cast with microseconds # select cast(cast(20010203101112.121314 as double) as datetime); cast(cast(20010203101112.121314 as double) as datetime) 2001-02-03 10:11:12 select cast(cast(010203101112.12 as double) as datetime); cast(cast(010203101112.12 as double) as datetime) 2001-02-03 10:11:12 select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime); cast(cast(20010203101112.121314 as decimal(32,6)) as datetime) 2001-02-03 10:11:12 select cast(20010203101112.121314 as datetime); cast(20010203101112.121314 as datetime) 2001-02-03 10:11:12 select cast(110203101112.121314 as datetime); cast(110203101112.121314 as datetime) 2011-02-03 10:11:12 select cast(cast(010203101112.12 as double) as datetime); cast(cast(010203101112.12 as double) as datetime) 2001-02-03 10:11:12 select cast("2011-02-03 10:11:12.123456" as datetime); cast("2011-02-03 10:11:12.123456" as datetime) 2011-02-03 10:11:12 select cast("2011-02-03 10:11:12.123456" as datetime(0)); cast("2011-02-03 10:11:12.123456" as datetime(0)) 2011-02-03 10:11:12 select cast("2011-02-03 10:11:12.123456" as datetime(5)); cast("2011-02-03 10:11:12.123456" as datetime(5)) 2011-02-03 10:11:12.12345 select cast("2011-02-03 10:11:12.123456" as datetime(6)); cast("2011-02-03 10:11:12.123456" as datetime(6)) 2011-02-03 10:11:12.123456 select cast("2011-02-03 10:11:12" as datetime(6)); cast("2011-02-03 10:11:12" as datetime(6)) 2011-02-03 10:11:12.000000 select cast(cast(20010203101112.5 as double) as datetime(1)); cast(cast(20010203101112.5 as double) as datetime(1)) 2001-02-03 10:11:12.5 select cast(cast(010203101112.12 as double) as datetime(2)); cast(cast(010203101112.12 as double) as datetime(2)) 2001-02-03 10:11:12.12 select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6)); cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6)) 2001-02-03 10:11:12.121314 select cast(20010203101112.121314 as datetime(6)); cast(20010203101112.121314 as datetime(6)) 2001-02-03 10:11:12.121314 select cast(110203101112.121314 as datetime(6)); cast(110203101112.121314 as datetime(6)) 2011-02-03 10:11:12.121314 select cast(cast(010203101112.12 as double) as datetime(6)); cast(cast(010203101112.12 as double) as datetime(6)) 2001-02-03 10:11:12.120000 select cast("2011-02-03 10:11:12.123456" as time); cast("2011-02-03 10:11:12.123456" as time) 10:11:12 select cast("2011-02-03 10:11:12.123456" as time(6)); cast("2011-02-03 10:11:12.123456" as time(6)) 10:11:12.123456 select cast("10:11:12.123456" as time); cast("10:11:12.123456" as time) 10:11:12 select cast("10:11:12.123456" as time(0)); cast("10:11:12.123456" as time(0)) 10:11:12 select cast("10:11:12.123456" as time(5)); cast("10:11:12.123456" as time(5)) 10:11:12.12345 select cast("10:11:12.123456" as time(6)); cast("10:11:12.123456" as time(6)) 10:11:12.123456 select cast("10:11:12" as time(6)); cast("10:11:12" as time(6)) 10:11:12.000000 select cast(cast("2011-04-05 8:46:06.123456" AS datetime) as time); cast(cast("2011-04-05 8:46:06.123456" AS datetime) as time) 08:46:06 select cast(cast("2011-04-05 8:46:06.123456" AS datetime) as time(6)); cast(cast("2011-04-05 8:46:06.123456" AS datetime) as time(6)) 08:46:06.000000 select cast(cast("2011-04-05 8:46:06.123456" AS datetime(6)) as time); cast(cast("2011-04-05 8:46:06.123456" AS datetime(6)) as time) 08:46:06 select cast(cast("2011-04-05 8:46:06.123456" AS datetime(6)) as time(6)); cast(cast("2011-04-05 8:46:06.123456" AS datetime(6)) as time(6)) 08:46:06.123456 select cast(NULL as unsigned), cast(1/0 as unsigned); cast(NULL as unsigned) cast(1/0 as unsigned) NULL NULL select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A"; cast("A" as binary) = "a" cast(BINARY "a" as CHAR) = "A" 0 1 select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME); cast("2001-1-1" as DATE) cast("2001-1-1" as DATETIME) 2001-01-01 2001-01-01 00:00:00 select cast("1:2:3" as TIME); cast("1:2:3" as TIME) 01:02:03 select CONVERT("2004-01-22 21:45:33",DATE); CONVERT("2004-01-22 21:45:33",DATE) 2004-01-22 select 10+'10'; 10+'10' 20 select 10.0+'10'; 10.0+'10' 20 select 10E+0+'10'; 10E+0+'10' 20 select CONVERT(TIMESTAMP "2004-01-22 21:45:33" USING latin1); CONVERT(TIMESTAMP "2004-01-22 21:45:33" USING latin1) 2004-01-22 21:45:33 select CONVERT(TIMESTAMP "2004-01-22 21:45:33",CHAR); CONVERT(TIMESTAMP "2004-01-22 21:45:33",CHAR) 2004-01-22 21:45:33 select CONVERT(TIMESTAMP "2004-01-22 21:45:33",CHAR(4)); CONVERT(TIMESTAMP "2004-01-22 21:45:33",CHAR(4)) 2004 Warnings: Warning 1292 Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33' select CONVERT(TIMESTAMP "2004-01-22 21:45:33",BINARY(4)); CONVERT(TIMESTAMP "2004-01-22 21:45:33",BINARY(4)) 2004 Warnings: Warning 1292 Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33' select CAST(TIMESTAMP "2004-01-22 21:45:33" AS BINARY(4)); CAST(TIMESTAMP "2004-01-22 21:45:33" AS BINARY(4)) 2004 Warnings: Warning 1292 Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33' select CAST(0xb3 as signed); CAST(0xb3 as signed) 179 select CAST(0x8fffffffffffffff as signed); CAST(0x8fffffffffffffff as signed) -8070450532247928833 select CAST(0xffffffffffffffff as unsigned); CAST(0xffffffffffffffff as unsigned) 18446744073709551615 select CAST(0xfffffffffffffffe as signed); CAST(0xfffffffffffffffe as signed) -2 select cast('-10a' as signed integer); cast('-10a' as signed integer) -10 Warnings: Warning 1292 Truncated incorrect INTEGER value: '-10a' select cast('a10' as unsigned integer); cast('a10' as unsigned integer) 0 Warnings: Warning 1292 Truncated incorrect INTEGER value: 'a10' select 10+'a'; 10+'a' 10 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'a' select 10.0+cast('a' as decimal); 10.0+cast('a' as decimal) 10.0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'a' select 10E+0+'a'; 10E+0+'a' 10 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'a' select cast("a" as double(5,2)); cast("a" as double(5,2)) 0.00 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'a' select cast(1000 as decimal(5,2)); cast(1000 as decimal(5,2)) 999.99 Warnings: Warning 1264 Out of range value for column 'cast(1000 as decimal(5,2))' at row 1 select cast(-1000 as decimal(5,2)); cast(-1000 as decimal(5,2)) -999.99 Warnings: Warning 1264 Out of range value for column 'cast(-1000 as decimal(5,2))' at row 1 select cast(1000 as double(5,2)); cast(1000 as double(5,2)) 999.99 Warnings: Warning 1264 Out of range value for column 'cast(1000 as double(5,2))' at row 1 select cast(-1000 as double(5,2)); cast(-1000 as double(5,2)) -999.99 Warnings: Warning 1264 Out of range value for column 'cast(-1000 as double(5,2))' at row 1 select cast(010203101112.121314 as datetime); cast(010203101112.121314 as datetime) 2001-02-03 10:11:12 select cast(120010203101112.121314 as datetime); cast(120010203101112.121314 as datetime) NULL Warnings: Warning 1292 Incorrect datetime value: '120010203101112.121314' select cast(cast(1.1 as decimal) as datetime); cast(cast(1.1 as decimal) as datetime) NULL Warnings: Warning 1292 Incorrect datetime value: '1' select cast(cast(-1.1 as decimal) as datetime); cast(cast(-1.1 as decimal) as datetime) NULL Warnings: Warning 1292 Incorrect datetime value: '-1' select cast('0' as date); cast('0' as date) NULL Warnings: Warning 1292 Incorrect datetime value: '0' select cast('' as date); cast('' as date) NULL Warnings: Warning 1292 Incorrect datetime value: '' select cast('0' as datetime); cast('0' as datetime) NULL Warnings: Warning 1292 Incorrect datetime value: '0' select cast('' as datetime); cast('' as datetime) NULL Warnings: Warning 1292 Incorrect datetime value: '' select cast('0' as time); cast('0' as time) 00:00:00 select cast('' as time); cast('' as time) NULL Warnings: Warning 1292 Truncated incorrect time value: '' select cast(NULL as DATE); cast(NULL as DATE) NULL select cast(NULL as DATETIME); cast(NULL as DATETIME) NULL select cast(NULL as TIME); cast(NULL as TIME) NULL select cast(NULL as BINARY); cast(NULL as BINARY) NULL select cast(cast(120010203101112.121314 as double) as datetime); cast(cast(120010203101112.121314 as double) as datetime) NULL select cast(cast(1.1 as double) as datetime); cast(cast(1.1 as double) as datetime) NULL select cast(cast(-1.1 as double) as datetime); cast(cast(-1.1 as double) as datetime) NULL explain extended select cast(10 as double(5,2)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select cast(10 as double(5,2)) AS `cast(10 as double(5,2))` explain extended select cast(10 as double); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select cast(10 as double) AS `cast(10 as double)` explain extended select cast(10 as decimal(5,2)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select cast(10 as decimal(5,2)) AS `cast(10 as decimal(5,2))` select cast('18446744073709551616' as unsigned); cast('18446744073709551616' as unsigned) 18446744073709551615 Warnings: Warning 1292 Truncated incorrect INTEGER value: '18446744073709551616' select cast('18446744073709551616' as signed); cast('18446744073709551616' as signed) -1 Warnings: Warning 1292 Truncated incorrect INTEGER value: '18446744073709551616' select cast('9223372036854775809' as signed); cast('9223372036854775809' as signed) -9223372036854775807 Warnings: Note 1105 Cast to signed converted positive out-of-range integer to it's negative complement select cast('-1' as unsigned); cast('-1' as unsigned) 18446744073709551615 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement select cast('abc' as signed); cast('abc' as signed) 0 Warnings: Warning 1292 Truncated incorrect INTEGER value: 'abc' select cast('1a' as signed); cast('1a' as signed) 1 Warnings: Warning 1292 Truncated incorrect INTEGER value: '1a' select cast('' as signed); cast('' as signed) 0 Warnings: Warning 1292 Truncated incorrect INTEGER value: '' select cast(1 as double(5,6)); ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column ''). select cast(1 as decimal(5,6)); ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column ''). select cast(1 as double(66,6)); ERROR 42000: Too big precision 66 specified for '1'. Maximum is 65. select cast(1 as decimal(66,6)); ERROR 42000: Too big precision 66 specified for '1'. Maximum is 65. select cast(1 as decimal(64,63)); ERROR 42000: Too big scale 63 specified for '1'. Maximum is 30. select cast(1 as double(64,63)); ERROR 42000: Too big scale 63 specified for '1'. Maximum is 30. set names binary; select cast(_latin1'test' as char character set latin2); cast(_latin1'test' as char character set latin2) test select cast(_koi8r'ÔÅÓÔ' as char character set cp1251); cast(_koi8r'ÔÅÓÔ' as char character set cp1251) òåñò create table t1 select cast(_koi8r'ÔÅÓÔ' as char character set cp1251) as t; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `t` varchar(4) CHARACTER SET cp1251 NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select cast(_latin1'ab' AS char) as c1, cast(_latin1'a ' AS char) as c2, cast(_latin1'abc' AS char(2)) as c3, cast(_latin1'a ' AS char(2)) as c4, hex(cast(_latin1'a' AS char(2))) as c5; c1 c2 c3 c4 c5 ab a ab a 6100 Warnings: Warning 1292 Truncated incorrect BINARY(2) value: 'abc' Warning 1292 Truncated incorrect BINARY(2) value: 'a ' select cast(1000 as CHAR(3)); cast(1000 as CHAR(3)) 100 Warnings: Warning 1292 Truncated incorrect BINARY(3) value: '1000' create table t1 select cast(_latin1'ab' AS char) as c1, cast(_latin1'a ' AS char) as c2, cast(_latin1'abc' AS char(2)) as c3, cast(_latin1'a ' AS char(2)) as c4, cast(_latin1'a' AS char(2)) as c5; Warnings: Warning 1292 Truncated incorrect BINARY(2) value: 'abc' Warning 1292 Truncated incorrect BINARY(2) value: 'a ' select c1,c2,c3,c4,hex(c5) from t1; c1 c2 c3 c4 hex(c5) ab a ab a 6100 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` varbinary(2) NOT NULL DEFAULT '', `c2` varbinary(2) NOT NULL DEFAULT '', `c3` varbinary(2) NOT NULL DEFAULT '', `c4` varbinary(2) NOT NULL DEFAULT '', `c5` varbinary(2) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select cast(_koi8r'ÆÇ' AS nchar) as c1, cast(_koi8r'Æ ' AS nchar) as c2, cast(_koi8r'ÆÇÈ' AS nchar(2)) as c3, cast(_koi8r'Æ ' AS nchar(2)) as c4, cast(_koi8r'Æ' AS nchar(2)) as c5; c1 c2 c3 c4 c5 фг Ñ„ фг Ñ„ Ñ„ Warnings: Warning 1292 Truncated incorrect CHAR(4) value: 'фгх' Warning 1292 Truncated incorrect CHAR(3) value: 'Ñ„ ' create table t1 select cast(_koi8r'ÆÇ' AS nchar) as c1, cast(_koi8r'Æ ' AS nchar) as c2, cast(_koi8r'ÆÇÈ' AS nchar(2)) as c3, cast(_koi8r'Æ ' AS nchar(2)) as c4, cast(_koi8r'Æ' AS nchar(2)) as c5; Warnings: Warning 1292 Truncated incorrect CHAR(4) value: 'фгх' Warning 1292 Truncated incorrect CHAR(3) value: 'Ñ„ ' select * from t1; c1 c2 c3 c4 c5 фг Ñ„ фг Ñ„ Ñ„ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '', `c2` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '', `c3` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '', `c4` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '', `c5` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a binary(4), b char(4) character set koi8r); insert into t1 values (_binary'ÔÅÓÔ',_binary'ÔÅÓÔ'); select a,b,cast(a as char character set cp1251),cast(b as binary) from t1; a b cast(a as char character set cp1251) cast(b as binary) ÔÅÓÔ ÔÅÓÔ ÔÅÓÔ ÔÅÓÔ set names koi8r; select a,b,cast(a as char character set cp1251),cast(b as binary) from t1; a b cast(a as char character set cp1251) cast(b as binary) ÔÅÓÔ ÔÅÓÔ æåõæ ÔÅÓÔ set names cp1251; select a,b,cast(a as char character set cp1251),cast(b as binary) from t1; a b cast(a as char character set cp1251) cast(b as binary) ÔÅÓÔ òåñò ÔÅÓÔ ÔÅÓÔ drop table t1; set names binary; select cast("2001-1-1" as date) = "2001-01-01"; cast("2001-1-1" as date) = "2001-01-01" 1 select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00"; cast("2001-1-1" as datetime) = "2001-01-01 00:00:00" 1 select cast("1:2:3" as TIME) = "1:02:03"; cast("1:2:3" as TIME) = "1:02:03" 1 CREATE TABLE t1 (a enum ('aac','aab','aaa') not null); INSERT INTO t1 VALUES ('aaa'),('aab'),('aac'); SELECT a, CAST(a AS CHAR) FROM t1 ORDER BY CAST(a AS UNSIGNED) ; a CAST(a AS CHAR) aac aac aab aab aaa aaa SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a; a CAST(a AS CHAR(3)) aac aac aab aab aaa aaa Warnings: Warning 1292 Truncated incorrect BINARY(2) value: 'aaa' Warning 1292 Truncated incorrect BINARY(2) value: 'aab' Warning 1292 Truncated incorrect BINARY(2) value: 'aac' SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ; a CAST(a AS UNSIGNED) aaa 3 aab 2 aac 1 SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a; a CAST(a AS CHAR(2)) aaa aa aab aa aac aa Warnings: Warning 1292 Truncated incorrect BINARY(2) value: 'aaa' Warning 1292 Truncated incorrect BINARY(2) value: 'aab' Warning 1292 Truncated incorrect BINARY(2) value: 'aac' DROP TABLE t1; select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour); date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour) 2004-12-30 00:00:00 select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00'); timediff(cast('2004-12-30 12:00:00' as time), '12:00:00') 00:00:00 select timediff(cast('1 12:00:00' as time), '12:00:00'); timediff(cast('1 12:00:00' as time), '12:00:00') 24:00:00 select cast(18446744073709551615 as unsigned); cast(18446744073709551615 as unsigned) 18446744073709551615 select cast(18446744073709551615 as signed); cast(18446744073709551615 as signed) -1 select cast('18446744073709551615' as unsigned); cast('18446744073709551615' as unsigned) 18446744073709551615 select cast('18446744073709551615' as signed); cast('18446744073709551615' as signed) -1 Warnings: Note 1105 Cast to signed converted positive out-of-range integer to it's negative complement select cast('9223372036854775807' as signed); cast('9223372036854775807' as signed) 9223372036854775807 select cast(concat('184467440','73709551615') as unsigned); cast(concat('184467440','73709551615') as unsigned) 18446744073709551615 select cast(concat('184467440','73709551615') as signed); cast(concat('184467440','73709551615') as signed) -1 Warnings: Note 1105 Cast to signed converted positive out-of-range integer to it's negative complement select cast(repeat('1',20) as unsigned); cast(repeat('1',20) as unsigned) 11111111111111111111 select cast(repeat('1',20) as signed); cast(repeat('1',20) as signed) -7335632962598440505 Warnings: Note 1105 Cast to signed converted positive out-of-range integer to it's negative complement select cast(1.0e+300 as signed int); cast(1.0e+300 as signed int) 9223372036854775807 create table t1 select cast(1 as unsigned), cast(1 as signed), cast(1 as double(5,2)), cast(1 as decimal(5,3)), cast("A" as binary), cast("A" as char(100)), cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME), cast("1:2:3" as TIME); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `cast(1 as unsigned)` int(1) unsigned NOT NULL DEFAULT '0', `cast(1 as signed)` int(1) NOT NULL DEFAULT '0', `cast(1 as double(5,2))` double(5,2) DEFAULT NULL, `cast(1 as decimal(5,3))` decimal(5,3) NOT NULL DEFAULT '0.000', `cast("A" as binary)` varbinary(1) NOT NULL DEFAULT '', `cast("A" as char(100))` varbinary(100) NOT NULL DEFAULT '', `cast("2001-1-1" as DATE)` date DEFAULT NULL, `cast("2001-1-1" as DATETIME)` datetime DEFAULT NULL, `cast("1:2:3" as TIME)` time DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; CREATE TABLE t1 (f1 double); INSERT INTO t1 SET f1 = -1.0e+30 ; INSERT INTO t1 SET f1 = +1.0e+30 ; SELECT f1 AS double_val, CAST(f1 AS SIGNED INT) AS cast_val FROM t1; double_val cast_val -1e30 -9223372036854775808 1e30 9223372036854775807 Warnings: Warning 1292 Truncated incorrect INTEGER value: '-1e30' Warning 1292 Truncated incorrect INTEGER value: '1e30' DROP TABLE t1; select isnull(date(NULL)), isnull(cast(NULL as DATE)); isnull(date(NULL)) isnull(cast(NULL as DATE)) 1 1 SELECT CAST(cast('01-01-01' as date) AS UNSIGNED); CAST(cast('01-01-01' as date) AS UNSIGNED) 20010101 SELECT CAST(cast('01-01-01' as date) AS SIGNED); CAST(cast('01-01-01' as date) AS SIGNED) 20010101 End of 4.1 tests select cast('1.2' as decimal(3,2)); cast('1.2' as decimal(3,2)) 1.20 select 1e18 * cast('1.2' as decimal(3,2)); 1e18 * cast('1.2' as decimal(3,2)) 1.2e18 select cast(cast('1.2' as decimal(3,2)) as signed); cast(cast('1.2' as decimal(3,2)) as signed) 1 set @v1=1e18; select cast(@v1 as decimal(22, 2)); cast(@v1 as decimal(22, 2)) 1000000000000000000.00 select cast(-1e18 as decimal(22,2)); cast(-1e18 as decimal(22,2)) -1000000000000000000.00 create table t1(s1 time); insert into t1 values ('11:11:11'); select cast(s1 as decimal(7,2)) from t1; cast(s1 as decimal(7,2)) 99999.99 Warnings: Warning 1264 Out of range value for column 'cast(s1 as decimal(7,2))' at row 1 drop table t1; CREATE TABLE t1 (v varchar(10), tt tinytext, t text, mt mediumtext, lt longtext); INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05'); SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL), CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1; CAST(v AS DECIMAL) CAST(tt AS DECIMAL) CAST(t AS DECIMAL) CAST(mt AS DECIMAL) CAST(lt AS DECIMAL) 1 2 3 4 5 DROP TABLE t1; select cast(NULL as decimal(6)) as t1; t1 NULL set names latin1; select hex(cast('a' as char(2) binary)); hex(cast('a' as char(2) binary)) 61 select hex(cast('a' as binary(2))); hex(cast('a' as binary(2))) 6100 select hex(cast('a' as char(2) binary)); hex(cast('a' as char(2) binary)) 61 CREATE TABLE t1 (d1 datetime); 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'); SELECT cast(date(d1) as signed) FROM t1; cast(date(d1) as signed) 20070719 NULL 20070719 NULL 20070719 drop table t1; CREATE TABLE t1 (f1 DATE); INSERT INTO t1 VALUES ('2007-07-19'), (NULL); SELECT HOUR(f1), MINUTE(f1), SECOND(f1) FROM t1; HOUR(f1) MINUTE(f1) SECOND(f1) 0 0 0 NULL NULL NULL SELECT HOUR(CAST('2007-07-19' AS DATE)), MINUTE(CAST('2007-07-19' AS DATE)), SECOND(CAST('2007-07-19' AS DATE)); HOUR(CAST('2007-07-19' AS DATE)) MINUTE(CAST('2007-07-19' AS DATE)) SECOND(CAST('2007-07-19' AS DATE)) 0 0 0 SELECT HOUR(CAST(NULL AS DATE)), MINUTE(CAST(NULL AS DATE)), SECOND(CAST(NULL AS DATE)); HOUR(CAST(NULL AS DATE)) MINUTE(CAST(NULL AS DATE)) SECOND(CAST(NULL AS DATE)) NULL NULL NULL SELECT HOUR(NULL), MINUTE(NULL), SECOND(NULL); HOUR(NULL) MINUTE(NULL) SECOND(NULL) NULL NULL NULL DROP TABLE t1; End of 5.0 tests # # Bug #44766: valgrind error when using convert() in a subquery # CREATE TABLE t1(a tinyint); INSERT INTO t1 VALUES (127); SELECT 1 FROM ( SELECT CONVERT(t2.a USING UTF8) FROM t1, t1 t2 LIMIT 1 ) AS s LIMIT 1; 1 1 DROP TABLE t1; # # Bug #11765023: 57934: DOS POSSIBLE SINCE BINARY CASTING # DOESN'T ADHERE TO MAX_ALLOWED_PACKET SET @@GLOBAL.max_allowed_packet=2048; Warnings: Warning 1708 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length' SELECT CONVERT('a', BINARY(2049)); CONVERT('a', BINARY(2049)) a Warnings: Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (2048) - truncated SELECT CONVERT('a', CHAR(2049)); CONVERT('a', CHAR(2049)) a Warnings: Warning 1301 Result of cast_as_char() was larger than max_allowed_packet (2048) - truncated SELECT length(CONVERT(repeat('a',2048), CHAR(2049))); length(CONVERT(repeat('a',2048), CHAR(2049))) 2048 Warnings: Warning 1301 Result of cast_as_char() was larger than max_allowed_packet (2048) - truncated SET @@GLOBAL.max_allowed_packet=default; # # Bug#13519724 63793: CRASH IN DTCOLLATION::SET(DTCOLLATION &SET) # CREATE TABLE t1 (a VARCHAR(50)); SELECT a FROM t1 WHERE CAST(a as BINARY)=x'62736D697468' AND CAST(a AS BINARY)=x'65736D697468'; a DROP TABLE t1; # # Bug#13581962 HIGH MEMORY USAGE ATTEMPT, THEN CRASH WITH # LONGTEXT, UNION, USER VARIABLE # Bug#14096619 UNABLE TO RESTORE DATABASE DUMP # CREATE TABLE t1 AS SELECT CONCAT(CAST(REPEAT('9', 1000) AS SIGNED)), CONCAT(CAST(REPEAT('9', 1000) AS UNSIGNED)); Warnings: Warning 1292 Truncated incorrect INTEGER value: '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999' Warning 1292 Truncated incorrect INTEGER value: '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `CONCAT(CAST(REPEAT('9', 1000) AS SIGNED))` varchar(21) NOT NULL DEFAULT '', `CONCAT(CAST(REPEAT('9', 1000) AS UNSIGNED))` varchar(21) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # End of test for Bug#13581962, Bug#14096619 End of 5.1 tests select cast("2101-00-01 02:03:04" as datetime); cast("2101-00-01 02:03:04" as datetime) 2101-00-01 02:03:04 select cast(cast("2101-00-01 02:03:04" as datetime) as time); cast(cast("2101-00-01 02:03:04" as datetime) as time) 02:03:04 SELECT CAST(CAST('20:05:05' AS TIME) as date); CAST(CAST('20:05:05' AS TIME) as date) 2001-02-03 set sql_mode= TRADITIONAL; select cast("2101-00-01 02:03:04" as datetime); cast("2101-00-01 02:03:04" as datetime) NULL Warnings: Warning 1292 Incorrect datetime value: '2101-00-01 02:03:04' select cast(cast("2101-00-01 02:03:04" as datetime) as time); cast(cast("2101-00-01 02:03:04" as datetime) as time) NULL Warnings: Warning 1292 Incorrect datetime value: '2101-00-01 02:03:04' SELECT CAST(CAST('20:05:05' AS TIME) as date); CAST(CAST('20:05:05' AS TIME) as date) 2001-02-03 set sql_mode=DEFAULT; create table t1 (f1 time, f2 date, f3 datetime); insert into t1 values ('11:22:33','2011-12-13','2011-12-13 11:22:33'); select cast(f1 as unsigned), cast(f2 as unsigned), cast(f3 as unsigned) from t1; cast(f1 as unsigned) cast(f2 as unsigned) cast(f3 as unsigned) 112233 20111213 20111213112233 drop table t1; SELECT CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY; CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY 2001-02-04 SET SQL_MODE=ALLOW_INVALID_DATES; SELECT DATE("foo"); DATE("foo") NULL Warnings: Warning 1292 Incorrect datetime value: 'foo'