case.test
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:4k
- #
- # 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<0 then "TRUE" else "FALSE" END;
- #
- # 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
- ;
- 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;
- # End of 4.1 tests