|
1
by brian
clean slate |
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
|
|
4 |
2
|
|
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
|
|
7 |
NULL
|
|
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
|
|
10 |
3
|
|
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
|
|
13 |
ok
|
|
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
|
|
16 |
ok
|
|
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
|
|
19 |
a
|
|
20 |
select CASE when 1=0 then "true" else "false" END;
|
|
21 |
CASE when 1=0 then "true" else "false" END
|
|
22 |
false
|
|
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
|
|
25 |
one
|
|
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
|
|
29 |
Warnings:
|
|
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
|
|
33 |
two
|
|
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
|
|
36 |
2
|
|
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
|
|
39 |
2.00
|
|
40 |
select case 1/0 when "a" then "true" else "false" END;
|
|
41 |
case 1/0 when "a" then "true" else "false" END
|
|
42 |
false
|
|
43 |
select case 1/0 when "a" then "true" END;
|
|
44 |
case 1/0 when "a" then "true" END
|
|
45 |
NULL
|
|
46 |
select (case 1/0 when "a" then "true" END) | 0;
|
|
47 |
(case 1/0 when "a" then "true" END) | 0
|
|
48 |
NULL
|
|
49 |
select (case 1/0 when "a" then "true" END) + 0.0;
|
|
50 |
(case 1/0 when "a" then "true" END) + 0.0
|
|
51 |
NULL
|
|
52 |
select case when 1>0 then "TRUE" else "FALSE" END;
|
|
53 |
case when 1>0 then "TRUE" else "FALSE" END
|
|
54 |
TRUE
|
|
55 |
select case when 1<0 then "TRUE" else "FALSE" END;
|
|
56 |
case when 1<0 then "TRUE" else "FALSE" END
|
|
57 |
FALSE
|
|
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;
|
|
61 |
fcase count(*)
|
|
62 |
0 2
|
|
63 |
2 1
|
|
64 |
3 1
|
|
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
|
|
68 |
Warnings:
|
|
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;
|
|
71 |
fcase count(*)
|
|
72 |
nothing 2
|
|
73 |
one 1
|
|
74 |
two 1
|
|
75 |
drop table t1;
|
|
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;
|
|
79 |
color
|
|
80 |
orange
|
|
81 |
yellow
|
|
82 |
green
|
|
83 |
drop table t1;
|
|
84 |
SET NAMES latin1;
|
|
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
|
|
98 |
;
|
|
99 |
SHOW CREATE TABLE t1;
|
|
100 |
Table Create Table
|
|
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,
|
|
110 |
"c9" decimal(2,1),
|
|
111 |
"c10" double NOT NULL,
|
|
112 |
"c11" double NOT NULL,
|
|
113 |
"c12" varbinary(5) NOT NULL
|
|
114 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1
|
|
115 |
DROP TABLE t1;
|
|
116 |
SELECT CASE
|
|
117 |
WHEN 1
|
|
118 |
THEN _latin1'a' COLLATE latin1_danish_ci
|
|
119 |
ELSE _latin1'a' COLLATE latin1_swedish_ci
|
|
120 |
END;
|
|
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
|
|
125 |
END;
|
|
126 |
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_danish_ci,EXPLICIT), (latin1_swedish_ci,EXPLICIT) for operation 'case'
|
|
127 |
SELECT
|
|
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
|
|
132 |
;
|
|
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
|
|
134 |
1 2 1 2
|
|
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
|
|
149 |
Warnings:
|
|
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;
|
|
152 |
Table Create Table
|
|
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
|
|
162 |
DROP TABLE t1;
|
|
163 |
SELECT 'case+union+test'
|
|
164 |
UNION
|
|
165 |
SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;
|
|
166 |
case+union+test
|
|
167 |
case+union+test
|
|
168 |
nobug
|
|
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
|
|
171 |
nobug
|
|
172 |
SELECT 'case+union+test'
|
|
173 |
UNION
|
|
174 |
SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END;
|
|
175 |
case+union+test
|
|
176 |
case+union+test
|
|
177 |
nobug
|
|
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)
|
|
184 |
2 2 2
|
|
185 |
drop table t1;
|
|
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
|
|
194 |
0.00 0 0.00
|
|
195 |
2.00 2 NULL
|
|
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
|
|
200 |
0.00 0 0.00
|
|
201 |
2.00 2 NULL
|
|
202 |
DROP TABLE t1,t2;
|
|
203 |
End of 4.1 tests
|
|
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;
|
|
211 |
a 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;
|
|
217 |
a d
|
|
218 |
3 11120436154190595086
|
|
219 |
drop table t1, t2;
|
|
220 |
End of 5.0 tests
|