diff options
Diffstat (limited to 'mysql-test/r/cast.result')
| -rw-r--r-- | mysql-test/r/cast.result | 197 |
1 files changed, 191 insertions, 6 deletions
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 ; |