cast.test 5.43 KB
Newer Older
1 2 3 4 5 6 7 8 9
#
# Test of cast function
#

select CAST(1-2 AS UNSIGNED);
select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER);
select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1;
select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1;
select ~5, cast(~5 as signed);
10
explain extended select ~5, cast(~5 as signed);
11
select cast(5 as unsigned) -6.0;
12 13
select cast(NULL as signed), cast(1/0 as signed); 
select cast(NULL as unsigned), cast(1/0 as unsigned); 
14 15 16
select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A";
select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME);
select cast("1:2:3" as TIME);
17
select CONVERT("2004-01-22 21:45:33",DATE);
18 19 20
select CONVERT(DATE "2004-01-22 21:45:33" USING latin1);
select CONVERT(DATE "2004-01-22 21:45:33",CHAR);
select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4));
21 22
select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4));
select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4));
23 24 25 26
select CAST(0xb3 as signed);
select CAST(0x8fffffffffffffff as signed);
select CAST(0xffffffffffffffff as unsigned);
select CAST(0xfffffffffffffffe as signed);
27

28 29 30 31 32 33 34 35 36
# out-of-range cases
select cast('18446744073709551616' as unsigned);
select cast('18446744073709551616' as signed);
select cast('9223372036854775809' as signed);
select cast('-1' as unsigned);
select cast('abc' as signed);
select cast('1a' as signed);
select cast('' as signed);

37 38 39
#
# Character set convertion
#
40
set names binary;
41 42 43 44 45 46
select cast(_latin1'test' as char character set latin2);
select cast(_koi8r'' as char character set cp1251);
create table t1 select cast(_koi8r'' as char character set cp1251) as t;
show create table t1;
drop table t1;

47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
#
# CAST to CHAR with/without length
#
select
  cast(_latin1'ab'  AS char)    as c1,
  cast(_latin1'a '  AS char)    as c2,
  cast(_latin1'abc' AS char(2)) as c3,
  cast(_latin1'a  ' AS char(2)) as c4,
  cast(_latin1'a'   AS char(2)) as c5;

create table t1 select
  cast(_latin1'ab'  AS char)    as c1,
  cast(_latin1'a '  AS char)    as c2,
  cast(_latin1'abc' AS char(2)) as c3,
  cast(_latin1'a  ' AS char(2)) as c4,
  cast(_latin1'a'   AS char(2)) as c5;
select * from t1;
show create table t1;
drop table t1;

#
# CAST to NCHAR with/without length
#
select
  cast(_koi8r''  AS nchar)    as c1,
  cast(_koi8r' '  AS nchar)    as c2,
  cast(_koi8r'' AS nchar(2)) as c3,
  cast(_koi8r'  ' AS nchar(2)) as c4,
  cast(_koi8r''   AS nchar(2)) as c5;

create table t1 select
  cast(_koi8r''  AS nchar)    as c1,
  cast(_koi8r' '  AS nchar)    as c2,
  cast(_koi8r'' AS nchar(2)) as c3,
  cast(_koi8r'  ' AS nchar(2)) as c4,
  cast(_koi8r''   AS nchar(2)) as c5;
select * from t1;
show create table t1;
drop table t1;

87 88 89 90 91 92 93 94 95 96 97 98 99
#
# Bug 2202
# CAST from BINARY to non-BINARY and from non-BINARY to BINARY
#
create table t1 (a binary(10), b char(10) character set koi8r);
insert into t1 values (_binary'',_binary'');
select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
set names koi8r;
select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
set names cp1251;
select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
drop table t1;
set names binary;
100

101 102 103 104 105 106 107
#
# The following should be fixed in 4.1
#

select cast("2001-1-1" as date) = "2001-01-01";
select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00";
select cast("1:2:3" as TIME) = "1:02:03";
108
select cast(NULL as DATE);
109
select cast(NULL as BINARY);
110 111 112 113 114 115 116 117 118 119 120 121 122

#
# Bug #5228 ORDER BY CAST(enumcol) sorts incorrectly under certain conditions
#
CREATE TABLE t1 (a enum ('aac','aab','aaa') not null);
INSERT INTO t1 VALUES ('aaa'),('aab'),('aac');
# these two should be in enum order
SELECT a, CAST(a AS CHAR) FROM t1 ORDER BY CAST(a AS UNSIGNED) ;
SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a;
# these two should be in alphabetic order
SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ;
SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a;
DROP TABLE t1;
123 124 125 126 127 128 129 130 131 132

#
# Test for bug #6914 "Problems using time()/date() output in expressions".
# When we are casting datetime value to DATE/TIME we should throw away
# time/date parts (correspondingly).
#
select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour);
select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00');
# Still we should not throw away "days" part of time value
select timediff(cast('1 12:00:00' as time), '12:00:00');
133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148

#
# Bug #7036: Casting from string to unsigned would cap value of result at
# maximum signed value instead of maximum unsigned value
#
select cast(18446744073709551615 as unsigned);
select cast(18446744073709551615 as signed);
select cast('18446744073709551615' as unsigned);
select cast('18446744073709551615' as signed);
select cast('9223372036854775807' as signed);

select cast(concat('184467440','73709551615') as unsigned);
select cast(concat('184467440','73709551615') as signed);

select cast(repeat('1',20) as unsigned);
select cast(repeat('1',20) as signed);
149

150 151 152 153 154 155
#
# Bug#8663 cant use bgint unsigned as input to cast
#
select cast(19999999999999999999 as unsigned);


156 157 158 159 160
#
# Bug #13344: cast of large decimal to signed int not handled correctly
#
select cast(1.0e+300 as signed int);

161 162 163 164 165 166 167 168 169
#
# Bugs: #15098: CAST(column double TO signed int), wrong result
#
CREATE TABLE t1 (f1 double);
INSERT INTO t1 SET f1 = -1.0e+30 ;
INSERT INTO t1 SET f1 = +1.0e+30 ;
SELECT f1 AS double_val, CAST(f1 AS SIGNED INT) AS cast_val FROM t1;
DROP TABLE t1;					   

170 171 172 173 174 175 176
#
# Bug #23938: cast(NULL as DATE)
#

select isnull(date(NULL)), isnull(cast(NULL as DATE));

--echo End of 4.1 tests