summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/cast.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/cast.result')
-rw-r--r--mysql-test/main/cast.result1380
1 files changed, 1380 insertions, 0 deletions
diff --git a/mysql-test/main/cast.result b/mysql-test/main/cast.result
new file mode 100644
index 00000000..4f48e9e0
--- /dev/null
+++ b/mysql-test/main/cast.result
@@ -0,0 +1,1380 @@
+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)) as exp;
+exp
+20110405084606.000000
+#
+# Check handling of cast with microseconds
+#
+select cast(cast(20010203101112.121314 as double) as datetime) as exp;
+exp
+2001-02-03 10:11:12
+select cast(cast(010203101112.12 as double) as datetime) as exp;
+exp
+2001-02-03 10:11:12
+select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime) as exp;
+exp
+2001-02-03 10:11:12
+select cast(20010203101112.121314 as datetime) as exp;
+exp
+2001-02-03 10:11:12
+select cast(110203101112.121314 as datetime) as exp;
+exp
+2011-02-03 10:11:12
+select cast(cast(010203101112.12 as double) as datetime) as exp;
+exp
+2001-02-03 10:11:12
+select cast("2011-02-03 10:11:12.123456" as datetime) as exp;
+exp
+2011-02-03 10:11:12
+select cast("2011-02-03 10:11:12.123456" as datetime(0)) as exp;
+exp
+2011-02-03 10:11:12
+select cast("2011-02-03 10:11:12.123456" as datetime(5)) as exp;
+exp
+2011-02-03 10:11:12.12345
+select cast("2011-02-03 10:11:12.123456" as datetime(6)) as exp;
+exp
+2011-02-03 10:11:12.123456
+select cast("2011-02-03 10:11:12" as datetime(6)) as exp;
+exp
+2011-02-03 10:11:12.000000
+select cast(cast(20010203101112.5 as double) as datetime(1)) as exp;
+exp
+2001-02-03 10:11:12.5
+select cast(cast(010203101112.12 as double) as datetime(2)) as exp;
+exp
+2001-02-03 10:11:12.12
+select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6)) as exp;
+exp
+2001-02-03 10:11:12.121314
+select cast(20010203101112.121314 as datetime(6)) as exp;
+exp
+2001-02-03 10:11:12.121314
+select cast(110203101112.121314 as datetime(6)) as exp;
+exp
+2011-02-03 10:11:12.121314
+select cast(cast(010203101112.12 as double) as datetime(6)) as exp;
+exp
+2001-02-03 10:11:12.120000
+select cast("2011-02-03 10:11:12.123456" as time) as exp;
+exp
+10:11:12
+select cast("2011-02-03 10:11:12.123456" as time(6)) as exp;
+exp
+10:11:12.123456
+select cast("10:11:12.123456" as time) as exp;
+exp
+10:11:12
+select cast("10:11:12.123456" as time(0)) as exp;
+exp
+10:11:12
+select cast("10:11:12.123456" as time(5)) as exp;
+exp
+10:11:12.12345
+select cast("10:11:12.123456" as time(6)) as exp;
+exp
+10:11:12.123456
+select cast("10:11:12" as time(6)) as exp;
+exp
+10:11:12.000000
+select cast(cast("2011-04-05 8:46:06.123456" AS datetime) as time) as exp;
+exp
+08:46:06
+select cast(cast("2011-04-05 8:46:06.123456" AS datetime) as time(6)) as exp;
+exp
+08:46:06.000000
+select cast(cast("2011-04-05 8:46:06.123456" AS datetime(6)) as time) as exp;
+exp
+08:46:06
+select cast(cast("2011-04-05 8:46:06.123456" AS datetime(6)) as time(6)) as exp;
+exp
+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:
+Note 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:
+Note 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 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 specified for '1'. Maximum is 65
+select cast(1 as decimal(66,6));
+ERROR 42000: Too big precision specified for '1'. Maximum is 65
+select cast(1 as decimal(64,63));
+ERROR 42000: Too big scale specified for '1'. Maximum is 38
+select cast(1 as double(64,63));
+ERROR 42000: Too big scale 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'\xD4\xC5\xD3\xD4' 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 COLLATE cp1251_general_ci DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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 utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
+ `c2` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
+ `c3` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
+ `c4` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
+ `c5` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+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
+Warnings:
+Note 1916 Got overflow when converting '1e300' to SIGNED BIGINT. Value truncated
+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 COLLATE=latin1_swedish_ci
+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:
+Note 1916 Got overflow when converting '-1e30' to SIGNED BIGINT. Value truncated
+Note 1916 Got overflow when converting '1e30' to SIGNED BIGINT. 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 @save_max_allowed_packet=@@global.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=@save_max_allowed_packet;
+#
+# 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: '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999...'
+Warning 1292 Truncated incorrect INTEGER value: '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999...'
+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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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))
+utf8mb3_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))
+utf8mb3_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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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 'utf8mb3' 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 'utf8mb3' 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;
+#
+# MDEV-10307 CAST(11068046444225730969 AS SIGNED) does not return a warning
+#
+SELECT CAST(11068046444225730969 AS SIGNED);
+CAST(11068046444225730969 AS SIGNED)
+-7378697629483820647
+#
+# MDEV-8919 Wrong result for CAST(9999999999999999999.0)
+#
+SET sql_mode='';
+SELECT CAST(9999999999999999999e0 AS UNSIGNED);
+CAST(9999999999999999999e0 AS UNSIGNED)
+10000000000000000000
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (9999999999999999999e0);
+SELECT * FROM t1;
+a
+10000000000000000000
+DROP TABLE t1;
+SELECT CAST(9999999999999999999.0 AS UNSIGNED);
+CAST(9999999999999999999.0 AS UNSIGNED)
+9999999999999999999
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (9999999999999999999.0);
+SELECT * FROM t1;
+a
+9999999999999999999
+DROP TABLE t1;
+SELECT CAST(-1.0 AS UNSIGNED);
+CAST(-1.0 AS UNSIGNED)
+0
+Warnings:
+Warning 1916 Got overflow when converting '-1.0' to UNSIGNED INT. Value truncated
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-1.0);
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
+SELECT CAST(-1e0 AS UNSIGNED);
+CAST(-1e0 AS UNSIGNED)
+0
+Warnings:
+Note 1916 Got overflow when converting '-1' to UNSIGNED BIGINT. Value truncated
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-1e0);
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
+SELECT CAST(-1e308 AS UNSIGNED);
+CAST(-1e308 AS UNSIGNED)
+0
+Warnings:
+Note 1916 Got overflow when converting '-1e308' to UNSIGNED BIGINT. Value truncated
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-1e308);
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
+SELECT CAST(TIME'-00:00:01.123' AS UNSIGNED);
+CAST(TIME'-00:00:01.123' AS UNSIGNED)
+0
+Warnings:
+Note 1916 Got overflow when converting '-00:00:01.123000' to UNSIGNED BIGINT. Value truncated
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (TIME'-00:00:01.123');
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
+CREATE TABLE t1 (a DOUBLE UNSIGNED);
+INSERT INTO t1 VALUES (1.9e19);
+SELECT CAST(a AS SIGNED), CAST(MIN(a) AS SIGNED) FROM t1;
+CAST(a AS SIGNED) CAST(MIN(a) AS SIGNED)
+9223372036854775807 9223372036854775807
+Warnings:
+Note 1916 Got overflow when converting '1.9e19' to SIGNED BIGINT. Value truncated
+Note 1916 Got overflow when converting '1.9e19' to SIGNED BIGINT. Value truncated
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(30,1) UNSIGNED);
+INSERT INTO t1 VALUES (1e19);
+SELECT a, CAST(a AS SIGNED) FROM t1;
+a CAST(a AS SIGNED)
+10000000000000000000.0 9223372036854775807
+Warnings:
+Warning 1916 Got overflow when converting '10000000000000000000.0' to INT. Value truncated
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-23 12:45:41 +0000