1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
#
# 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);
explain extended select ~5, cast(~5 as signed);
select cast(5 as unsigned) -6.0;
select cast(NULL as signed), cast(1/0 as signed);
select cast(NULL as unsigned), cast(1/0 as unsigned);
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);
select CONVERT("2004-01-22 21:45:33",DATE);
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));
select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4));
select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4));
# 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);
#
# Character set convertion
#
set names binary;
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;
#
# 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;
#
# 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;
#
# 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";
select cast(NULL as DATE);
select cast(NULL as BINARY);
#
# 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;
#
# 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');
#
# 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);
|
