2
# Test of cast function
6
# Bug #28250: Run-Time Check Failure #3 - The variable 'value' is being used
9
# The following line causes Run-Time Check Failure on
10
# binaries built with Visual C++ 2005
11
select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A";
12
select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME);
13
select CONVERT("2004-01-22 21:45:33",DATE);
18
# The following cast creates warnings
20
select CONVERT(DATE "2004-01-22 21:45:33",CHAR);
21
select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4));
22
select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4));
23
select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4));
25
select 10.0+cast('a' as decimal);
29
# CAST to CHAR with/without length
32
cast('ab' AS char) as c1,
33
cast('a ' AS char) as c2,
34
cast('abc' AS char(2)) as c3,
35
cast('a ' AS char(2)) as c4,
36
hex(cast('a' AS char(2))) as c5;
37
select cast(1000 as CHAR(3));
39
# Should throw an error about 'abc' being too large for a char(2)
41
create table t1 select
42
cast('ab' AS char) as c1,
43
cast('a ' AS char) as c2,
44
cast('abc' AS char(2)) as c3,
45
cast('a ' AS char(2)) as c4,
46
cast('a' AS char(2)) as c5;
49
# CAST to NCHAR with/without length
52
cast('��' AS char) as c1,
53
cast('� ' AS char) as c2,
54
cast('���' AS char(2)) as c3,
55
cast('� ' AS char(2)) as c4,
56
cast('�' AS char(2)) as c5;
58
# BUG in drizzletest - can't handle these chars right
59
# # Should throw an error about incorrect
61
#create table t1 select
62
# cast('��' AS char) as c1,
63
# cast('� ' AS char) as c2,
64
# cast('���' AS char(2)) as c3,
65
# cast('� ' AS char(2)) as c4,
66
# cast('�' AS char(2)) as c5;
69
# The following should be fixed in 4.1
72
select cast("2001-1-1" as date) = "2001-01-01";
73
select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00";
74
select cast(NULL as DATE);
75
select cast(NULL as BINARY);
78
# Bug #5228 ORDER BY CAST(enumcol) sorts incorrectly under certain conditions
80
CREATE TABLE t1 (a enum ('aac','aab','aaa') not null);
81
INSERT INTO t1 VALUES ('aaa'),('aab'),('aac');
82
# should be in enum order
83
SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a;
84
# should be in alphabetic order
85
SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a;
89
# Test for bug #6914 "Problems using time()/date() output in expressions".
90
# When we are casting datetime value to DATE/TIME we should throw away
91
# time/date parts (correspondingly).
93
select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour);
94
# Still we should not throw away "days" part of time value
97
# Bug #23938: cast(NULL as DATE)
100
select isnull(date(NULL)), isnull(cast(NULL as DATE));
104
#decimal-related additions
105
select cast('1.2' as decimal(3,2));
106
select 1e18 * cast('1.2' as decimal(3,2));
108
select cast(@v1 as decimal(22, 2));
109
select cast(-1e18 as decimal(22,2));
111
# Test for bug #11283: field conversion from varchar, and text types to decimal
114
CREATE TABLE t1 (v varchar(10), tt tinytext, t text,
115
mt mediumtext, lt longtext);
116
INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05');
118
SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL),
119
CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1;
124
# Bug #10237 (CAST(NULL DECIMAL) crashes server)
126
select cast(NULL as decimal(6)) as t1;
130
# Bug #17903: cast to char results in binary
132
select hex(cast('a' as binary(2)));
136
# Bug #31990: MINUTE() and SECOND() return bogus results when used on a DATE
139
# Show that HH:MM:SS of a DATE are 0, and that it's the same for columns
140
# and typecasts (NULL in, NULL out).
141
CREATE TABLE t1 (f1 DATE);
142
INSERT INTO t1 VALUES ('2007-07-19'), (NULL);
146
SELECT HOUR(CAST('2007-07-19' AS DATE)),
147
MINUTE(CAST('2007-07-19' AS DATE)),
148
SECOND(CAST('2007-07-19' AS DATE));
149
SELECT HOUR(CAST(NULL AS DATE)),
150
MINUTE(CAST(NULL AS DATE)),
151
SECOND(CAST(NULL AS DATE));
157
--echo End of 5.0 tests