diff options
Diffstat (limited to 'mysql-test/r/cast.result')
| -rw-r--r-- | mysql-test/r/cast.result | 295 |
1 files changed, 288 insertions, 7 deletions
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 89cbda9847c..5e6078c5f39 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 @@ -23,12 +33,129 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select ~(5) AS `~5`,cast(~(5) as signed) AS `cast(~5 as signed)` +select cast(18446744073709551615 as signed); +cast(18446744073709551615 as signed) +-1 select cast(5 as unsigned) -6.0; cast(5 as unsigned) -6.0 -1.0 select cast(NULL as signed), cast(1/0 as signed); cast(NULL as signed) cast(1/0 as signed) NULL NULL +select cast(1 as double(5,2)); +cast(1 as double(5,2)) +1.00 +select cast("5.2222" as double(5,2)); +cast("5.2222" as double(5,2)) +5.22 +select cast(12.444 as double(5,2)); +cast(12.444 as double(5,2)) +12.44 +select cast(cast(12.444 as decimal(10,3)) as double(5,2)); +cast(cast(12.444 as decimal(10,3)) as double(5,2)) +12.44 +select cast(null as double(5,2)); +cast(null as double(5,2)) +NULL +select cast(12.444 as double); +cast(12.444 as double) +12.444 +select cast(cast("20:01:01" as time) as datetime); +cast(cast("20:01:01" as time) as datetime) +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.0000000000 +select cast(cast("2011-04-05 8:46:06.23434" AS datetime) as decimal(32,6)); +cast(cast("2011-04-05 8:46:06.23434" AS datetime) as decimal(32,6)) +20110405084606.000000 +# +# Check handling of cast with microseconds +# +select cast(cast(20010203101112.121314 as double) as datetime); +cast(cast(20010203101112.121314 as double) as datetime) +2001-02-03 10:11:12 +select cast(cast(010203101112.12 as double) as datetime); +cast(cast(010203101112.12 as double) as datetime) +0001-02-03 10:11:12 +select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime); +cast(cast(20010203101112.121314 as decimal(32,6)) as datetime) +2001-02-03 10:11:12 +select cast(20010203101112.121314 as datetime); +cast(20010203101112.121314 as datetime) +2001-02-03 10:11:12 +select cast(110203101112.121314 as datetime); +cast(110203101112.121314 as datetime) +0011-02-03 10:11:12 +select cast(cast(010203101112.12 as double) as datetime); +cast(cast(010203101112.12 as double) as datetime) +0001-02-03 10:11:12 +select cast("2011-02-03 10:11:12.123456" as datetime); +cast("2011-02-03 10:11:12.123456" as datetime) +2011-02-03 10:11:12 +select cast("2011-02-03 10:11:12.123456" as datetime(0)); +cast("2011-02-03 10:11:12.123456" as datetime(0)) +2011-02-03 10:11:12 +select cast("2011-02-03 10:11:12.123456" as datetime(5)); +cast("2011-02-03 10:11:12.123456" as datetime(5)) +2011-02-03 10:11:12.12345 +select cast("2011-02-03 10:11:12.123456" as datetime(6)); +cast("2011-02-03 10:11:12.123456" as datetime(6)) +2011-02-03 10:11:12.123456 +select cast("2011-02-03 10:11:12" as datetime(6)); +cast("2011-02-03 10:11:12" as datetime(6)) +2011-02-03 10:11:12.000000 +select cast(cast(20010203101112.1 as double) as datetime(1)); +cast(cast(20010203101112.1 as double) as datetime(1)) +2001-02-03 10:11:12.1 +select cast(cast(010203101112.12 as double) as datetime(2)); +cast(cast(010203101112.12 as double) as datetime(2)) +0001-02-03 10:11:12.12 +select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6)); +cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6)) +2001-02-03 10:11:12.121314 +select cast(20010203101112.121314 as datetime(6)); +cast(20010203101112.121314 as datetime(6)) +2001-02-03 10:11:12.121314 +select cast(110203101112.121314 as datetime(6)); +cast(110203101112.121314 as datetime(6)) +0011-02-03 10:11:12.121314 +select cast(cast(010203101112.12 as double) as datetime(6)); +cast(cast(010203101112.12 as double) as datetime(6)) +0001-02-03 10:11:12.120000 +select cast("2011-02-03 10:11:12.123456" as time); +cast("2011-02-03 10:11:12.123456" as time) +10:11:12 +select cast("2011-02-03 10:11:12.123456" as time(6)); +cast("2011-02-03 10:11:12.123456" as time(6)) +10:11:12.123456 +select cast("10:11:12.123456" as time); +cast("10:11:12.123456" as time) +10:11:12 +select cast("10:11:12.123456" as time(0)); +cast("10:11:12.123456" as time(0)) +10:11:12 +select cast("10:11:12.123456" as time(5)); +cast("10:11:12.123456" as time(5)) +10:11:12.12345 +select cast("10:11:12.123456" as time(6)); +cast("10:11:12.123456" as time(6)) +10:11:12.123456 +select cast("10:11:12" as time(6)); +cast("10:11:12" as time(6)) +10:11:12.000000 +select cast(cast("2011-04-05 8:46:06.123456" AS datetime) as time); +cast(cast("2011-04-05 8:46:06.123456" AS datetime) as time) +08:46:06 +select cast(cast("2011-04-05 8:46:06.123456" AS datetime) as time(6)); +cast(cast("2011-04-05 8:46:06.123456" AS datetime) as time(6)) +08:46:06.000000 +select cast(cast("2011-04-05 8:46:06.123456" AS datetime(6)) as time); +cast(cast("2011-04-05 8:46:06.123456" AS datetime(6)) as time) +08:46:06 +select cast(cast("2011-04-05 8:46:06.123456" AS datetime(6)) as time(6)); +cast(cast("2011-04-05 8:46:06.123456" AS datetime(6)) as time(6)) +08:46:06.123456 select cast(NULL as unsigned), cast(1/0 as unsigned); cast(NULL as unsigned) cast(1/0 as unsigned) NULL NULL @@ -111,6 +238,113 @@ 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: +Warning 1264 Out of range value for column 'cast(1000 as decimal(5,2))' at row 1 +select cast(-1000 as decimal(5,2)); +cast(-1000 as decimal(5,2)) +-999.99 +Warnings: +Warning 1264 Out of range value for column 'cast(-1000 as decimal(5,2))' at row 1 +select cast(1000 as double(5,2)); +cast(1000 as double(5,2)) +999.99 +Warnings: +Warning 1264 Out of range value for column 'cast(1000 as double(5,2))' at row 1 +select cast(-1000 as double(5,2)); +cast(-1000 as double(5,2)) +-999.99 +Warnings: +Warning 1264 Out of range value for column 'cast(-1000 as double(5,2))' at row 1 +select cast(010203101112.121314 as datetime); +cast(010203101112.121314 as datetime) +0001-02-03 10:11:12 +select cast(120010203101112.121314 as datetime); +cast(120010203101112.121314 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '120010203101112.121314' +select cast(cast(1.1 as decimal) as datetime); +cast(cast(1.1 as decimal) as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '1' +select cast(cast(-1.1 as decimal) as datetime); +cast(cast(-1.1 as decimal) as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '-1' +select cast('0' as date); +cast('0' as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '0' +select cast('' as date); +cast('' as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '' +select cast('0' as datetime); +cast('0' as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '0' +select cast('' as datetime); +cast('' as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '' +select cast('0' as time); +cast('0' as time) +00:00:00 +select cast('' as time); +cast('' as time) +NULL +Warnings: +Warning 1292 Truncated incorrect time value: '' +select cast(NULL as DATE); +cast(NULL as DATE) +NULL +select cast(NULL as DATETIME); +cast(NULL as DATETIME) +NULL +select cast(NULL as TIME); +cast(NULL as TIME) +NULL +select cast(NULL as BINARY); +cast(NULL as BINARY) +NULL +select cast(cast(120010203101112.121314 as double) as datetime); +cast(cast(120010203101112.121314 as double) as datetime) +NULL +select cast(cast(1.1 as double) as datetime); +cast(cast(1.1 as double) as datetime) +0000-00-00 00:00:01 +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 +380,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 '1'. Maximum is 65. +select cast(1 as decimal(66,6)); +ERROR 42000: Too big precision 66 specified for '1'. Maximum is 65. +select cast(1 as decimal(64,63)); +ERROR 42000: Too big scale 63 specified for '1'. Maximum is 30. +select cast(1 as double(64,63)); +ERROR 42000: Too big scale 63 specified for '1'. Maximum is 30. set names binary; select cast(_latin1'test' as char character set latin2); cast(_latin1'test' as char character set latin2) @@ -254,13 +500,7 @@ cast("2001-1-1" as datetime) = "2001-01-01 00:00:00" 1 select cast("1:2:3" as TIME) = "1:02:03"; cast("1:2:3" as TIME) = "1:02:03" -0 -select cast(NULL as DATE); -cast(NULL as DATE) -NULL -select cast(NULL as BINARY); -cast(NULL as BINARY) -NULL +1 CREATE TABLE t1 (a enum ('aac','aab','aaa') not null); INSERT INTO t1 VALUES ('aaa'),('aab'),('aac'); SELECT a, CAST(a AS CHAR) FROM t1 ORDER BY CAST(a AS UNSIGNED) ; @@ -337,6 +577,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 ; @@ -469,3 +724,29 @@ Warnings: Warning 1301 Result of cast_as_char() was larger than max_allowed_packet (2048) - truncated SET @@GLOBAL.max_allowed_packet=default; End of 5.1 tests +select cast("2101-00-01 02:03:04" as datetime); +cast("2101-00-01 02:03:04" as datetime) +2101-00-01 02:03:04 +select cast(cast("2101-00-01 02:03:04" as datetime) as time); +cast(cast("2101-00-01 02:03:04" as datetime) as time) +02:03:04 +SELECT CAST(CAST('20:05:05' AS TIME) as date); +CAST(CAST('20:05:05' AS TIME) as date) +0000-00-00 +set sql_mode= TRADITIONAL; +select cast("2101-00-01 02:03:04" as datetime); +cast("2101-00-01 02:03:04" as datetime) +2101-00-01 02:03:04 +select cast(cast("2101-00-01 02:03:04" as datetime) as time); +cast(cast("2101-00-01 02:03:04" as datetime) as time) +02:03:04 +SELECT CAST(CAST('20:05:05' AS TIME) as date); +CAST(CAST('20:05:05' AS TIME) as date) +0000-00-00 +set sql_mode=DEFAULT; +create table t1 (f1 time, f2 date, f3 datetime); +insert into t1 values ('11:22:33','2011-12-13','2011-12-13 11:22:33'); +select cast(f1 as unsigned), cast(f2 as unsigned), cast(f3 as unsigned) from t1; +cast(f1 as unsigned) cast(f2 as unsigned) cast(f3 as unsigned) +112233 20111213 20111213112233 +drop table t1; |