1
drop table if exists t1, t2;
2
select CASE "b" when "a" then 1 when "b" then 2 END;
3
CASE "b" when "a" then 1 when "b" then 2 END
5
select CASE "c" when "a" then 1 when "b" then 2 END;
6
CASE "c" when "a" then 1 when "b" then 2 END
8
select CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END;
9
CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END
11
select CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END;
12
CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END
14
select CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END;
15
CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END
17
select CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end;
18
CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end
20
select CASE when 1=0 then "true" else "false" END;
21
CASE when 1=0 then "true" else "false" END
23
select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END;
24
CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END
26
explain extended select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END;
27
id select_type table type possible_keys key key_len ref rows filtered Extra
28
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
30
Note 1003 select (case 1 when 1 then 'one' when 2 then 'two' else 'more' end) AS "CASE 1 when 1 then ""one"" WHEN 2 then ""two"" ELSE ""more"" END"
31
select CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END;
32
CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END
34
select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0;
35
(CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0
37
select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0;
38
(CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0
40
select case 1/0 when "a" then "true" else "false" END;
41
case 1/0 when "a" then "true" else "false" END
43
select case 1/0 when "a" then "true" END;
44
case 1/0 when "a" then "true" END
46
select (case 1/0 when "a" then "true" END) | 0;
47
(case 1/0 when "a" then "true" END) | 0
49
select (case 1/0 when "a" then "true" END) + 0.0;
50
(case 1/0 when "a" then "true" END) + 0.0
52
select case when 1>0 then "TRUE" else "FALSE" END;
53
case when 1>0 then "TRUE" else "FALSE" END
55
select case when 1<0 then "TRUE" else "FALSE" END;
56
case when 1<0 then "TRUE" else "FALSE" END
58
create table t1 (a int);
59
insert into t1 values(1),(2),(3),(4);
60
select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
65
explain extended select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
66
id select_type table type possible_keys key key_len ref rows filtered Extra
67
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
69
Note 1003 select (case "test"."t1"."a" when 1 then 2 when 2 then 3 else 0 end) AS "fcase",count(0) AS "count(*)" from "test"."t1" group by (case "test"."t1"."a" when 1 then 2 when 2 then 3 else 0 end)
70
select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase;
76
create table t1 (row int not null, col int not null, val varchar(255) not null);
77
insert into t1 values (1,1,'orange'),(1,2,'large'),(2,1,'yellow'),(2,2,'medium'),(3,1,'green'),(3,2,'small');
78
select max(case col when 1 then val else null end) as color from t1 group by row;
85
CREATE TABLE t1 SELECT
86
CASE WHEN 1 THEN _latin1'a' COLLATE latin1_danish_ci ELSE _latin1'a' END AS c1,
87
CASE WHEN 1 THEN _latin1'a' ELSE _latin1'a' COLLATE latin1_danish_ci END AS c2,
88
CASE WHEN 1 THEN 'a' ELSE 1 END AS c3,
89
CASE WHEN 1 THEN 1 ELSE 'a' END AS c4,
90
CASE WHEN 1 THEN 'a' ELSE 1.0 END AS c5,
91
CASE WHEN 1 THEN 1.0 ELSE 'a' END AS c6,
92
CASE WHEN 1 THEN 1 ELSE 1.0 END AS c7,
93
CASE WHEN 1 THEN 1.0 ELSE 1 END AS c8,
94
CASE WHEN 1 THEN 1.0 END AS c9,
95
CASE WHEN 1 THEN 0.1e1 else 0.1 END AS c10,
96
CASE WHEN 1 THEN 0.1e1 else 1 END AS c11,
97
CASE WHEN 1 THEN 0.1e1 else '1' END AS c12
101
t1 CREATE TABLE "t1" (
102
"c1" varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL,
103
"c2" varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL,
104
"c3" varbinary(1) NOT NULL,
105
"c4" varbinary(1) NOT NULL,
106
"c5" varbinary(4) NOT NULL,
107
"c6" varbinary(4) NOT NULL,
108
"c7" decimal(2,1) NOT NULL,
109
"c8" decimal(2,1) NOT NULL,
111
"c10" double NOT NULL,
112
"c11" double NOT NULL,
113
"c12" varbinary(5) NOT NULL
114
) ENGINE=MyISAM DEFAULT CHARSET=latin1
118
THEN _latin1'a' COLLATE latin1_danish_ci
119
ELSE _latin1'a' COLLATE latin1_swedish_ci
121
ERROR HY000: Illegal mix of collations (latin1_danish_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'case'
122
SELECT CASE _latin1'a' COLLATE latin1_general_ci
123
WHEN _latin1'a' COLLATE latin1_danish_ci THEN 1
124
WHEN _latin1'a' COLLATE latin1_swedish_ci THEN 2
126
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_danish_ci,EXPLICIT), (latin1_swedish_ci,EXPLICIT) for operation 'case'
128
CASE _latin1'a' COLLATE latin1_general_ci WHEN _latin1'A' THEN '1' ELSE 2 END,
129
CASE _latin1'a' COLLATE latin1_bin WHEN _latin1'A' THEN '1' ELSE 2 END,
130
CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_swedish_ci THEN '1' ELSE 2 END,
131
CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_bin THEN '1' ELSE 2 END
133
CASE _latin1'a' COLLATE latin1_general_ci WHEN _latin1'A' THEN '1' ELSE 2 END CASE _latin1'a' COLLATE latin1_bin WHEN _latin1'A' THEN '1' ELSE 2 END CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_swedish_ci THEN '1' ELSE 2 END CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_bin THEN '1' ELSE 2 END
135
CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a');
136
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'coalesce'
137
CREATE TABLE t1 SELECT COALESCE('a' COLLATE latin1_swedish_ci,'b' COLLATE latin1_bin);
138
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'
139
CREATE TABLE t1 SELECT
140
COALESCE(1), COALESCE(1.0),COALESCE('a'),
141
COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
142
COALESCE('a' COLLATE latin1_bin,'b');
143
explain extended SELECT
144
COALESCE(1), COALESCE(1.0),COALESCE('a'),
145
COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
146
COALESCE('a' COLLATE latin1_bin,'b');
147
id select_type table type possible_keys key key_len ref rows filtered Extra
148
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
150
Note 1003 select coalesce(1) AS "COALESCE(1)",coalesce(1.0) AS "COALESCE(1.0)",coalesce('a') AS "COALESCE('a')",coalesce(1,1.0) AS "COALESCE(1,1.0)",coalesce(1,'1') AS "COALESCE(1,'1')",coalesce(1.1,'1') AS "COALESCE(1.1,'1')",coalesce(('a' collate latin1_bin),'b') AS "COALESCE('a' COLLATE latin1_bin,'b')"
151
SHOW CREATE TABLE t1;
153
t1 CREATE TABLE "t1" (
154
"COALESCE(1)" int(1) NOT NULL,
155
"COALESCE(1.0)" decimal(2,1) NOT NULL,
156
"COALESCE('a')" varchar(1) NOT NULL,
157
"COALESCE(1,1.0)" decimal(2,1) NOT NULL,
158
"COALESCE(1,'1')" varbinary(1) NOT NULL,
159
"COALESCE(1.1,'1')" varbinary(4) NOT NULL,
160
"COALESCE('a' COLLATE latin1_bin,'b')" varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL
161
) ENGINE=MyISAM DEFAULT CHARSET=latin1
163
SELECT 'case+union+test'
165
SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;
169
SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;
170
CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END
172
SELECT 'case+union+test'
174
SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END;
178
create table t1(a float, b int default 3);
179
insert into t1 (a) values (2), (11), (8);
180
select min(a), min(case when 1=1 then a else NULL end),
181
min(case when 1!=1 then NULL else a end)
182
from t1 where b=3 group by b;
183
min(a) min(case when 1=1 then a else NULL end) min(case when 1!=1 then NULL else a end)
186
CREATE TABLE t1 (EMPNUM INT);
187
INSERT INTO t1 VALUES (0), (2);
188
CREATE TABLE t2 (EMPNUM DECIMAL (4, 2));
189
INSERT INTO t2 VALUES (0.0), (9.0);
190
SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM,
191
t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2
192
FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM;
193
CEMPNUM EMPMUM1 EMPNUM2
196
SELECT IFNULL(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM,
197
t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2
198
FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM;
199
CEMPNUM EMPMUM1 EMPNUM2
204
create table t1 (a int, b bigint unsigned);
205
create table t2 (c int);
206
insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997),
207
(3,11120436154190595086);
208
insert into t2 (c) values (1), (2), (3);
209
select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1
210
join t2 on t1.a=t2.c order by d;
212
1 4572794622775114594
213
3 11120436154190595086
214
2 18196094287899841997
215
select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1
216
join t2 on t1.a=t2.c where b=11120436154190595086 order by d;
218
3 11120436154190595086
added
removed
mysql-test/r/case.result
