diff options
Diffstat (limited to 'mysql-test/main/cast.result')
| -rw-r--r-- | mysql-test/main/cast.result | 1279 |
1 files changed, 1279 insertions, 0 deletions
diff --git a/mysql-test/main/cast.result b/mysql-test/main/cast.result new file mode 100644 index 00000000000..ca314573581 --- /dev/null +++ b/mysql-test/main/cast.result @@ -0,0 +1,1279 @@ +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: +Note 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 +Warnings: +Warning 1365 Division by 0 +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 +Warnings: +Warning 1365 Division by 0 +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 38 +select cast(1 as double(64,63)); +ERROR 42000: Too big scale 63 specified for '1'. Maximum is 38 +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 DEFAULT NULL +) 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' +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +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, + `c2` varbinary(2) NOT NULL, + `c3` varbinary(2) NOT NULL, + `c4` varbinary(2) NOT NULL, + `c5` varbinary(2) NOT NULL +) 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(2) value: 'фгх' +Warning 1292 Truncated incorrect CHAR(2) value: 'Ñ„ ' +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +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(2) value: 'фгх' +Warning 1292 Truncated incorrect CHAR(2) 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, + `c2` varchar(2) CHARACTER SET utf8 NOT NULL, + `c3` varchar(2) CHARACTER SET utf8 NOT NULL, + `c4` varchar(2) CHARACTER SET utf8 NOT NULL, + `c5` varchar(2) CHARACTER SET utf8 NOT NULL +) 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, + `cast(1 as signed)` int(2) NOT NULL, + `cast(1 as double(5,2))` double(5,2) DEFAULT NULL, + `cast(1 as decimal(5,3))` decimal(5,3) NOT NULL, + `cast("A" as binary)` varbinary(1) DEFAULT NULL, + `cast("A" as char(100))` varbinary(100) DEFAULT NULL, + `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 1916 Got overflow when converting '-1e30' to INT. Value truncated +Warning 1916 Got overflow when converting '1e30' to INT. Value truncated +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' +connect newconn, localhost, root,,; +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 +connection default; +disconnect newconn; +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 +# +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +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, + `CONCAT(CAST(REPEAT('9', 1000) AS UNSIGNED))` varchar(20) NOT NULL +) 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' +create table t1 (a int, b char(5) as (cast("a" as char(10) binary) + a) ); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(5) GENERATED ALWAYS AS (cast('a' as char(10) charset latin1) + `a`) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select collation(cast("a" as char(10) binary)); +collation(cast("a" as char(10) binary)) +latin1_bin +select collation(cast("a" as char(10) charset utf8 binary)); +collation(cast("a" as char(10) charset utf8 binary)) +utf8_bin +select collation(cast("a" as char(10) ascii binary)); +collation(cast("a" as char(10) ascii binary)) +latin1_bin +select collation(cast("a" as char(10) binary charset utf8)); +collation(cast("a" as char(10) binary charset utf8)) +utf8_bin +select collation(cast("a" as char(10) binary ascii)); +collation(cast("a" as char(10) binary ascii)) +latin1_bin +# +# MDEV-11030 Assertion `precision > 0' failed in decimal_bin_size +# +SELECT * FROM (SELECT IFNULL(CONVERT(NULL, UNSIGNED), NULL)) sq; +IFNULL(CONVERT(NULL, UNSIGNED), NULL) +NULL +CREATE TABLE t1 AS SELECT IFNULL(CONVERT(NULL, UNSIGNED), NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `IFNULL(CONVERT(NULL, UNSIGNED), NULL)` decimal(1,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT COALESCE(CONVERT(NULL, UNSIGNED), NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `COALESCE(CONVERT(NULL, UNSIGNED), NULL)` decimal(1,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT CASE WHEN TRUE THEN CONVERT(NULL, UNSIGNED) ELSE NULL END; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `CASE WHEN TRUE THEN CONVERT(NULL, UNSIGNED) ELSE NULL END` decimal(1,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT IFNULL(CONVERT(NULL,SIGNED),CONVERT(NULL,UNSIGNED)) AS a; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` decimal(1,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +-1, +CONVERT(NULL,SIGNED), +CONCAT(CONVERT(NULL,SIGNED)), +1, +CONVERT(NULL,UNSIGNED), +CONCAT(CONVERT(NULL,UNSIGNED)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `-1` int(2) NOT NULL, + `CONVERT(NULL,SIGNED)` int(2) DEFAULT NULL, + `CONCAT(CONVERT(NULL,SIGNED))` varchar(2) DEFAULT NULL, + `1` int(1) NOT NULL, + `CONVERT(NULL,UNSIGNED)` int(1) unsigned DEFAULT NULL, + `CONCAT(CONVERT(NULL,UNSIGNED))` varchar(1) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +CONVERT('',SIGNED), +CONCAT(CONVERT('',SIGNED)), +CONVERT('',UNSIGNED), +CONCAT(CONVERT('',UNSIGNED)); +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `CONVERT('',SIGNED)` int(2) NOT NULL, + `CONCAT(CONVERT('',SIGNED))` varchar(2) NOT NULL, + `CONVERT('',UNSIGNED)` int(1) unsigned NOT NULL, + `CONCAT(CONVERT('',UNSIGNED))` varchar(1) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# MDEV-12849 Out-of-range errors when casting hex-hybrid to SIGNED and UNSIGNED +# +SET sql_mode=STRICT_ALL_TABLES; +CREATE PROCEDURE p1(hh TEXT) +BEGIN +EXECUTE IMMEDIATE +CONCAT('CREATE OR REPLACE TABLE t1 AS SELECT CAST(0x', hh, ' AS UNSIGNED) AS c'); +DESCRIBE t1; +SELECT c, LENGTH(c) FROM t1; +DROP TABLE t1; +EXECUTE IMMEDIATE +CONCAT('CREATE OR REPLACE TABLE t1 AS SELECT CAST(0x', hh, ' AS SIGNED) AS c'); +DESCRIBE t1; +SELECT c, LENGTH(c) FROM t1; +DROP TABLE t1; +SELECT '' AS `------`; +END +$$ +CALL p1('FF'); +Field Type Null Key Default Extra +c int(3) unsigned NO NULL +c LENGTH(c) +255 3 +Field Type Null Key Default Extra +c int(3) NO NULL +c LENGTH(c) +255 3 +------ + +CALL p1('FFFF'); +Field Type Null Key Default Extra +c int(5) unsigned NO NULL +c LENGTH(c) +65535 5 +Field Type Null Key Default Extra +c int(5) NO NULL +c LENGTH(c) +65535 5 +------ + +CALL p1('FFFFFF'); +Field Type Null Key Default Extra +c int(8) unsigned NO NULL +c LENGTH(c) +16777215 8 +Field Type Null Key Default Extra +c int(8) NO NULL +c LENGTH(c) +16777215 8 +------ + +CALL p1('FFFFFFFF'); +Field Type Null Key Default Extra +c int(10) unsigned NO NULL +c LENGTH(c) +4294967295 10 +Field Type Null Key Default Extra +c bigint(10) NO NULL +c LENGTH(c) +4294967295 10 +------ + +CALL p1('FFFFFFFFFF'); +Field Type Null Key Default Extra +c bigint(13) unsigned NO NULL +c LENGTH(c) +1099511627775 13 +Field Type Null Key Default Extra +c bigint(13) NO NULL +c LENGTH(c) +1099511627775 13 +------ + +CALL p1('FFFFFFFFFFFF'); +Field Type Null Key Default Extra +c bigint(15) unsigned NO NULL +c LENGTH(c) +281474976710655 15 +Field Type Null Key Default Extra +c bigint(15) NO NULL +c LENGTH(c) +281474976710655 15 +------ + +CALL p1('FFFFFFFFFFFFFF'); +Field Type Null Key Default Extra +c bigint(17) unsigned NO NULL +c LENGTH(c) +72057594037927935 17 +Field Type Null Key Default Extra +c bigint(17) NO NULL +c LENGTH(c) +72057594037927935 17 +------ + +CALL p1('FFFFFFFFFFFFFFFF'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +18446744073709551615 20 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-1 2 +------ + +CALL p1('FFFFFFFFFFFFFFFFFF'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +18446744073709551615 20 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-1 2 +------ + +CALL p1('FFFFFFFFFFFFFFFFFFFF'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +18446744073709551615 20 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-1 2 +------ + +CALL p1('8000000000000000'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +9223372036854775808 19 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-9223372036854775808 20 +------ + +CALL p1('80000000000000FF'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +9223372036854776063 19 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-9223372036854775553 20 +------ + +CALL p1('800000000000FFFF'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +9223372036854841343 19 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-9223372036854710273 20 +------ + +CALL p1('8000000000FFFFFF'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +9223372036871553023 19 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-9223372036837998593 20 +------ + +CALL p1('80000000FFFFFFFF'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +9223372041149743103 19 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-9223372032559808513 20 +------ + +CALL p1('800000FFFFFFFFFF'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +9223373136366403583 19 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-9223370937343148033 20 +------ + +CALL p1('8000FFFFFFFFFFFF'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +9223653511831486463 19 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-9223090561878065153 20 +------ + +CALL p1('80FFFFFFFFFFFFFF'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +9295429630892703743 19 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-9151314442816847873 20 +------ + +CALL p1('8FFFFFFFFFFFFFFF'); +Field Type Null Key Default Extra +c bigint(20) unsigned NO NULL +c LENGTH(c) +10376293541461622783 20 +Field Type Null Key Default Extra +c bigint(20) NO NULL +c LENGTH(c) +-8070450532247928833 20 +------ + +DROP PROCEDURE p1; +SET sql_mode=DEFAULT; +# +# MDEV-12852 Out-of-range errors when CAST(1-2 AS UNSIGNED +# +SET sql_mode=STRICT_ALL_TABLES; +CREATE TABLE t1 AS SELECT +CAST(-1 AS UNSIGNED), +CAST(1-2 AS UNSIGNED); +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 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `CAST(-1 AS UNSIGNED)` bigint(20) unsigned NOT NULL, + `CAST(1-2 AS UNSIGNED)` bigint(20) unsigned NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +CAST(-1 AS UNSIGNED) CAST(1-2 AS UNSIGNED) +18446744073709551615 18446744073709551615 +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# MDEV-12853 Out-of-range errors when CAST('-1' AS UNSIGNED +# +SET sql_mode=STRICT_ALL_TABLES; +CREATE TABLE t1 AS SELECT CAST('-1' AS UNSIGNED); +Warnings: +Note 1105 Cast to unsigned converted negative integer to it's positive complement +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `CAST('-1' AS UNSIGNED)` bigint(20) unsigned NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +CAST('-1' AS UNSIGNED) +18446744073709551615 +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# MDEV-14376 Explicit CAST(CHAR(N)) erroneously escalates warnings to errors in STRICT_ALL_TABLES +# +SET sql_mode=STRICT_ALL_TABLES; +SELECT CAST('xxx' AS CHAR(1)); +CAST('xxx' AS CHAR(1)) +x +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +CREATE OR REPLACE TABLE t1 (a VARCHAR(1)); +INSERT INTO t1 VALUES (CAST('xxx' AS CHAR(1))); +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a VARCHAR(3)); +INSERT INTO t1 VALUES ('xxx'); +UPDATE t1 SET a=CAST(a AS CHAR(1)); +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +DROP TABLE t1; +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET latin1; +SET a=CAST('xxx' AS CHAR(1)); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET latin1; +SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET latin1; +SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET utf8; +SET a=CAST('xxx' AS CHAR(1)); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET utf8; +SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET utf8; +SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +# Conversion problems still escalate warnings to errors (without right truncation) +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET utf8; +SET a=CAST(_utf8 0xD18F AS CHAR(1) CHARACTER SET latin1); +END; +$$ +ERROR HY000: Cannot convert 'utf8' character 0xD18F to 'latin1' +# Conversion problems still escalate warnings to errors (with right truncation) +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET utf8; +SET a=CAST(_utf8 0xD18FD18F AS CHAR(1) CHARACTER SET latin1); +END; +$$ +ERROR HY000: Cannot convert 'utf8' character 0xD18F to 'latin1' +# CAST(number AS CHAR) escalates warnings to errors on truncation +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES (CAST(123 AS CHAR(1))); +ERROR 22007: Truncated incorrect CHAR(1) value: '123' +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('1'); +UPDATE t1 SET a=CAST(123 AS CHAR(1)); +ERROR 22007: Truncated incorrect CHAR(1) value: '123' +DROP TABLE t1; +BEGIN NOT ATOMIC +DECLARE a VARCHAR(10); +SET a=CAST(123 AS CHAR(1)); +END; +$$ +ERROR 22007: Truncated incorrect CHAR(1) value: '123' +# CAST(temporal AS CHAR) escalates warnings to errors on truncation +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES (CAST(TIME'10:20:30' AS CHAR(1))); +ERROR 22007: Truncated incorrect CHAR(1) value: '10:20:30' +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('1'); +UPDATE t1 SET a=CAST(TIME'10:20:30' AS CHAR(1)); +ERROR 22007: Truncated incorrect CHAR(1) value: '10:20:30' +DROP TABLE t1; +BEGIN NOT ATOMIC +DECLARE a VARCHAR(10); +SET a=CAST(TIME'10:20:30' AS CHAR(1)); +END; +$$ +ERROR 22007: Truncated incorrect CHAR(1) value: '10:20:30' +SET sql_mode=DEFAULT; |