case.result 7.66 KB
Newer Older
unknown's avatar
unknown committed
1 2
drop table if exists t1;
select CASE "b" when "a" then 1 when "b" then 2 END;
3 4
CASE "b" when "a" then 1 when "b" then 2 END
2
unknown's avatar
unknown committed
5
select CASE "c" when "a" then 1 when "b" then 2 END;
6 7
CASE "c" when "a" then 1 when "b" then 2 END
NULL
unknown's avatar
unknown committed
8
select CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END;
9 10
CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END
3
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
13
ok
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
16
ok
unknown's avatar
unknown committed
17
select CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end;
18 19
CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end
a
unknown's avatar
unknown committed
20
select CASE when 1=0 then "true" else "false" END;
21 22
CASE when 1=0 then "true" else "false" END
false
unknown's avatar
unknown committed
23
select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END;
24 25
CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END
one
26 27 28 29
explain extended select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
unknown's avatar
unknown committed
30
Note	1003	select (case 1 when 1 then _latin1'one' when 2 then _latin1'two' else _latin1'more' end) AS `CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END`
unknown's avatar
unknown committed
31
select CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END;
32 33
CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END
two
unknown's avatar
unknown committed
34
select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0;
35 36
(CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0
2
unknown's avatar
unknown committed
37
select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0;
38 39
(CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0
2.00
unknown's avatar
unknown committed
40
select case 1/0 when "a" then "true" else "false" END;
41 42
case 1/0 when "a" then "true" else "false" END
false
unknown's avatar
unknown committed
43
select case 1/0 when "a" then "true" END;
44 45
case 1/0 when "a" then "true" END
NULL
unknown's avatar
unknown committed
46
select (case 1/0 when "a" then "true" END) | 0;
47 48
(case 1/0 when "a" then "true" END) | 0
NULL
unknown's avatar
unknown committed
49
select (case 1/0 when "a" then "true" END) + 0.0;
50 51
(case 1/0 when "a" then "true" END) + 0.0
NULL
unknown's avatar
unknown committed
52
select case when 1>0 then "TRUE" else "FALSE" END;
53 54
case when 1>0 then "TRUE" else "FALSE" END
TRUE
unknown's avatar
unknown committed
55
select case when 1<0 then "TRUE" else "FALSE" END;
56 57
case when 1<0 then "TRUE" else "FALSE" END
FALSE
unknown's avatar
unknown committed
58 59 60
create table t1 (a int);
insert into t1 values(1),(2),(3),(4);
select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
61 62 63 64
fcase	count(*)
0	2
2	1
3	1
65 66 67 68
explain extended select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
Warnings:
unknown's avatar
unknown committed
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)
unknown's avatar
unknown committed
70
select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase;
71 72 73 74
fcase	count(*)
nothing	2
one	1
two	1
unknown's avatar
unknown committed
75
drop table t1;
unknown's avatar
unknown committed
76 77 78
create table t1 (row int not null, col int not null, val varchar(255) not null);
insert into t1 values (1,1,'orange'),(1,2,'large'),(2,1,'yellow'),(2,2,'medium'),(3,1,'green'),(3,2,'small');
select max(case col when 1 then val else null end) as color from t1 group by row;
unknown's avatar
unknown committed
79 80 81 82
color
orange
yellow
green
unknown's avatar
unknown committed
83
drop table t1;
84
SET NAMES latin1;
unknown's avatar
unknown committed
85 86 87 88 89 90 91 92
CREATE TABLE t1 SELECT 
CASE WHEN 1 THEN _latin1'a' COLLATE latin1_danish_ci ELSE _latin1'a' END AS c1,
CASE WHEN 1 THEN _latin1'a' ELSE _latin1'a' COLLATE latin1_danish_ci END AS c2,
CASE WHEN 1 THEN 'a' ELSE  1  END AS c3,
CASE WHEN 1 THEN  1  ELSE 'a' END AS c4,
CASE WHEN 1 THEN 'a' ELSE 1.0 END AS c5,
CASE WHEN 1 THEN 1.0 ELSE 'a' END AS c6,
CASE WHEN 1 THEN  1  ELSE 1.0 END AS c7,
unknown's avatar
unknown committed
93 94
CASE WHEN 1 THEN 1.0 ELSE  1  END AS c8,
CASE WHEN 1 THEN 1.0 END AS c9
unknown's avatar
unknown committed
95 96 97 98
;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
unknown's avatar
unknown committed
99 100
  `c1` char(1) character set latin1 collate latin1_danish_ci NOT NULL default '',
  `c2` char(1) character set latin1 collate latin1_danish_ci NOT NULL default '',
101 102 103 104
  `c3` binary(1) NOT NULL default '',
  `c4` binary(1) NOT NULL default '',
  `c5` binary(3) NOT NULL default '',
  `c6` binary(3) NOT NULL default '',
unknown's avatar
unknown committed
105 106 107
  `c7` double(3,1) NOT NULL default '0.0',
  `c8` double(3,1) NOT NULL default '0.0',
  `c9` double(3,1) default NULL
unknown's avatar
unknown committed
108
) ENGINE=MyISAM DEFAULT CHARSET=latin1
unknown's avatar
unknown committed
109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
DROP TABLE t1;
SELECT CASE 
WHEN 1 
THEN _latin1'a' COLLATE latin1_danish_ci 
ELSE _latin1'a' COLLATE latin1_swedish_ci
END;
ERROR HY000: Illegal mix of collations (latin1_danish_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'case'
SELECT CASE _latin1'a' COLLATE latin1_general_ci
WHEN _latin1'a' COLLATE latin1_danish_ci  THEN 1
WHEN _latin1'a' COLLATE latin1_swedish_ci THEN 2
END;
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_danish_ci,EXPLICIT), (latin1_swedish_ci,EXPLICIT) for operation 'case'
SELECT 
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
;
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
1	2	1	2
129 130 131 132 133 134 135 136
CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'coalesce'
CREATE TABLE t1 SELECT COALESCE('a' COLLATE latin1_swedish_ci,'b' COLLATE latin1_bin);
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'
CREATE TABLE t1 SELECT 
COALESCE(1), COALESCE(1.0),COALESCE('a'),
COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
COALESCE('a' COLLATE latin1_bin,'b');
137 138 139 140 141 142 143
explain extended SELECT 
COALESCE(1), COALESCE(1.0),COALESCE('a'),
COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
COALESCE('a' COLLATE latin1_bin,'b');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
unknown's avatar
unknown committed
144
Note	1003	select coalesce(1) AS `COALESCE(1)`,coalesce(1.0) AS `COALESCE(1.0)`,coalesce(_latin1'a') AS `COALESCE('a')`,coalesce(1,1.0) AS `COALESCE(1,1.0)`,coalesce(1,_latin1'1') AS `COALESCE(1,'1')`,coalesce(1.1,_latin1'1') AS `COALESCE(1.1,'1')`,coalesce((_latin1'a' collate _latin1'latin1_bin'),_latin1'b') AS `COALESCE('a' COLLATE latin1_bin,'b')`
145 146 147 148 149 150 151
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `COALESCE(1)` int(1) NOT NULL default '0',
  `COALESCE(1.0)` double(3,1) NOT NULL default '0.0',
  `COALESCE('a')` char(1) NOT NULL default '',
  `COALESCE(1,1.0)` double(3,1) NOT NULL default '0.0',
152 153
  `COALESCE(1,'1')` binary(1) NOT NULL default '',
  `COALESCE(1.1,'1')` binary(3) NOT NULL default '',
154
  `COALESCE('a' COLLATE latin1_bin,'b')` char(1) character set latin1 collate latin1_bin NOT NULL default ''
unknown's avatar
unknown committed
155
) ENGINE=MyISAM DEFAULT CHARSET=latin1
156
DROP TABLE t1;
157 158 159 160 161 162 163 164 165 166 167 168 169 170 171
SELECT 'case+union+test'
UNION 
SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;
case+union+test
case+union+test
nobug
SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;
CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END
nobug
SELECT 'case+union+test'
UNION 
SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END;
case+union+test
case+union+test
nobug