# # Testing of CASE # --disable_warnings drop table if exists t1; --enable_warnings select CASE "b" when "a" then 1 when "b" then 2 END; select CASE "c" when "a" then 1 when "b" then 2 END; select CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END; select CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END; select CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END; select CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end; select CASE when 1=0 then "true" else "false" END; select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END; explain extended select 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; select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0; select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0; select case 1/0 when "a" then "true" else "false" END; select case 1/0 when "a" then "true" END; select (case 1/0 when "a" then "true" END) | 0; select (case 1/0 when "a" then "true" END) + 0.0; select case when 1>0 then "TRUE" else "FALSE" END; select case when 1 # # Test bug when using GROUP BY on CASE # 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; explain extended select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase; select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase; drop table t1; # # Test MAX(CASE ... ) that can return null # 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; drop table t1; SET NAMES latin1; # # CASE and argument types/collations aggregation into result # 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; DROP TABLE t1; --error 1267 SELECT CASE WHEN 1 THEN _latin1'a' COLLATE latin1_danish_ci ELSE _latin1'a' COLLATE latin1_swedish_ci END; --error 1270 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; 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 ; # # COALESCE is a CASE abbrevation: # # COALESCE(v1,v2) == CASE WHEN v1 IS NOT NULL THEN v1 ELSE v2 END # # COALESCE(V1, V2, . . . ,Vn ) = # CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,Vn) END # # Check COALESCE argument types aggregation --error 1267 CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a'); --error 1267 CREATE TABLE t1 SELECT COALESCE('a' COLLATE latin1_swedish_ci,'b' COLLATE latin1_bin); 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'); SHOW CREATE TABLE t1; DROP TABLE t1; # Test for BUG#10151 SELECT 'case+union+test' UNION SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END; SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END; SELECT 'case+union+test' UNION SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END; # # Bug #17896: problem with MIN(CASE...) # create table t1(a float, b int default 3); insert into t1 (a) values (2), (11), (8); select min(a), min(case when 1=1 then a else NULL end), min(case when 1!=1 then NULL else a end) from t1 where b=3 group by b; drop table t1; # # Tests for bug #9939: conversion of the arguments for COALESCE and IFNULL # CREATE 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 EMPNUM2 FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM; SELECT IFNULL(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM, t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2 FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM; DROP TABLE t1,t2; # End of 4.1 tests
