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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
#
# Test of cast function
#
#
# Bug #28250: Run-Time Check Failure #3 - The variable 'value' is being used
# without being def
#
# The following line causes Run-Time Check Failure on
# binaries built with Visual C++ 2005
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 CONVERT("2004-01-22 21:45:33",DATE);
select 10+'10';
select 10.0+'10';
select 10E+0+'10';
# The following cast creates warnings
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));
select 10+'a';
select 10.0+cast('a' as decimal);
select 10E+0+'a';
#
# CAST to CHAR with/without length
#
select
cast('ab' AS char) as c1,
cast('a ' AS char) as c2,
cast('abc' AS char(2)) as c3,
cast('a ' AS char(2)) as c4,
hex(cast('a' AS char(2))) as c5;
select cast(1000 as CHAR(3));
# Should throw an error about 'abc' being too large for a char(2)
--error 1292
create table t1 select
cast('ab' AS char) as c1,
cast('a ' AS char) as c2,
cast('abc' AS char(2)) as c3,
cast('a ' AS char(2)) as c4,
cast('a' AS char(2)) as c5;
#
# CAST to NCHAR with/without length
#
select
cast('ÆÇ' AS char) as c1,
cast('Æ ' AS char) as c2,
cast('ÆÇÈ' AS char(2)) as c3,
cast('Æ ' AS char(2)) as c4,
cast('Æ' AS char(2)) as c5;
# BUG in drizzletest - can't handle these chars right
# # Should throw an error about incorrect
#--error 1292
#create table t1 select
# cast('ÆÇ' AS char) as c1,
# cast('Æ ' AS char) as c2,
# cast('ÆÇÈ' AS char(2)) as c3,
# cast('Æ ' AS char(2)) as c4,
# cast('Æ' AS char(2)) as c5;
#
# 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(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');
# should be in enum order
SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a;
# should be in alphabetic order
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);
# Still we should not throw away "days" part of time value
#
# Bug #23938: cast(NULL as DATE)
#
select isnull(date(NULL)), isnull(cast(NULL as DATE));
#decimal-related additions
select cast('1.2' as decimal(3,2));
select 1e18 * cast('1.2' as decimal(3,2));
set @v1=1e18;
select cast(@v1 as decimal(22, 2));
select cast(-1e18 as decimal(22,2));
# Test for bug #11283: field conversion from varchar, and text types to decimal
#
CREATE TABLE t1 (v varchar(10), tt tinytext, t text,
mt mediumtext, lt longtext);
INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05');
SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL),
CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1;
DROP TABLE t1;
#
# Bug #10237 (CAST(NULL DECIMAL) crashes server)
#
select cast(NULL as decimal(6)) as t1;
#
# Bug #17903: cast to char results in binary
#
select hex(cast('a' as char(2) binary));
select hex(cast('a' as binary(2)));
select hex(cast('a' as char(2) binary));
#
# Bug #31990: MINUTE() and SECOND() return bogus results when used on a DATE
#
# Show that HH:MM:SS of a DATE are 0, and that it's the same for columns
# and typecasts (NULL in, NULL out).
CREATE TABLE t1 (f1 DATE);
INSERT INTO t1 VALUES ('2007-07-19'), (NULL);
SELECT HOUR(f1),
MINUTE(f1),
SECOND(f1) FROM t1;
SELECT HOUR(CAST('2007-07-19' AS DATE)),
MINUTE(CAST('2007-07-19' AS DATE)),
SECOND(CAST('2007-07-19' AS DATE));
SELECT HOUR(CAST(NULL AS DATE)),
MINUTE(CAST(NULL AS DATE)),
SECOND(CAST(NULL AS DATE));
SELECT HOUR(NULL),
MINUTE(NULL),
SECOND(NULL);
DROP TABLE t1;
--echo End of 5.0 tests
|