drop table if exists t1;select CASE "b" when "a" then 1 when "b" then 2 END;CASE "b" when "a" then 1 when "b" then 2 END2select CASE "c" when "a" then 1 when "b" then 2 END;CASE "c" when "a" then 1 when "b" then 2 ENDNULLselect CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END;CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END3select CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END;CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" ENDokselect CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END;CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" ENDokselect CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end;CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" endaselect CASE when 1=0 then "true" else "false" END;CASE when 1=0 then "true" else "false" ENDfalseselect CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END;CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" ENDoneexplain 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	Extra1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables usedWarnings: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`select CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END;CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" ENDtwoselect (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0;(CASE "two" when "one" then "1" WHEN "two" then "2" END) | 02select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0;(CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.02.00select case 1/0 when "a" then "true" else "false" END;case 1/0 when "a" then "true" else "false" ENDfalseselect case 1/0 when "a" then "true" END;case 1/0 when "a" then "true" ENDNULLselect (case 1/0 when "a" then "true" END) | 0;(case 1/0 when "a" then "true" END) | 0NULLselect (case 1/0 when "a" then "true" END) + 0.0;(case 1/0 when "a" then "true" END) + 0.0NULLselect case when 1>0 then "TRUE" else "FALSE" END;case when 1>0 then "TRUE" else "FALSE" ENDTRUEselect case when 1<0 then "TRUE" else "FALSE" END;case when 1<0 then "TRUE" else "FALSE" ENDFALSEcreate 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;fcase	count(*)0	22	13	1explain 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	Extra1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesortWarnings: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)select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase;fcase	count(*)nothing	2one	1two	1drop table t1;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;colororangeyellowgreendrop table t1;SET NAMES latin1;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,CASE WHEN 1 THEN 1.0 ELSE  1  END AS c8,CASE WHEN 1 THEN 1.0 END AS c9,CASE WHEN 1 THEN 0.1e1 else 0.1 END AS c10,CASE WHEN 1 THEN 0.1e1 else 1 END AS c11,CASE WHEN 1 THEN 0.1e1 else '1' END AS c12;SHOW CREATE TABLE t1;Table	Create Tablet1	CREATE TABLE `t1` (  `c1` varchar(1) character set latin1 collate latin1_danish_ci NOT NULL default '',  `c2` varchar(1) character set latin1 collate latin1_danish_ci NOT NULL default '',  `c3` varbinary(1) NOT NULL default '',  `c4` varbinary(1) NOT NULL default '',  `c5` varbinary(3) NOT NULL default '',  `c6` varbinary(3) NOT NULL default '',  `c7` decimal(2,1) NOT NULL default '0.0',  `c8` decimal(2,1) NOT NULL default '0.0',  `c9` decimal(2,1) default NULL,  `c10` double NOT NULL default '0',  `c11` double NOT NULL default '0',  `c12` varbinary(5) NOT NULL default '') ENGINE=MyISAM DEFAULT CHARSET=latin1DROP TABLE t1;SELECT CASE WHEN 1 THEN _latin1'a' COLLATE latin1_danish_ci ELSE _latin1'a' COLLATE latin1_swedish_ciEND;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_ciWHEN _latin1'a' COLLATE latin1_danish_ci  THEN 1WHEN _latin1'a' COLLATE latin1_swedish_ci THEN 2END;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 END1	2	1	2CREATE 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');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	Extra1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables usedWarnings: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_bin),_latin1'b') AS `COALESCE('a' COLLATE latin1_bin,'b')`SHOW CREATE TABLE t1;Table	Create Tablet1	CREATE TABLE `t1` (  `COALESCE(1)` int(1) NOT NULL default '0',  `COALESCE(1.0)` decimal(2,1) unsigned NOT NULL default '0.0',  `COALESCE('a')` varchar(1) NOT NULL default '',  `COALESCE(1,1.0)` decimal(2,1) NOT NULL default '0.0',  `COALESCE(1,'1')` varbinary(1) NOT NULL default '',  `COALESCE(1.1,'1')` varbinary(3) NOT NULL default '',  `COALESCE('a' COLLATE latin1_bin,'b')` varchar(1) character set latin1 collate latin1_bin NOT NULL default '') ENGINE=MyISAM DEFAULT CHARSET=latin1DROP TABLE t1;SELECT 'case+union+test'UNION SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;case+union+testcase+union+testnobugSELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' ENDnobugSELECT 'case+union+test'UNION SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END;case+union+testcase+union+testnobugCREATE TABLE t1 (EMPNUM INT);INSERT INTO t1 VALUES (0), (2);CREATE TABLE t2 (EMPNUM DECIMAL (4, 2));INSERT INTO t2 VALUES (0.0), (9.0);SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM,t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM;CEMPNUM	EMPMUM1	EMPNUM20.00	0	0.002.00	2	NULLSELECT IFNULL(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM,t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM;CEMPNUM	EMPMUM1	EMPNUM20.00	0	0.002.00	2	NULLDROP TABLE t1,t2;