diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/bigint.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/cast.result | 197 | ||||
| -rw-r--r-- | mysql-test/r/dyncol.result | 1187 | ||||
| -rw-r--r-- | mysql-test/r/func_math.result | 30 | ||||
| -rw-r--r-- | mysql-test/r/func_str.result | 250 | ||||
| -rw-r--r-- | mysql-test/r/func_time.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/sp-vars.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/strict.result | 80 | ||||
| -rw-r--r-- | mysql-test/r/type_newdecimal.result | 18 | ||||
| -rw-r--r-- | mysql-test/r/warnings.result | 2 |
10 files changed, 1588 insertions, 184 deletions
diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 8f9b75fc664..38c417b4628 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -362,12 +362,12 @@ select cast(19999999999999999999 as signed); cast(19999999999999999999 as signed) 9223372036854775807 Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '19999999999999999999' to INT. Value truncated. select cast(-19999999999999999999 as signed); cast(-19999999999999999999 as signed) -9223372036854775808 Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-19999999999999999999' to INT. Value truncated. select -9223372036854775808; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def -9223372036854775808 8 20 20 N 32897 0 63 diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index dd61396e485..094ccce76c2 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -1,9 +1,13 @@ select CAST(1-2 AS UNSIGNED); CAST(1-2 AS UNSIGNED) 18446744073709551615 +Warnings: +Warning 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: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select CAST('10 ' as unsigned integer); CAST('10 ' as unsigned integer) 10 @@ -12,9 +16,15 @@ 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: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement +Warning 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: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement +Warning 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 @@ -29,6 +39,51 @@ cast(5 as unsigned) -6.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) +0000-00-00 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.2343400000 +select cast(cast(20010203101112.121314 as double) as datetime); +cast(cast(20010203101112.121314 as double) as datetime) +2001-02-03 10:11:12.125000 +select cast(cast(010203101112.12 as double) as datetime); +cast(cast(010203101112.12 as double) as datetime) +2001-02-03 10:11:12.120000 +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.121314 +select cast(20010203101112.121314 as datetime); +cast(20010203101112.121314 as datetime) +2001-02-03 10:11:12.121314 +select cast(110203101112.121314 as datetime); +cast(110203101112.121314 as datetime) +2011-02-03 10:11:12.121314 +select cast(cast(010203101112.12 as double) as datetime); +cast(cast(010203101112.12 as double) as datetime) +2001-02-03 10:11:12.120000 +select cast(cast("2011-04-05 8:46:06.23434" AS datetime) as time); +cast(cast("2011-04-05 8:46:06.23434" AS datetime) as time) +08:46:06.234340 select cast(NULL as unsigned), cast(1/0 as unsigned); cast(NULL as unsigned) cast(1/0 as unsigned) NULL NULL @@ -111,6 +166,115 @@ select 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: +Error 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: +Error 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) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '10203101112.121314' +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 @@ -146,6 +310,18 @@ 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 column '1'. Maximum is 65. +select cast(1 as decimal(66,6)); +ERROR 42000: Too big precision 66 specified for column '1'. Maximum is 65. +select cast(1 as decimal(64,63)); +ERROR 42000: Too big scale 63 specified for column '1'. Maximum is 30. +select cast(1 as double(64,63)); +ERROR 42000: Too big scale 63 specified for column '1'. Maximum is 30. set names binary; select cast(_latin1'test' as char character set latin2); cast(_latin1'test' as char character set latin2) @@ -255,12 +431,6 @@ cast("2001-1-1" as datetime) = "2001-01-01 00:00:00" select cast("1:2:3" as TIME) = "1:02:03"; cast("1:2:3" as TIME) = "1:02:03" 0 -select cast(NULL as DATE); -cast(NULL as DATE) -NULL -select cast(NULL as BINARY); -cast(NULL as BINARY) -NULL 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) ; @@ -337,6 +507,21 @@ Warning 1105 Cast to signed converted positive out-of-range integer to it's nega 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 ; diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result new file mode 100644 index 00000000000..f58340841c2 --- /dev/null +++ b/mysql-test/r/dyncol.result @@ -0,0 +1,1187 @@ +# +# column create +# +select hex(COLUMN_CREATE(1, NULL AS char character set utf8)); +hex(COLUMN_CREATE(1, NULL AS char character set utf8)) +000000 +select hex(COLUMN_CREATE(1, "afaf" AS char character set utf8)); +hex(COLUMN_CREATE(1, "afaf" AS char character set utf8)) +0001000100030861666166 +select hex(COLUMN_CREATE(1, 1212 AS char character set utf8)); +hex(COLUMN_CREATE(1, 1212 AS char character set utf8)) +0001000100033F31323132 +select hex(COLUMN_CREATE(1, 12.12 AS char character set utf8)); +hex(COLUMN_CREATE(1, 12.12 AS char character set utf8)) +0001000100033F31322E3132 +select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS char character set utf8)); +hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS char character set utf8)) +0001000100033F3939393939393939393939393939393939393939393939393939393939 +select hex(COLUMN_CREATE(1, NULL AS unsigned int)); +hex(COLUMN_CREATE(1, NULL AS unsigned int)) +000000 +select hex(COLUMN_CREATE(1, 1212 AS unsigned int)); +hex(COLUMN_CREATE(1, 1212 AS unsigned int)) +000100010001BC04 +select hex(COLUMN_CREATE(1, 7 AS unsigned int)); +hex(COLUMN_CREATE(1, 7 AS unsigned int)) +00010001000107 +select hex(COLUMN_CREATE(1, 8 AS unsigned int)); +hex(COLUMN_CREATE(1, 8 AS unsigned int)) +00010001000108 +select hex(COLUMN_CREATE(1, 127 AS unsigned int)); +hex(COLUMN_CREATE(1, 127 AS unsigned int)) +0001000100017F +select hex(COLUMN_CREATE(1, 128 AS unsigned int)); +hex(COLUMN_CREATE(1, 128 AS unsigned int)) +00010001000180 +select hex(COLUMN_CREATE(1, 12.12 AS unsigned int)); +hex(COLUMN_CREATE(1, 12.12 AS unsigned int)) +0001000100010C +select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS unsigned int)); +hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS unsigned int)) +000100010001FFFFFFFFFFFFFF7F +Warnings: +Error 1655 Got overflow when converting '99999999999999999999999999999' to INT. Value truncated. +select hex(COLUMN_CREATE(1, NULL AS int)); +hex(COLUMN_CREATE(1, NULL AS int)) +000000 +select hex(COLUMN_CREATE(1, 1212 AS int)); +hex(COLUMN_CREATE(1, 1212 AS int)) +0001000100007809 +select hex(COLUMN_CREATE(1, 7 AS int)); +hex(COLUMN_CREATE(1, 7 AS int)) +0001000100000E +select hex(COLUMN_CREATE(1, 8 AS int)); +hex(COLUMN_CREATE(1, 8 AS int)) +00010001000010 +select hex(COLUMN_CREATE(1, 127 AS int)); +hex(COLUMN_CREATE(1, 127 AS int)) +000100010000FE +select hex(COLUMN_CREATE(1, 128 AS int)); +hex(COLUMN_CREATE(1, 128 AS int)) +0001000100000001 +select hex(COLUMN_CREATE(1, 12.12 AS int)); +hex(COLUMN_CREATE(1, 12.12 AS int)) +00010001000018 +select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS int)); +hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS int)) +000100010000FEFFFFFFFFFFFFFF +Warnings: +Error 1655 Got overflow when converting '99999999999999999999999999999' to INT. Value truncated. +select hex(COLUMN_CREATE(1, NULL AS double)); +hex(COLUMN_CREATE(1, NULL AS double)) +000000 +select hex(COLUMN_CREATE(1, 1212 AS double)); +hex(COLUMN_CREATE(1, 1212 AS double)) +0001000100020000000000F09240 +select hex(COLUMN_CREATE(1, 12.12 AS double)); +hex(COLUMN_CREATE(1, 12.12 AS double)) +0001000100023D0AD7A3703D2840 +select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS double)); +hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS double)) +00010001000221D7E6FAE031F445 +select hex(COLUMN_CREATE(1, NULL AS decimal)); +hex(COLUMN_CREATE(1, NULL AS decimal)) +000000 +select hex(COLUMN_CREATE(1, 1212 AS decimal)); +hex(COLUMN_CREATE(1, 1212 AS decimal)) +0001000100040900800004BC +select hex(COLUMN_CREATE(1, 7 AS decimal)); +hex(COLUMN_CREATE(1, 7 AS decimal)) +000100010004090080000007 +select hex(COLUMN_CREATE(1, 8 AS decimal)); +hex(COLUMN_CREATE(1, 8 AS decimal)) +000100010004090080000008 +select hex(COLUMN_CREATE(1, 127 AS decimal)); +hex(COLUMN_CREATE(1, 127 AS decimal)) +00010001000409008000007F +select hex(COLUMN_CREATE(1, 128 AS decimal)); +hex(COLUMN_CREATE(1, 128 AS decimal)) +000100010004090080000080 +select hex(COLUMN_CREATE(1, 12.12 AS decimal)); +hex(COLUMN_CREATE(1, 12.12 AS decimal)) +00010001000402028C0C +select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS decimal)); +hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS decimal)) +0001000100041D00E33B9AC9FF3B9AC9FF3B9AC9FF +select hex(COLUMN_CREATE(1, NULL AS date)); +hex(COLUMN_CREATE(1, NULL AS date)) +000000 +select hex(COLUMN_CREATE(1, "2011-04-05" AS date)); +hex(COLUMN_CREATE(1, "2011-04-05" AS date)) +00010001000685B60F +select hex(COLUMN_CREATE(1, NULL AS time)); +hex(COLUMN_CREATE(1, NULL AS time)) +000000 +select hex(COLUMN_CREATE(1, "0:45:49.000001" AS time)); +hex(COLUMN_CREATE(1, "0:45:49.000001" AS time)) +000100010007010010B70000 +select hex(COLUMN_CREATE(1, NULL AS datetime)); +hex(COLUMN_CREATE(1, NULL AS datetime)) +000000 +select hex(COLUMN_CREATE(1, "2011-04-05 0:45:49.000001" AS datetime)); +hex(COLUMN_CREATE(1, "2011-04-05 0:45:49.000001" AS datetime)) +00010001000585B60F010010B70000 +select hex(COLUMN_CREATE(1, "afaf" AS char character set utf8, +2, 1212 AS unsigned int, +3, 1212 AS int, +4, 12.12 AS double, +4+1, 12.12 AS decimal, +6, "2011-04-05" AS date, +7, "- 0:45:49.000001" AS time, +8, "2011-04-05 0:45:49.000001" AS datetime)); +hex(COLUMN_CREATE(1, "afaf" AS char character set utf8, +2, 1212 AS unsigned int, +3, 1212 AS int, +4, 12.12 AS double, +4+1, 12.12 AS decimal, +6, "2011-04-05" AS date, +7, "- 0:45:49.000001" AS time, +8, "2011-04-05 0:45:49.000001" AS datetime)) +01080001000300020029000300380004004A0005008C000600AE000700C7000800F5000861666166BC0478093D0AD7A3703D284002028C0C85B60F010010B7000485B60F010010B70000 +explain extended +select hex(COLUMN_CREATE(1, "afaf" AS char character set utf8, +2, 1212 AS unsigned int, +3, 1212 AS int, +4, 12.12 AS double, +4+1, 12.12 AS decimal, +6, "2011-04-05" AS date, +7, "- 0:45:49.000001" AS time, +8, "2011-04-05 0:45:49.000001" AS datetime)); +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 hex(column_create(1,'afaf' AS char charset utf8 ,2,1212 AS unsigned int,3,1212 AS int,4,12.12 AS double,(4 + 1),12.12 AS decimal,6,'2011-04-05' AS date,7,'- 0:45:49.000001' AS time,8,'2011-04-05 0:45:49.000001' AS datetime)) AS `hex(COLUMN_CREATE(1, "afaf" AS char character set utf8, +2, 1212 AS unsigned int, +3, 1212 AS int, +4, 12.12 AS double, +4+1, 12.12 AS decimal, +6, "2011-04-05" AS date, +7, "- 0:45:49.000001" AS time, +8, "2011-04-05 0:45:49.000001" AS datetime))` +# +# column get uint +# +select column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned int); +column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned int) +1212 +explain extended +select column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned int); +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(column_get(column_create(1,1212 AS unsigned int),1) as unsigned) AS `column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned int)` +explain extended +select column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned); +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(column_get(column_create(1,1212 AS unsigned int),1) as unsigned) AS `column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned)` +select column_get(column_create(1, 1212 AS decimal), 1 as unsigned int); +column_get(column_create(1, 1212 AS decimal), 1 as unsigned int) +1212 +select column_get(column_create(1, 1212 AS double), 1 as unsigned int); +column_get(column_create(1, 1212 AS double), 1 as unsigned int) +1212 +select column_get(column_create(1, 1212 AS int), 1 as unsigned int); +column_get(column_create(1, 1212 AS int), 1 as unsigned int) +1212 +select column_get(column_create(1, "1212" AS char), 1 as unsigned int); +column_get(column_create(1, "1212" AS char), 1 as unsigned int) +1212 +select column_get(column_create(1, "2011-04-05" AS date), 1 as unsigned int); +column_get(column_create(1, "2011-04-05" AS date), 1 as unsigned int) +20110405 +select column_get(column_create(1, "8:46:06.23434" AS time), 1 as unsigned int); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as unsigned int) +84606 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as unsigned int); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as unsigned int) +20110405084606 +select column_get(column_create(1, NULL AS unsigned int), 1 as unsigned int); +column_get(column_create(1, NULL AS unsigned int), 1 as unsigned int) +NULL +# column geint truncation & warnings +select column_get(column_create(1, -1212 AS int), 1 as unsigned int); +column_get(column_create(1, -1212 AS int), 1 as unsigned int) +18446744073709550404 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement +select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as unsigned int); +column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as unsigned int) +18446744073709551615 +Warnings: +Error 1655 Got overflow when converting '99999999999999999999999999999' to UNSIGNED INT. Value truncated. +select column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as unsigned int); +column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as unsigned int) +1000 +select column_get(column_create(1, -1 AS decimal), 1 as unsigned int); +column_get(column_create(1, -1 AS decimal), 1 as unsigned int) +0 +Warnings: +Error 1655 Got overflow when converting '-1' to UNSIGNED INT. Value truncated. +select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as unsigned int); +column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as unsigned int) +18446744073709551615 +Warnings: +Warning 1655 Got overflow when converting '1e+29' to UNSIGNED INT. Value truncated. +select column_get(column_create(1, 999.9 AS double), 1 as unsigned int); +column_get(column_create(1, 999.9 AS double), 1 as unsigned int) +1000 +select column_get(column_create(1, -1 AS double), 1 as unsigned int); +column_get(column_create(1, -1 AS double), 1 as unsigned int) +0 +Warnings: +Warning 1655 Got overflow when converting '-1' to UNSIGNED INT. Value truncated. +select column_get(column_create(1, "1212III" AS char), 1 as unsigned int); +column_get(column_create(1, "1212III" AS char), 1 as unsigned int) +1212 +Warnings: +Warning 1657 Encountered illegal value '1212III' when converting to UNSIGNED INT +# +# column get int +# +select column_get(column_create(1, 1212 AS int), 1 as int); +column_get(column_create(1, 1212 AS int), 1 as int) +1212 +explain extended +select column_get(column_create(1, 1212 AS int), 1 as int); +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(column_get(column_create(1,1212 AS int),1) as signed) AS `column_get(column_create(1, 1212 AS int), 1 as int)` +explain extended +select column_get(column_create(1, 1212 AS int), 1 as signed int); +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(column_get(column_create(1,1212 AS int),1) as signed) AS `column_get(column_create(1, 1212 AS int), 1 as signed int)` +select column_get(column_create(1, -1212 AS int), 1 as int); +column_get(column_create(1, -1212 AS int), 1 as int) +-1212 +select column_get(column_create(1, 1212 AS decimal), 1 as int); +column_get(column_create(1, 1212 AS decimal), 1 as int) +1212 +select column_get(column_create(1, 1212 AS double), 1 as int); +column_get(column_create(1, 1212 AS double), 1 as int) +1212 +select column_get(column_create(1, 1212 AS unsigned int), 1 as int); +column_get(column_create(1, 1212 AS unsigned int), 1 as int) +1212 +select column_get(column_create(1, "1212" AS char), 1 as int); +column_get(column_create(1, "1212" AS char), 1 as int) +1212 +select column_get(column_create(1, "-1212" AS char), 1 as int); +column_get(column_create(1, "-1212" AS char), 1 as int) +-1212 +select column_get(column_create(1, "2011-04-05" AS date), 1 as int); +column_get(column_create(1, "2011-04-05" AS date), 1 as int) +20110405 +select column_get(column_create(1, "8:46:06.23434" AS time), 1 as int); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as int) +84606 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as int); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as int) +20110405084606 +select column_get(column_create(1, NULL AS int), 1 as int); +column_get(column_create(1, NULL AS int), 1 as int) +NULL +#column gett truncation & warnings +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as int); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as int) +-1 +Warnings: +Warning 1105 Cast to signed converted positive out-of-range integer to it's negative complement +select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as int); +column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as int) +9223372036854775807 +Warnings: +Error 1655 Got overflow when converting '99999999999999999999999999999' to INT. Value truncated. +select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as int); +column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as int) +-9223372036854775808 +Warnings: +Error 1655 Got overflow when converting '-99999999999999999999999999999' to INT. Value truncated. +select column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as int); +column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as int) +1000 +select column_get(column_create(1, 999.9 AS double), 1 as int); +column_get(column_create(1, 999.9 AS double), 1 as int) +1000 +select column_get(column_create(1, -99999999999999999999999999999 AS double), 1 as int); +column_get(column_create(1, -99999999999999999999999999999 AS double), 1 as int) +-9223372036854775808 +Warnings: +Warning 1655 Got overflow when converting '-1e+29' to INT. Value truncated. +select column_get(column_create(1, "-1212III" AS char), 1 as int); +column_get(column_create(1, "-1212III" AS char), 1 as int) +-1212 +Warnings: +Warning 1657 Encountered illegal value '-1212III' when converting to INT +select column_get(column_create(1, "1212III" AS char), 1 as int); +column_get(column_create(1, "1212III" AS char), 1 as int) +1212 +Warnings: +Warning 1657 Encountered illegal value '1212III' when converting to INT +# +#column get char +# +select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8); +column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8) +1212 +explain extended +select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8); +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(column_get(column_create(1,'1212' AS char charset utf8 ),1) as char charset utf8) AS `column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8)` +select column_get(column_create(1, 1212 AS unsigned int), 1 as char charset utf8); +column_get(column_create(1, 1212 AS unsigned int), 1 as char charset utf8) +1212 +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as char charset utf8); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as char charset utf8) +18446744073709551615 +select column_get(column_create(1, 1212 AS int), 1 as char charset utf8); +column_get(column_create(1, 1212 AS int), 1 as char charset utf8) +1212 +select column_get(column_create(1, -1212 AS int), 1 as char charset utf8); +column_get(column_create(1, -1212 AS int), 1 as char charset utf8) +-1212 +select column_get(column_create(1, 9223372036854775807 AS int), 1 as char charset utf8); +column_get(column_create(1, 9223372036854775807 AS int), 1 as char charset utf8) +9223372036854775807 +select column_get(column_create(1, -9223372036854775808 AS int), 1 as char charset utf8); +column_get(column_create(1, -9223372036854775808 AS int), 1 as char charset utf8) +-9223372036854775808 +select column_get(column_create(1, 1212.12 AS decimal), 1 as char charset utf8); +column_get(column_create(1, 1212.12 AS decimal), 1 as char charset utf8) +1212.12 +select column_get(column_create(1, 1212.12 AS double), 1 as char charset utf8); +column_get(column_create(1, 1212.12 AS double), 1 as char charset utf8) +1212.12 +select column_get(column_create(1, "2011-04-05" AS date), 1 as char charset utf8); +column_get(column_create(1, "2011-04-05" AS date), 1 as char charset utf8) +2011-04-05 +select column_get(column_create(1, "8:46:06.23434" AS time), 1 as char charset utf8); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as char charset utf8) +08:46:06.234340 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as char charset utf8); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as char charset utf8) +2011-04-05 08:46:06.234340 +select column_get(column_create(1, NULL AS char charset utf8), 1 as char charset utf8); +column_get(column_create(1, NULL AS char charset utf8), 1 as char charset utf8) +NULL +select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary); +column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary) +1212 +explain extended +select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary); +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(column_get(column_create(1,'1212' AS char charset utf8 ),1) as char charset binary) AS `column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary)` +# +# column get real +# +select column_get(column_create(1, 1212.12 AS double), 1 as double); +column_get(column_create(1, 1212.12 AS double), 1 as double) +1212.12 +explain extended +select column_get(column_create(1, 1212.12 AS double), 1 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(column_get(column_create(1,1212.12 AS double),1) as double) AS `column_get(column_create(1, 1212.12 AS double), 1 as double)` +explain extended +select column_get(column_create(1, 1212.12 AS double), 1 as double(6,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(column_get(column_create(1,1212.12 AS double),1) as double(6,2)) AS `column_get(column_create(1, 1212.12 AS double), 1 as double(6,2))` +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as double); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as double) +1.84467440737096e+19 +select column_get(column_create(1, 9223372036854775807 AS int), 1 as double); +column_get(column_create(1, 9223372036854775807 AS int), 1 as double) +9.22337203685478e+18 +select column_get(column_create(1, -9223372036854775808 AS int), 1 as double); +column_get(column_create(1, -9223372036854775808 AS int), 1 as double) +-9.22337203685478e+18 +select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as double); +column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as double) +1e+29 +select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as double); +column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as double) +-1e+29 +select column_get(column_create(1, "2011-04-05" AS date), 1 as double); +column_get(column_create(1, "2011-04-05" AS date), 1 as double) +20110405 +select column_get(column_create(1, "8:46:06.23434" AS time), 1 as double); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as double) +84606.23434 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double) +20110405084606.2 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double(20,6)); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double(20,6)) +20110405084606.234375 +select column_get(column_create(1, NULL AS double), 1 as double); +column_get(column_create(1, NULL AS double), 1 as double) +NULL +# column get real truncation & warnings +select column_get(column_create(1, "1223.5aa" AS char), 1 as double); +column_get(column_create(1, "1223.5aa" AS char), 1 as double) +1223.5 +Warnings: +Warning 1657 Encountered illegal value '1223.5aa' when converting to DOUBLE +select column_get(column_create(1, "aa" AS char), 1 as double); +column_get(column_create(1, "aa" AS char), 1 as double) +0 +Warnings: +Warning 1657 Encountered illegal value 'aa' when converting to DOUBLE +select column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2)); +column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2)) +999.99 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2))' at row 1 +select column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2)); +column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2)) +9.99 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2))' at row 1 +# +# column get decimal +# +select column_get(column_create(1, 1212.12 AS double), 1 as decimal); +column_get(column_create(1, 1212.12 AS double), 1 as decimal) +1212 +select column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2)); +column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2)) +1212.12 +explain extended +select column_get(column_create(1, 1212.12 AS double), 1 as decimal); +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(column_get(column_create(1,1212.12 AS double),1) as decimal(10,0)) AS `column_get(column_create(1, 1212.12 AS double), 1 as decimal)` +explain extended +select column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,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(column_get(column_create(1,1212.12 AS double),1) as decimal(6,2)) AS `column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2))` +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal(20,0)); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal(20,0)) +18446744073709551615 +select column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal(32,0)); +column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal(32,0)) +9223372036854775807 +select column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal(32,0)); +column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal(32,0)) +-9223372036854775808 +select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as decimal(40,10)); +column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as decimal(40,10)) +-99999999999999999999999999999.0000000000 +select column_get(column_create(1, "2011-04-05" AS date), 1 as decimal(32,6)); +column_get(column_create(1, "2011-04-05" AS date), 1 as decimal(32,6)) +20110405.000000 +select column_get(column_create(1, "8:46:06.23434" AS time), 1 as decimal(32,6)); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as decimal(32,6)) +84606.234340 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal(32,6)); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal(32,6)) +20110405084606.230000 +select column_get(column_create(1, NULL as decimal), 1 as decimal(32,10)); +column_get(column_create(1, NULL as decimal), 1 as decimal(32,10)) +NULL +select column_get(column_create(1, "1223.5555" as decimal(10,5)), 1 as decimal(6,2)); +column_get(column_create(1, "1223.5555" as decimal(10,5)), 1 as decimal(6,2)) +1223.56 +# column get decimal truncation & warnings +select column_get(column_create(1, "1223.5aa" AS char), 1 as decimal(32,10)); +column_get(column_create(1, "1223.5aa" AS char), 1 as decimal(32,10)) +1223.5000000000 +Warnings: +Warning 1657 Encountered illegal value '1223.5aa' when converting to DECIMAL +select column_get(column_create(1, "aa" AS char), 1 as decimal(32,10)); +column_get(column_create(1, "aa" AS char), 1 as decimal(32,10)) +0.0000000000 +Warnings: +Warning 1657 Encountered illegal value 'aa' when converting to DECIMAL +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal) +9999999999 +Warnings: +Error 1264 Out of range value for column 'column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal)' at row 1 +select column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal); +column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal) +9999999999 +Warnings: +Error 1264 Out of range value for column 'column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal)' at row 1 +select column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal); +column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal) +-9999999999 +Warnings: +Error 1264 Out of range value for column 'column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal)' at row 1 +select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as decimal); +column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as decimal) +9999999999 +Warnings: +Error 1264 Out of range value for column 'column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as decimal)' at row 1 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal) +9999999999 +Warnings: +Error 1264 Out of range value for column 'column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal)' at row 1 +select column_get(column_create(1, "1223.5555" as double), 1 as decimal(5,2)); +column_get(column_create(1, "1223.5555" as double), 1 as decimal(5,2)) +999.99 +Warnings: +Error 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" as double), 1 as decimal(5,2))' at row 1 +select column_get(column_create(1, "-1223.5555" as double), 1 as decimal(5,2)); +column_get(column_create(1, "-1223.5555" as double), 1 as decimal(5,2)) +-999.99 +Warnings: +Error 1264 Out of range value for column 'column_get(column_create(1, "-1223.5555" as double), 1 as decimal(5,2))' at row 1 +select column_get(column_create(1, "1223.5555" AS double), 1 as decimal(3,2)); +column_get(column_create(1, "1223.5555" AS double), 1 as decimal(3,2)) +9.99 +Warnings: +Error 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as decimal(3,2))' at row 1 +select column_get(column_create(1, "1223.5555" AS decimal(10,5)), 1 as decimal(3,2)); +column_get(column_create(1, "1223.5555" AS decimal(10,5)), 1 as decimal(3,2)) +9.99 +Warnings: +Error 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS decimal(10,5)), 1 as decimal(3,2))' at row 1 +# +# column get datetime +# +select column_get(column_create(1, 20010203101112.121314 as double), 1 as datetime); +column_get(column_create(1, 20010203101112.121314 as double), 1 as datetime) +2001-02-03 10:11:12.125000 +select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as datetime); +column_get(column_create(1, 20010203101112.121314 as decimal), 1 as datetime) +2001-02-03 10:11:12.121314 +select column_get(column_create(1, 20010203101112 as unsigned int), 1 as datetime); +column_get(column_create(1, 20010203101112 as unsigned int), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, 20010203101112 as int), 1 as datetime); +column_get(column_create(1, 20010203101112 as int), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, "20010203101112" as char), 1 as datetime); +column_get(column_create(1, "20010203101112" as char), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as datetime); +column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as datetime); +column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as datetime) +2001-02-03 10:11:12.121314 +select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as datetime); +column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as datetime) +2001-02-03 10:11:12.121314 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime) +2011-04-05 08:46:06.234340 +select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as datetime); +column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as datetime) +2011-00-00 08:46:06.234340 +select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as datetime); +column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as datetime) +2011-00-01 08:46:06.234340 +select column_get(column_create(1, 20010203 as unsigned int), 1 as datetime); +column_get(column_create(1, 20010203 as unsigned int), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, 20010203 as int), 1 as datetime); +column_get(column_create(1, 20010203 as int), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, 20010203), 1 as datetime); +column_get(column_create(1, 20010203), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, 20010203.0), 1 as datetime); +column_get(column_create(1, 20010203.0), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, 20010203.0 as double), 1 as datetime); +column_get(column_create(1, 20010203.0 as double), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, "2001-02-03"), 1 as datetime); +column_get(column_create(1, "2001-02-03"), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, "20010203"), 1 as datetime); +column_get(column_create(1, "20010203"), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, 0), 1 as datetime); +column_get(column_create(1, 0), 1 as datetime) +0000-00-00 00:00:00 +select column_get(column_create(1, "2001021"), 1 as datetime); +column_get(column_create(1, "2001021"), 1 as datetime) +2020-01-02 01:00:00 +select column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime) +0000-00-00 08:46:06.234340 +set @@sql_mode="allow_invalid_dates"; +select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime); +column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime) +2011-02-30 18:46:06.234340 +select column_get(column_create(1, "0000-00-000" AS CHAR), 1 as datetime); +column_get(column_create(1, "0000-00-000" AS CHAR), 1 as datetime) +0000-00-00 00:00:00 +select column_get(column_create(1, "2001-00-02" AS CHAR), 1 as datetime); +column_get(column_create(1, "2001-00-02" AS CHAR), 1 as datetime) +2001-00-02 00:00:00 +set @@sql_mode=""; +# column get datetime truncation & warnings +select column_get(column_create(1, "1223.5aa" AS char), 1 as datetime); +column_get(column_create(1, "1223.5aa" AS char), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '1223.5aa' +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as datetime); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as datetime) +NULL +Warnings: +Warning 1657 Encountered illegal value '18446744073709551615' when converting to DATE or DATETIME +select column_get(column_create(1, 9223372036854775807 AS int), 1 as datetime); +column_get(column_create(1, 9223372036854775807 AS int), 1 as datetime) +NULL +Warnings: +Warning 1657 Encountered illegal value '9223372036854775807' when converting to DATE or DATETIME +select column_get(column_create(1, -9223372036854775808 AS int), 1 as datetime); +column_get(column_create(1, -9223372036854775808 AS int), 1 as datetime) +NULL +Warnings: +Warning 1657 Encountered illegal value '-9223372036854775808' when converting to DATE or DATETIME +select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as datetime); +column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '99999999999999999999999999999' +select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as datetime); +column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as datetime) +NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '9.99999999999999914332e+28 ' +select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as datetime); +column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2011-02-32 8:46:06.23434' +select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as datetime); +column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2011-13-01 8:46:06.23434' +select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as datetime); +column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2011-02-30 8:46:06.23434' +select column_get(column_create(1, "20010231"), 1 as datetime); +column_get(column_create(1, "20010231"), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '20010231' +select column_get(column_create(1, "0" AS CHAR), 1 as datetime); +column_get(column_create(1, "0" AS CHAR), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '0' +# +# column get date +# +select column_get(column_create(1, 20010203101112.121314 as double), 1 as date); +column_get(column_create(1, 20010203101112.121314 as double), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as date); +column_get(column_create(1, 20010203101112.121314 as decimal), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203101112 as unsigned int), 1 as date); +column_get(column_create(1, 20010203101112 as unsigned int), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203101112 as int), 1 as date); +column_get(column_create(1, 20010203101112 as int), 1 as date) +2001-02-03 +select column_get(column_create(1, "20010203101112" as char), 1 as date); +column_get(column_create(1, "20010203101112" as char), 1 as date) +2001-02-03 +select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as date); +column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as date) +2001-02-03 +select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as date); +column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as date) +2001-02-03 +select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as date); +column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as date) +2001-02-03 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as date); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as date) +2011-04-05 +select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as date); +column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as date) +2011-00-00 +select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as date); +column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as date) +2011-00-01 +select column_get(column_create(1, 20010203 as unsigned int), 1 as date); +column_get(column_create(1, 20010203 as unsigned int), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203 as int), 1 as date); +column_get(column_create(1, 20010203 as int), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203), 1 as date); +column_get(column_create(1, 20010203), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203.0), 1 as date); +column_get(column_create(1, 20010203.0), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203.0 as double), 1 as date); +column_get(column_create(1, 20010203.0 as double), 1 as date) +2001-02-03 +select column_get(column_create(1, "2001-02-03"), 1 as date); +column_get(column_create(1, "2001-02-03"), 1 as date) +2001-02-03 +select column_get(column_create(1, "20010203"), 1 as date); +column_get(column_create(1, "20010203"), 1 as date) +2001-02-03 +select column_get(column_create(1, 0), 1 as date); +column_get(column_create(1, 0), 1 as date) +0000-00-00 +select column_get(column_create(1, "2001021"), 1 as date); +column_get(column_create(1, "2001021"), 1 as date) +2020-01-02 +set @@sql_mode="allow_invalid_dates"; +select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as date); +column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as date) +2011-02-30 +select column_get(column_create(1, "0000-00-000" AS CHAR), 1 as date); +column_get(column_create(1, "0000-00-000" AS CHAR), 1 as date) +0000-00-00 +select column_get(column_create(1, "2001-00-02" AS CHAR), 1 as date); +column_get(column_create(1, "2001-00-02" AS CHAR), 1 as date) +2001-00-02 +set @@sql_mode=""; +# column get date truncation & warnings +select column_get(column_create(1, "1223.5aa" AS char), 1 as date); +column_get(column_create(1, "1223.5aa" AS char), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '1223.5aa' +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as date); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as date) +NULL +Warnings: +Warning 1657 Encountered illegal value '18446744073709551615' when converting to DATE or DATETIME +select column_get(column_create(1, 9223372036854775807 AS int), 1 as date); +column_get(column_create(1, 9223372036854775807 AS int), 1 as date) +NULL +Warnings: +Warning 1657 Encountered illegal value '9223372036854775807' when converting to DATE or DATETIME +select column_get(column_create(1, -9223372036854775808 AS int), 1 as date); +column_get(column_create(1, -9223372036854775808 AS int), 1 as date) +NULL +Warnings: +Warning 1657 Encountered illegal value '-9223372036854775808' when converting to DATE or DATETIME +select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as date); +column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '99999999999999999999999999999' +select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as date); +column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as date) +NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '9.99999999999999914332e+28 ' +select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as date); +column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2011-02-32 8:46:06.23434' +select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as date); +column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2011-13-01 8:46:06.23434' +select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as date); +column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2011-02-30 8:46:06.23434' +select column_get(column_create(1, "20010231"), 1 as date); +column_get(column_create(1, "20010231"), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '20010231' +select column_get(column_create(1, "0" AS CHAR), 1 as date); +column_get(column_create(1, "0" AS CHAR), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '0' +# +# column get time +# +select column_get(column_create(1, 20010203101112.121314 as double), 1 as time); +column_get(column_create(1, 20010203101112.121314 as double), 1 as time) +10:11:12.100000 +select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as time); +column_get(column_create(1, 20010203101112.121314 as decimal), 1 as time) +10:11:12.121314 +select column_get(column_create(1, 20010203101112 as unsigned int), 1 as time); +column_get(column_create(1, 20010203101112 as unsigned int), 1 as time) +10:11:12 +select column_get(column_create(1, 20010203101112 as int), 1 as time); +column_get(column_create(1, 20010203101112 as int), 1 as time) +10:11:12 +select column_get(column_create(1, "20010203101112" as char), 1 as time); +column_get(column_create(1, "20010203101112" as char), 1 as time) +10:11:12 +select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as time); +column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as time) +10:11:12 +select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time); +column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time) +10:11:12.121314 +select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as time); +column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as time) +10:11:12.121314 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as time); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as time) +08:46:06.234340 +select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as time); +column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as time) +08:46:06.234340 +select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as time); +column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as time) +08:46:06.234340 +select column_get(column_create(1, "830:46:06.23434" AS CHAR), 1 as time); +column_get(column_create(1, "830:46:06.23434" AS CHAR), 1 as time) +830:46:06.234340 +select cast("-830:46:06.23434" AS time); +cast("-830:46:06.23434" AS time) +-830:46:06.234340 +select 1,cast("-830:46:06.23434" AS time); +1 cast("-830:46:06.23434" AS time) +1 -830:46:06.234340 +select hex(column_create(1, "-830:46:06.23434" AS CHAR)); +hex(column_create(1, "-830:46:06.23434" AS CHAR)) +000100010003082D3833303A34363A30362E3233343334 +select column_get(column_create(1, "-830:46:06.23434" AS CHAR), 1 as time); +column_get(column_create(1, "-830:46:06.23434" AS CHAR), 1 as time) +-830:46:06.234340 +select column_get(column_create(1, "0" AS CHAR), 1 as time); +column_get(column_create(1, "0" AS CHAR), 1 as time) +00:00:00 +select column_get(column_create(1, "6" AS CHAR), 1 as time); +column_get(column_create(1, "6" AS CHAR), 1 as time) +00:00:06 +select column_get(column_create(1, "1:6" AS CHAR), 1 as time); +column_get(column_create(1, "1:6" AS CHAR), 1 as time) +01:06:00 +select column_get(column_create(1, "2:1:6" AS CHAR), 1 as time); +column_get(column_create(1, "2:1:6" AS CHAR), 1 as time) +02:01:06 +select column_get(column_create(1, 0), 1 as time); +column_get(column_create(1, 0), 1 as time) +00:00:00 +select column_get(column_create(1, "2001021"), 1 as time); +column_get(column_create(1, "2001021"), 1 as time) +200:10:21 +set @@sql_mode="allow_invalid_dates"; +select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as time); +column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as time) +18:46:06.234340 +set @@sql_mode=""; +# column get date truncation & warnings +select column_get(column_create(1, "1223.5aa" AS char), 1 as time); +column_get(column_create(1, "1223.5aa" AS char), 1 as time) +00:12:23.500000 +Warnings: +Warning 1292 Truncated incorrect time value: '1223.5aa' +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time) +NULL +Warnings: +Warning 1292 Truncated incorrect time value: '18446744073709551615' +select column_get(column_create(1, 9223372036854775807 AS int), 1 as time); +column_get(column_create(1, 9223372036854775807 AS int), 1 as time) +NULL +Warnings: +Warning 1292 Truncated incorrect time value: '9223372036854775807' +select column_get(column_create(1, -9223372036854775808 AS int), 1 as time); +column_get(column_create(1, -9223372036854775808 AS int), 1 as time) +NULL +Warnings: +Warning 1292 Truncated incorrect time value: '-9223372036854775808' +select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time); +column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time) +NULL +Warnings: +Warning 1292 Truncated incorrect time value: '99999999999999999999999999999' +select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time); +column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time) +NULL +Warnings: +Warning 1292 Truncated incorrect time value: '1e+29' +select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time); +column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time) +NULL +Warnings: +Warning 1292 Truncated incorrect time value: '2011-02-32 8:46:06.23434' +select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as time); +column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as time) +NULL +Warnings: +Warning 1292 Truncated incorrect time value: '2011-13-01 8:46:06.23434' +select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as time); +column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as time) +NULL +Warnings: +Warning 1292 Truncated incorrect time value: '2011-02-30 8:46:06.23434' +select column_get(column_create(1, "2001-02-03"), 1 as time); +column_get(column_create(1, "2001-02-03"), 1 as time) +00:20:01 +Warnings: +Warning 1292 Truncated incorrect time value: '2001-02-03' +select column_get(column_create(1, "20010203"), 1 as time); +column_get(column_create(1, "20010203"), 1 as time) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '20010203' +# column add +select hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer)); +hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer)) +00020001000002001078097809 +select hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer)); +hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer)) +0001000100007809 +select hex(column_add(column_create(1, 1212 as integer), 1, NULL as integer)); +hex(column_add(column_create(1, 1212 as integer), 1, NULL as integer)) +000000 +select hex(column_add(column_create(1, 1212 as integer), 2, NULL as integer)); +hex(column_add(column_create(1, 1212 as integer), 2, NULL as integer)) +0001000100007809 +select hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer)); +hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer)) +000200010000020008167809 +select column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 1 as integer); +column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 1 as integer) +11 +select column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 2 as integer); +column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 2 as integer) +1212 +select hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer, 2, 11 as integer)); +hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer, 2, 11 as integer)) +000200010000020010780916 +select hex(column_add(column_create(1, NULL as integer), 1, 1212 as integer, 2, 11 as integer)); +hex(column_add(column_create(1, NULL as integer), 1, 1212 as integer, 2, 11 as integer)) +000200010000020010780916 +select hex(column_add(column_create(1, 1212 as integer, 2, 1212 as integer), 1, 11 as integer)); +hex(column_add(column_create(1, 1212 as integer, 2, 1212 as integer), 1, 11 as integer)) +000200010000020008167809 +select hex(column_add(column_create(1, 1), 1, null)); +hex(column_add(column_create(1, 1), 1, null)) +000000 +select column_list(column_add(column_create(1, 1), 1, null)); +column_list(column_add(column_create(1, 1), 1, null)) + +select column_list(column_add(column_create(1, 1), 1, "")); +column_list(column_add(column_create(1, 1), 1, "")) +1 +select hex(column_add("", 1, 1)); +hex(column_add("", 1, 1)) +00010001000002 +# column delete +select hex(column_delete(column_create(1, 1212 as integer, 2, 1212 as integer), 1)); +hex(column_delete(column_create(1, 1212 as integer, 2, 1212 as integer), 1)) +0001000200007809 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2)) +0002000100000300080206 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 3)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 3)) +0002000100000200080204 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 4)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 4)) +000300010000020008030010020406 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 1)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 1)) +00010003000006 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 3)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 3)) +00010001000002 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3)) +000000 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3, 10)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3, 10)) +000000 +select hex(column_delete(column_create(1, 1), 1)); +hex(column_delete(column_create(1, 1), 1)) +000000 +select hex(column_delete("", 1)); +hex(column_delete("", 1)) + +# column exists +select column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 1); +column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 1) +1 +select column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 4); +column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 4) +0 +# column list +select column_list(column_create(1, 1212 as integer, 2, 1212 as integer)); +column_list(column_create(1, 1212 as integer, 2, 1212 as integer)) +1,2 +select column_list(column_create(1, 1212 as integer)); +column_list(column_create(1, 1212 as integer)) +1 +select column_list(column_create(1, NULL as integer)); +column_list(column_create(1, NULL as integer)) + +# +# check error handling +# +select HEX(COLUMN_CREATE(1, 5, 1, 5)); +ERROR 22007: Illegal value used as argument of dynamic column function +select HEX(COLUMN_CREATE("", 1, 5, 1, 5)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1 +select COLUMN_LIST("a"); +ERROR HY000: Encountered illegal format of dynamic column string +select column_delete("a", 1); +ERROR HY000: Encountered illegal format of dynamic column string +select hex(column_delete("", 1)); +hex(column_delete("", 1)) + +select hex(column_delete("", -1)); +ERROR 22007: Illegal value used as argument of dynamic column function +select hex(column_create(-1, 1)); +ERROR 22007: Illegal value used as argument of dynamic column function +select hex(column_create(65536, 1)); +ERROR 22007: Illegal value used as argument of dynamic column function +select hex(column_add("", -1, 1)); +ERROR 22007: Illegal value used as argument of dynamic column function +select hex(column_add("", 65536, 1)); +ERROR 22007: Illegal value used as argument of dynamic column function +select hex(column_get("", -1 as int)); +hex(column_get("", -1 as int)) +NULL +# +# Test with table +# +create table t1 (id int primary key, str mediumblob); +insert into t1 values (1, ''), (2, ''), (3, ''), (4, ''), (5, null); +select id, str, column_get(str, 1 as int) from t1; +id str column_get(str, 1 as int) +1 NULL +2 NULL +3 NULL +4 NULL +5 NULL NULL +update t1 set str=column_create(1, id, 2, "a") where id < 3; +update t1 set str=column_add(str, 1, id, 2, "b") where id >= 4; +select id, column_get(str, 1 as int), column_get(str, 2 as char) from t1 where column_exists(str,1) or column_exists(str,2); +id column_get(str, 1 as int) column_get(str, 2 as char) +1 1 a +2 2 a +4 4 b +update t1 set str=column_create(1, id, 10, "test") where id = 5; +insert into t1 values (6, column_create(10, "test2")); +update t1 set str=column_add(str, 2, 'c', 1, column_get(str, 1 as int) + 1, 3, 100) where id > 2; +select id, length(str), column_get(str, 1 as int), column_get(str, 2 as char), column_get(str, 3 as int) from t1; +id length(str) column_get(str, 1 as int) column_get(str, 2 as char) column_get(str, 3 as int) +1 12 1 a NULL +2 12 2 a NULL +3 12 NULL c 100 +4 16 5 c 100 +5 24 6 c 100 +6 21 NULL c 100 +select column_get(str, 2 as char), sum(column_get(str, 1 as int)) from t1 group by column_get(str, 2 as char); +column_get(str, 2 as char) sum(column_get(str, 1 as int)) +a 3 +c 11 +select column_get(str, 2 as char), sum(column_get(str, 1 as int)) from t1 where column_exists(str, 2) <> 0 group by 1; +column_get(str, 2 as char) sum(column_get(str, 1 as int)) +a 3 +c 11 +select sum(column_get(str, 1 as int)) from t1 group by column_get(str, 2 as char) order by sum(column_get(str, 1 as int)) desc; +sum(column_get(str, 1 as int)) +11 +3 +select sum(column_get(str, 1 as int)) from t1 group by column_get(str, 2 as char) having sum(column_get(str, 1 as int)) > 2; +sum(column_get(str, 1 as int)) +3 +11 +select sum(column_get(str, 1 as int)) from t1 where column_get(str, 3 as int) > 50 group by column_get(str, 2 as char); +sum(column_get(str, 1 as int)) +11 +select id, column_list(str) from t1 where id= 5; +id column_list(str) +5 1,2,3,10 +update t1 set str=column_delete(str, 3, 4, 2) where id= 5; +select id, length(str), column_list(str), column_get(str, 1 as int), column_get(str, 2 as char), column_get(str, 3 as int) from t1; +id length(str) column_list(str) column_get(str, 1 as int) column_get(str, 2 as char) column_get(str, 3 as int) +1 12 1,2 1 a NULL +2 12 1,2 2 a NULL +3 12 2,3 NULL c 100 +4 16 1,2,3 5 c 100 +5 15 1,10 6 NULL NULL +6 21 2,3,10 NULL c 100 +update t1 set str=column_add(str, 4, 45 as char, 2, 'c') where id= 5; +select id, length(str), column_list(str), column_get(str, 1 as int), column_get(str, 2 as char), column_get(str, 3 as int) from t1 where id = 5; +id length(str) column_list(str) column_get(str, 1 as int) column_get(str, 2 as char) column_get(str, 3 as int) +5 26 1,2,4,10 6 c NULL +select id, length(str), column_list(str), column_exists(str, 4) from t1; +id length(str) column_list(str) column_exists(str, 4) +1 12 1,2 0 +2 12 1,2 0 +3 12 2,3 0 +4 16 1,2,3 0 +5 26 1,2,4,10 1 +6 21 2,3,10 0 +select sum(column_get(str, 1 as int)), column_list(str) from t1 group by 2; +sum(column_get(str, 1 as int)) column_list(str) +3 1,2 +5 1,2,3 +6 1,2,4,10 +NULL 2,3 +NULL 2,3,10 +select id, hex(str) from t1; +id hex(str) +1 00020001000002000B020861 +2 00020001000002000B040861 +3 0002000200030300100863C8 +4 00030001000002000B0300180A0863C8 +5 00040001000002000B04001B0A00330C08633F34350874657374 +6 0003000200030300100A001B0863C8087465737432 +update t1 set str=column_add(str, 4, repeat("a", 100000)) where id=5; +select id from t1 where column_get(str,4 as char(100000)) = repeat("a", 100000); +id +5 +select id from t1 where column_get(str,4 as char(100)) = repeat("a", 100); +id +5 +Warnings: +Warning 1292 Truncated incorrect CHAR(100) value: 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' +update t1 set str=column_add(str, 4, repeat("b", 10000)) where id=5; +select id from t1 where column_get(str,4 as char(100000)) = repeat("b", 10000); +id +5 +update t1 set str=column_add(str, 4, repeat("c", 100)) where id=5; +select id from t1 where column_get(str,4 as char(100000)) = repeat("c", 100); +id +5 +update t1 set str=column_add(str, 4, repeat("d", 10000)) where id=5; +select id from t1 where column_get(str,4 as char(100000)) = repeat("d", 10000); +id +5 +update t1 set str=column_add(str, 4, repeat("e", 10), 5, repeat("f", 100000)) where id=5; +select id from t1 where column_get(str,5 as char(100000)) = repeat("f", 100000); +id +5 +select id, column_list(str), length(str) from t1 where id=5; +id column_list(str) length(str) +5 1,2,4,5,10 100048 +update t1 set str=column_delete(str, 5) where id=5; +select id, column_list(str), length(str) from t1 where id=5; +id column_list(str) length(str) +5 1,2,4,10 34 +drop table t1; diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 3a626084c9e..ea9e034e6c5 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -285,33 +285,55 @@ set names default; select cast(-2 as unsigned), 18446744073709551614, -2; cast(-2 as unsigned) 18446744073709551614 -2 18446744073709551614 18446744073709551614 -2 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select abs(cast(-2 as unsigned)), abs(18446744073709551614), abs(-2); abs(cast(-2 as unsigned)) abs(18446744073709551614) abs(-2) 18446744073709551614 18446744073709551614 2 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select ceiling(cast(-2 as unsigned)), ceiling(18446744073709551614), ceiling(-2); ceiling(cast(-2 as unsigned)) ceiling(18446744073709551614) ceiling(-2) 18446744073709551614 18446744073709551614 -2 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2); floor(cast(-2 as unsigned)) floor(18446744073709551614) floor(-2) 18446744073709551614 18446744073709551614 -2 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select format(cast(-2 as unsigned), 2), format(18446744073709551614, 2), format(-2, 2); format(cast(-2 as unsigned), 2) format(18446744073709551614, 2) format(-2, 2) 18,446,744,073,709,551,614.00 18,446,744,073,709,551,614.00 -2.00 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select sqrt(cast(-2 as unsigned)), sqrt(18446744073709551614), sqrt(-2); sqrt(cast(-2 as unsigned)) sqrt(18446744073709551614) sqrt(-2) 4294967296 4294967296 NULL +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select round(cast(-2 as unsigned), 1), round(18446744073709551614, 1), round(-2, 1); round(cast(-2 as unsigned), 1) round(18446744073709551614, 1) round(-2, 1) 18446744073709551614 18446744073709551614 -2 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select round(4, cast(-2 as unsigned)), round(4, 18446744073709551614), round(4, -2); round(4, cast(-2 as unsigned)) round(4, 18446744073709551614) round(4, -2) 4 4 0 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select truncate(cast(-2 as unsigned), 1), truncate(18446744073709551614, 1), truncate(-2, 1); truncate(cast(-2 as unsigned), 1) truncate(18446744073709551614, 1) truncate(-2, 1) 18446744073709551614 18446744073709551614 -2 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select truncate(4, cast(-2 as unsigned)), truncate(4, 18446744073709551614), truncate(4, -2); truncate(4, cast(-2 as unsigned)) truncate(4, 18446744073709551614) truncate(4, -2) 4 4 0 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select round(10000000000000000000, -19), truncate(10000000000000000000, -19); round(10000000000000000000, -19) truncate(10000000000000000000, -19) 10000000000000000000 10000000000000000000 @@ -363,12 +385,18 @@ round(4, -4294967200) truncate(4, -4294967200) select mod(cast(-2 as unsigned), 3), mod(18446744073709551614, 3), mod(-2, 3); mod(cast(-2 as unsigned), 3) mod(18446744073709551614, 3) mod(-2, 3) 2 2 -2 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select mod(5, cast(-2 as unsigned)), mod(5, 18446744073709551614), mod(5, -2); mod(5, cast(-2 as unsigned)) mod(5, 18446744073709551614) mod(5, -2) 5 5 1 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement select pow(cast(-2 as unsigned), 5), pow(18446744073709551614, 5), pow(-2, 5); pow(cast(-2 as unsigned), 5) pow(18446744073709551614, 5) pow(-2, 5) 2.13598703592091e+96 2.13598703592091e+96 -32 +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement CREATE TABLE t1 (a timestamp, b varchar(20), c bit(1)); INSERT INTO t1 VALUES('1998-09-23', 'str1', 1), ('2003-03-25', 'str2', 0); SELECT a DIV 900 y FROM t1 GROUP BY y; @@ -488,7 +516,7 @@ SELECT -9999999999999999991 DIV -1; -9999999999999999991 DIV -1 -9223372036854775808 Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-9999999999999999991' to INT. Value truncated. SELECT -9223372036854775808 DIV -1; -9223372036854775808 DIV -1 -9223372036854775808 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index bbfd3880d2e..03fa25ba7b5 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1533,7 +1533,7 @@ select locate('lo','hello',-18446744073709551615); locate('lo','hello',-18446744073709551615) 0 Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select locate('lo','hello',18446744073709551615); locate('lo','hello',18446744073709551615) 0 @@ -1541,22 +1541,22 @@ select locate('lo','hello',-18446744073709551616); locate('lo','hello',-18446744073709551616) 0 Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select locate('lo','hello',18446744073709551616); locate('lo','hello',18446744073709551616) 0 Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. select locate('lo','hello',-18446744073709551617); locate('lo','hello',-18446744073709551617) 0 Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select locate('lo','hello',18446744073709551617); locate('lo','hello',18446744073709551617) 0 Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. select left('hello', 10); left('hello', 10) hello @@ -1588,8 +1588,8 @@ select left('hello', -18446744073709551615); left('hello', -18446744073709551615) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select left('hello', 18446744073709551615); left('hello', 18446744073709551615) hello @@ -1597,26 +1597,26 @@ select left('hello', -18446744073709551616); left('hello', -18446744073709551616) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select left('hello', 18446744073709551616); left('hello', 18446744073709551616) hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. select left('hello', -18446744073709551617); left('hello', -18446744073709551617) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select left('hello', 18446744073709551617); left('hello', 18446744073709551617) hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. select right('hello', 10); right('hello', 10) hello @@ -1648,8 +1648,8 @@ select right('hello', -18446744073709551615); right('hello', -18446744073709551615) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select right('hello', 18446744073709551615); right('hello', 18446744073709551615) hello @@ -1657,26 +1657,26 @@ select right('hello', -18446744073709551616); right('hello', -18446744073709551616) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select right('hello', 18446744073709551616); right('hello', 18446744073709551616) hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. select right('hello', -18446744073709551617); right('hello', -18446744073709551617) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select right('hello', 18446744073709551617); right('hello', 18446744073709551617) hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. select substring('hello', 2, -1); substring('hello', 2, -1) @@ -1708,8 +1708,8 @@ select substring('hello', -18446744073709551615, 1); substring('hello', -18446744073709551615, 1) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select substring('hello', 18446744073709551615, 1); substring('hello', 18446744073709551615, 1) @@ -1717,26 +1717,26 @@ select substring('hello', -18446744073709551616, 1); substring('hello', -18446744073709551616, 1) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select substring('hello', 18446744073709551616, 1); substring('hello', 18446744073709551616, 1) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. select substring('hello', -18446744073709551617, 1); substring('hello', -18446744073709551617, 1) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select substring('hello', 18446744073709551617, 1); substring('hello', 18446744073709551617, 1) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. select substring('hello', 1, -1); substring('hello', 1, -1) @@ -1762,8 +1762,8 @@ select substring('hello', 1, -18446744073709551615); substring('hello', 1, -18446744073709551615) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select substring('hello', 1, 18446744073709551615); substring('hello', 1, 18446744073709551615) hello @@ -1771,26 +1771,26 @@ select substring('hello', 1, -18446744073709551616); substring('hello', 1, -18446744073709551616) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select substring('hello', 1, 18446744073709551616); substring('hello', 1, 18446744073709551616) hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. select substring('hello', 1, -18446744073709551617); substring('hello', 1, -18446744073709551617) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select substring('hello', 1, 18446744073709551617); substring('hello', 1, 18446744073709551617) hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. select substring('hello', -1, -1); substring('hello', -1, -1) @@ -1816,10 +1816,10 @@ select substring('hello', -18446744073709551615, -18446744073709551615); substring('hello', -18446744073709551615, -18446744073709551615) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select substring('hello', 18446744073709551615, 18446744073709551615); substring('hello', 18446744073709551615, 18446744073709551615) @@ -1827,34 +1827,34 @@ select substring('hello', -18446744073709551616, -18446744073709551616); substring('hello', -18446744073709551616, -18446744073709551616) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select substring('hello', 18446744073709551616, 18446744073709551616); substring('hello', 18446744073709551616, 18446744073709551616) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. select substring('hello', -18446744073709551617, -18446744073709551617); substring('hello', -18446744073709551617, -18446744073709551617) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select substring('hello', 18446744073709551617, 18446744073709551617); substring('hello', 18446744073709551617, 18446744073709551617) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. select insert('hello', -1, 1, 'hi'); insert('hello', -1, 1, 'hi') hello @@ -1880,7 +1880,7 @@ select insert('hello', -18446744073709551615, 1, 'hi'); insert('hello', -18446744073709551615, 1, 'hi') hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select insert('hello', 18446744073709551615, 1, 'hi'); insert('hello', 18446744073709551615, 1, 'hi') hello @@ -1888,22 +1888,22 @@ select insert('hello', -18446744073709551616, 1, 'hi'); insert('hello', -18446744073709551616, 1, 'hi') hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select insert('hello', 18446744073709551616, 1, 'hi'); insert('hello', 18446744073709551616, 1, 'hi') hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. select insert('hello', -18446744073709551617, 1, 'hi'); insert('hello', -18446744073709551617, 1, 'hi') hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select insert('hello', 18446744073709551617, 1, 'hi'); insert('hello', 18446744073709551617, 1, 'hi') hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. select insert('hello', 1, -1, 'hi'); insert('hello', 1, -1, 'hi') hi @@ -1929,7 +1929,7 @@ select insert('hello', 1, -18446744073709551615, 'hi'); insert('hello', 1, -18446744073709551615, 'hi') hi Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select insert('hello', 1, 18446744073709551615, 'hi'); insert('hello', 1, 18446744073709551615, 'hi') hi @@ -1937,22 +1937,22 @@ select insert('hello', 1, -18446744073709551616, 'hi'); insert('hello', 1, -18446744073709551616, 'hi') hi Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select insert('hello', 1, 18446744073709551616, 'hi'); insert('hello', 1, 18446744073709551616, 'hi') hi Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. select insert('hello', 1, -18446744073709551617, 'hi'); insert('hello', 1, -18446744073709551617, 'hi') hi Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select insert('hello', 1, 18446744073709551617, 'hi'); insert('hello', 1, 18446744073709551617, 'hi') hi Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. select insert('hello', -1, -1, 'hi'); insert('hello', -1, -1, 'hi') hello @@ -1978,8 +1978,8 @@ select insert('hello', -18446744073709551615, -18446744073709551615, 'hi'); insert('hello', -18446744073709551615, -18446744073709551615, 'hi') hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select insert('hello', 18446744073709551615, 18446744073709551615, 'hi'); insert('hello', 18446744073709551615, 18446744073709551615, 'hi') hello @@ -1987,26 +1987,26 @@ select insert('hello', -18446744073709551616, -18446744073709551616, 'hi'); insert('hello', -18446744073709551616, -18446744073709551616, 'hi') hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select insert('hello', 18446744073709551616, 18446744073709551616, 'hi'); insert('hello', 18446744073709551616, 18446744073709551616, 'hi') hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. select insert('hello', -18446744073709551617, -18446744073709551617, 'hi'); insert('hello', -18446744073709551617, -18446744073709551617, 'hi') hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select insert('hello', 18446744073709551617, 18446744073709551617, 'hi'); insert('hello', 18446744073709551617, 18446744073709551617, 'hi') hello Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. select repeat('hello', -1); repeat('hello', -1) @@ -2038,8 +2038,8 @@ select repeat('hello', -18446744073709551615); repeat('hello', -18446744073709551615) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select repeat('hello', 18446744073709551615); repeat('hello', 18446744073709551615) NULL @@ -2049,27 +2049,27 @@ select repeat('hello', -18446744073709551616); repeat('hello', -18446744073709551616) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select repeat('hello', 18446744073709551616); repeat('hello', 18446744073709551616) NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated select repeat('hello', -18446744073709551617); repeat('hello', -18446744073709551617) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select repeat('hello', 18446744073709551617); repeat('hello', 18446744073709551617) NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated select space(-1); space(-1) @@ -2102,8 +2102,8 @@ select space(-18446744073709551615); space(-18446744073709551615) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select space(18446744073709551615); space(18446744073709551615) NULL @@ -2113,27 +2113,27 @@ select space(-18446744073709551616); space(-18446744073709551616) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select space(18446744073709551616); space(18446744073709551616) NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated select space(-18446744073709551617); space(-18446744073709551617) Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select space(18446744073709551617); space(18446744073709551617) NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated select rpad('hello', -1, '1'); rpad('hello', -1, '1') @@ -2166,8 +2166,8 @@ select rpad('hello', -18446744073709551615, '1'); rpad('hello', -18446744073709551615, '1') NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select rpad('hello', 18446744073709551615, '1'); rpad('hello', 18446744073709551615, '1') NULL @@ -2177,27 +2177,27 @@ select rpad('hello', -18446744073709551616, '1'); rpad('hello', -18446744073709551616, '1') NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select rpad('hello', 18446744073709551616, '1'); rpad('hello', 18446744073709551616, '1') NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated select rpad('hello', -18446744073709551617, '1'); rpad('hello', -18446744073709551617, '1') NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select rpad('hello', 18446744073709551617, '1'); rpad('hello', 18446744073709551617, '1') NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated select lpad('hello', -1, '1'); lpad('hello', -1, '1') @@ -2230,8 +2230,8 @@ select lpad('hello', -18446744073709551615, '1'); lpad('hello', -18446744073709551615, '1') NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551615' to INT. Value truncated. select lpad('hello', 18446744073709551615, '1'); lpad('hello', 18446744073709551615, '1') NULL @@ -2241,27 +2241,27 @@ select lpad('hello', -18446744073709551616, '1'); lpad('hello', -18446744073709551616, '1') NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551616' to INT. Value truncated. select lpad('hello', 18446744073709551616, '1'); lpad('hello', 18446744073709551616, '1') NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551616' to INT. Value truncated. Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated select lpad('hello', -18446744073709551617, '1'); lpad('hello', -18446744073709551617, '1') NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '-18446744073709551617' to INT. Value truncated. select lpad('hello', 18446744073709551617, '1'); lpad('hello', 18446744073709551617, '1') NULL Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. +Error 1655 Got overflow when converting '18446744073709551617' to INT. Value truncated. Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated SET @orig_sql_mode = @@SQL_MODE; SET SQL_MODE=traditional; diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index a97c040d9ac..9c446b4e21f 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1014,6 +1014,7 @@ SELECT MAKETIME(CAST(-1 AS UNSIGNED), 0, 0); MAKETIME(CAST(-1 AS UNSIGNED), 0, 0) 838:59:59 Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement Warning 1292 Truncated incorrect time value: '18446744073709551615:00:00' SELECT EXTRACT(HOUR FROM '100000:02:03'); EXTRACT(HOUR FROM '100000:02:03') @@ -1033,6 +1034,7 @@ SELECT SEC_TO_TIME(CAST(-1 AS UNSIGNED)); SEC_TO_TIME(CAST(-1 AS UNSIGNED)) 838:59:59 Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement Warning 1292 Truncated incorrect time value: '18446744073709551615' SET NAMES latin1; SET character_set_results = NULL; diff --git a/mysql-test/r/sp-vars.result b/mysql-test/r/sp-vars.result index f5420a62f63..6cd70466d1b 100644 --- a/mysql-test/r/sp-vars.result +++ b/mysql-test/r/sp-vars.result @@ -380,7 +380,7 @@ ERROR 22003: Out of range value for column 'sp_vars_check_ret1()' at row 1 SELECT sp_vars_check_ret2(); ERROR 22003: Out of range value for column 'sp_vars_check_ret2()' at row 1 SELECT sp_vars_check_ret3(); -ERROR HY000: Incorrect integer value: 'Hello, world' for column 'sp_vars_check_ret3()' at row 1 +ERROR 22007: Incorrect integer value: 'Hello, world' for column 'sp_vars_check_ret3()' at row 1 SELECT sp_vars_check_ret4(); sp_vars_check_ret4() 154.12 diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 241f4198bf7..4cd68a52748 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -260,24 +260,24 @@ INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE)); INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE)); -ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 +ERROR 22007: Incorrect datetime value: '2004-10-0' INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE)); -ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 +ERROR 22007: Incorrect datetime value: '2004-0-10' INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); -ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 +ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); -ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1 +ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); -ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 +ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); -ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1 +ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME)); ERROR 22007: Incorrect datetime value: '0000-00-00' drop table t1; @@ -287,24 +287,26 @@ INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE)); INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE)); -ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 +ERROR 22007: Incorrect datetime value: '2004-10-0' INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE)); -ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 +ERROR 22007: Incorrect datetime value: '2004-0-10' +INSERT INTO t1 (col1) VALUES('2004-0-10'); +ERROR 22007: Incorrect date value: '2004-0-10' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); -ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 +ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); -ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1 +ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); -ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 +ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); -ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1 +ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME)); ERROR 22007: Incorrect datetime value: '0000-00-00' drop table t1; @@ -364,9 +366,9 @@ Warnings: Error 1365 Division by 0 Error 1365 Division by 0 INSERT INTO t1 (col1) VALUES (''); -ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 +ERROR 22007: Incorrect integer value: '' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); -ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 +ERROR 22007: Incorrect integer value: 'a59b' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); @@ -447,9 +449,9 @@ ERROR 22012: Division by 0 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); -ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 +ERROR 22007: Incorrect integer value: '' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); -ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 +ERROR 22007: Incorrect integer value: 'a59b' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); @@ -531,9 +533,9 @@ ERROR 22012: Division by 0 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); -ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 +ERROR 22007: Incorrect integer value: '' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); -ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 +ERROR 22007: Incorrect integer value: 'a59b' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); @@ -615,9 +617,9 @@ ERROR 22012: Division by 0 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); -ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 +ERROR 22007: Incorrect integer value: '' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); -ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 +ERROR 22007: Incorrect integer value: 'a59b' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); @@ -697,9 +699,9 @@ ERROR 22012: Division by 0 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); -ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 +ERROR 22007: Incorrect integer value: '' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); -ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 +ERROR 22007: Incorrect integer value: 'a59b' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); @@ -776,7 +778,7 @@ ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES ('-100E+1'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES ('-100E'); -ERROR HY000: Incorrect decimal value: '-100E' for column 'col1' at row 1 +ERROR 22007: Incorrect decimal value: '-100E' for column 'col1' at row 1 UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11; ERROR 22003: Out of range value for column 'col1' at row 6 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; @@ -784,11 +786,11 @@ ERROR 22012: Division by 0 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); -ERROR HY000: Incorrect decimal value: '' for column 'col1' at row 1 +ERROR 22007: Incorrect decimal value: '' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); -ERROR HY000: Incorrect decimal value: 'a59b' for column 'col1' at row 1 +ERROR 22007: Incorrect decimal value: 'a59b' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('1a'); -ERROR HY000: Incorrect decimal value: '1a' for column 'col1' at row 1 +ERROR 22007: Incorrect decimal value: '1a' for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); Warnings: Note 1265 Data truncated for column 'col1' at row 1 @@ -1361,34 +1363,34 @@ col5 mediumint, col6 mediumint unsigned, col7 int, col8 int unsigned, col9 bigint, col10 bigint unsigned); insert into t1(col1) values('-'); -ERROR HY000: Incorrect integer value: '-' for column 'col1' at row 1 +ERROR 22007: Incorrect integer value: '-' for column 'col1' at row 1 insert into t1(col2) values('+'); -ERROR HY000: Incorrect integer value: '+' for column 'col2' at row 1 +ERROR 22007: Incorrect integer value: '+' for column 'col2' at row 1 insert into t1(col3) values('-'); -ERROR HY000: Incorrect integer value: '-' for column 'col3' at row 1 +ERROR 22007: Incorrect integer value: '-' for column 'col3' at row 1 insert into t1(col4) values('+'); -ERROR HY000: Incorrect integer value: '+' for column 'col4' at row 1 +ERROR 22007: Incorrect integer value: '+' for column 'col4' at row 1 insert into t1(col5) values('-'); -ERROR HY000: Incorrect integer value: '-' for column 'col5' at row 1 +ERROR 22007: Incorrect integer value: '-' for column 'col5' at row 1 insert into t1(col6) values('+'); -ERROR HY000: Incorrect integer value: '+' for column 'col6' at row 1 +ERROR 22007: Incorrect integer value: '+' for column 'col6' at row 1 insert into t1(col7) values('-'); -ERROR HY000: Incorrect integer value: '-' for column 'col7' at row 1 +ERROR 22007: Incorrect integer value: '-' for column 'col7' at row 1 insert into t1(col8) values('+'); -ERROR HY000: Incorrect integer value: '+' for column 'col8' at row 1 +ERROR 22007: Incorrect integer value: '+' for column 'col8' at row 1 insert into t1(col9) values('-'); -ERROR HY000: Incorrect integer value: '-' for column 'col9' at row 1 +ERROR 22007: Incorrect integer value: '-' for column 'col9' at row 1 insert into t1(col10) values('+'); -ERROR HY000: Incorrect integer value: '+' for column 'col10' at row 1 +ERROR 22007: Incorrect integer value: '+' for column 'col10' at row 1 drop table t1; set sql_mode='traditional'; create table t1(a year); insert into t1 values ('-'); -ERROR HY000: Incorrect integer value: '-' for column 'a' at row 1 +ERROR 22007: Incorrect integer value: '-' for column 'a' at row 1 insert into t1 values ('+'); -ERROR HY000: Incorrect integer value: '+' for column 'a' at row 1 +ERROR 22007: Incorrect integer value: '+' for column 'a' at row 1 insert into t1 values (''); -ERROR HY000: Incorrect integer value: '' for column 'a' at row 1 +ERROR 22007: Incorrect integer value: '' for column 'a' at row 1 insert into t1 values ('2000a'); ERROR 01000: Data truncated for column 'a' at row 1 insert into t1 values ('2E3x'); diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 70ee3a56cf3..3ce3805df62 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -825,7 +825,7 @@ Error 1365 Division by 0 Error 1365 Division by 0 Error 1365 Division by 0 INSERT INTO Sow6_2f VALUES ('a59b'); -ERROR HY000: Incorrect decimal value: 'a59b' for column 'col1' at row 1 +ERROR 22007: Incorrect decimal value: 'a59b' for column 'col1' at row 1 drop table Sow6_2f; select 10.3330000000000/12.34500000; 10.3330000000000/12.34500000 @@ -838,12 +838,12 @@ select 9999999999999999999999999999999999999999999999999999999999999999999999999 x 99999999999999999999999999999999999999999999999999999999999999999 Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '' to DECIMAL. Value truncated. select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 + 1 as x; x 100000000000000000000000000000000000000000000000000000000000000000 Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '' to DECIMAL. Value truncated. select 0.190287977636363637 + 0.040372670 * 0 - 0; 0.190287977636363637 + 0.040372670 * 0 - 0 0.190287977636363637 @@ -1380,15 +1380,15 @@ create table t1 (c1 decimal(64)); insert into t1 values( 89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000); Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '' to DECIMAL. Value truncated. Warning 1264 Out of range value for column 'c1' at row 1 insert into t1 values( 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999); Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '' to DECIMAL. Value truncated. +Error 1655 Got overflow when converting '' to DECIMAL. Value truncated. +Error 1655 Got overflow when converting '' to DECIMAL. Value truncated. Warning 1264 Out of range value for column 'c1' at row 1 insert into t1 values(1e100); Warnings: @@ -1432,7 +1432,7 @@ select cast(19999999999999999999 as unsigned); cast(19999999999999999999 as unsigned) 18446744073709551615 Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '19999999999999999999' to UNSIGNED INT. Value truncated. create table t1(a decimal(18)); insert into t1 values(123456789012345678); alter table t1 modify column a decimal(19); @@ -1674,7 +1674,7 @@ CREATE TABLE t1 SELECT /* 82 */ 1000000000000000000000000000000000000000000000000000000000000000000000000000000001 AS c1; Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' +Error 1655 Got overflow when converting '' to DECIMAL. Value truncated. DESC t1; Field Type Null Key Default Extra c1 decimal(65,0) NO 0 diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 95a969d3791..1c75e415b75 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -324,7 +324,7 @@ select CAST(a AS DECIMAL(13,5)) FROM (SELECT '' as a) t; CAST(a AS DECIMAL(13,5)) 0.00000 Warnings: -Error 1366 Incorrect decimal value: '' for column '' at row 0 +Error 1657 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: '' create table t1 (a integer unsigned); insert into t1 values (1),(-1),(0),(-2); |