diff options
Diffstat (limited to 'mysql-test/main/case.result')
| -rw-r--r-- | mysql-test/main/case.result | 577 |
1 files changed, 577 insertions, 0 deletions
diff --git a/mysql-test/main/case.result b/mysql-test/main/case.result new file mode 100644 index 00000000..fcabbc1a --- /dev/null +++ b/mysql-test/main/case.result @@ -0,0 +1,577 @@ +drop table if exists t1, t2; +select CASE "b" when "a" then 1 when "b" then 2 END as exp; +exp +2 +select CASE "c" when "a" then 1 when "b" then 2 END as exp; +exp +NULL +select CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END as exp; +exp +3 +select CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END as exp; +exp +ok +select CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END as exp; +exp +ok +select CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end as exp; +exp +a +select CASE when 1=0 then "true" else "false" END as exp; +exp +false +select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END as exp; +exp +one +explain extended select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END as exp; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select case 1 when 1 then 'one' when 2 then 'two' else 'more' end AS `exp` +select CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END as exp; +exp +two +select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0 as exp; +exp +2 +select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0 as exp; +exp +2.00 +select case 1/0 when "a" then "true" else "false" END as exp; +exp +false +Warnings: +Warning 1365 Division by 0 +select case 1/0 when "a" then "true" END as exp; +exp +NULL +Warnings: +Warning 1365 Division by 0 +select (case 1/0 when "a" then "true" END) | 0 as exp; +exp +NULL +Warnings: +Warning 1365 Division by 0 +select (case 1/0 when "a" then "true" END) + 0.0 as exp; +exp +NULL +Warnings: +Warning 1365 Division by 0 +select case when 1>0 then "TRUE" else "FALSE" END as exp; +exp +TRUE +select case when 1<0 then "TRUE" else "FALSE" END as exp; +exp +FALSE +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; +fcase count(*) +0 2 +2 1 +3 1 +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 filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort +Warnings: +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 2 +one 1 +two 1 +drop 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; +color +orange +yellow +green +drop 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 Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci DEFAULT NULL, + `c2` varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci DEFAULT NULL, + `c3` varchar(1) NOT NULL, + `c4` varchar(1) NOT NULL, + `c5` varchar(4) NOT NULL, + `c6` varchar(4) NOT NULL, + `c7` decimal(2,1) NOT NULL, + `c8` decimal(2,1) NOT NULL, + `c9` decimal(2,1) DEFAULT NULL, + `c10` double NOT NULL, + `c11` double NOT NULL, + `c12` varchar(5) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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 as e1, +CASE _latin1'a' COLLATE latin1_bin WHEN _latin1'A' THEN '1' ELSE 2 END as e2, +CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_swedish_ci THEN '1' ELSE 2 END as e3, +CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_bin THEN '1' ELSE 2 END as e4 +; +e1 e2 e3 e4 +1 2 1 2 +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'); +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 filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +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')` +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `COALESCE(1)` int(1) NOT NULL, + `COALESCE(1.0)` decimal(2,1) NOT NULL, + `COALESCE('a')` varchar(1) NOT NULL, + `COALESCE(1,1.0)` decimal(2,1) NOT NULL, + `COALESCE(1,'1')` varchar(1) NOT NULL, + `COALESCE(1.1,'1')` varchar(4) NOT NULL, + `COALESCE('a' COLLATE latin1_bin,'b')` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 SELECT IFNULL('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 'ifnull' +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 +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; +min(a) min(case when 1=1 then a else NULL end) min(case when 1!=1 then NULL else a end) +2 2 2 +drop table t1; +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; +CEMPNUM EMPMUM1 EMPNUM2 +0.00 0 0.00 +2.00 2 NULL +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; +CEMPNUM EMPMUM1 EMPNUM2 +0.00 0 0.00 +2.00 2 NULL +DROP TABLE t1,t2; +End of 4.1 tests +create table t1 (a int, b bigint unsigned); +create table t2 (c int); +insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997), +(3,11120436154190595086); +insert into t2 (c) values (1), (2), (3); +select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 +join t2 on t1.a=t2.c order by d; +a d +1 4572794622775114594 +3 11120436154190595086 +2 18196094287899841997 +select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 +join t2 on t1.a=t2.c where b=11120436154190595086 order by d; +a d +3 11120436154190595086 +drop table t1, t2; +End of 5.0 tests +# +# Bug#19875294 ASSERTION `SRC' FAILED IN MY_STRNXFRM_UNICODE +# (SIG 6 -STRINGS/CTYPE-UTF8.C:5151) +# +set @@sql_mode=''; +CREATE TABLE t1(c1 SET('','')CHARACTER SET ucs2); +Warnings: +Note 1291 Column 'c1' has duplicated value '' in SET +INSERT INTO t1 VALUES(990101.102); +Warnings: +Warning 1265 Data truncated for column 'c1' at row 1 +SELECT COALESCE(c1)FROM t1 ORDER BY 1; +COALESCE(c1) + +DROP TABLE t1; +set @@sql_mode=default; +CREATE TABLE t1(a YEAR); +SELECT 1 FROM t1 WHERE a=1 AND CASE 1 WHEN a THEN 1 ELSE 1 END; +1 +DROP TABLE t1; +create table t1 (f1 time); +insert t1 values ('00:00:00'),('00:01:00'); +select case t1.f1 when '00:00:00' then 1 end from t1; +case t1.f1 when '00:00:00' then 1 end +1 +NULL +drop table t1; +# +# MDEV-9745 Crash with CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 4 END +# +CREATE TABLE t1 SELECT CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 4 END AS a; +DESCRIBE t1; +Field Type Null Key Default Extra +a decimal(1,0) YES NULL +DROP TABLE t1; +CREATE TABLE t1 SELECT CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 40 END AS a; +DESCRIBE t1; +Field Type Null Key Default Extra +a decimal(2,0) YES NULL +DROP TABLE t1; +# +# Start of 10.1 test +# +# +# MDEV-8752 Wrong result for SELECT..WHERE CASE enum_field WHEN 1 THEN 1 ELSE 0 END AND a='5' +# +CREATE TABLE t1 (a ENUM('5','6') CHARACTER SET BINARY); +INSERT INTO t1 VALUES ('5'),('6'); +SELECT * FROM t1 WHERE a='5'; +a +5 +SELECT * FROM t1 WHERE a=1; +a +5 +SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END; +a +5 +SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END AND a='5'; +a +5 +# Multiple comparison types in CASE, not Ok to propagate +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END AND a='5'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '5' and case `test`.`t1`.`a` when 1 then 1 else 0 end +DROP TABLE t1; +CREATE TABLE t1 (a ENUM('a','b','100')); +INSERT INTO t1 VALUES ('a'),('b'),('100'); +SELECT * FROM t1 WHERE a='a'; +a +a +SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END; +a +a +SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a'; +a +a +# String comparison in CASE and in the equality, ok to propagate +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +SELECT * FROM t1 WHERE a=3; +a +100 +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END; +a +100 +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a=3; +a +100 +# Integer comparison in CASE and in the equality, not ok to propagate +# ENUM does not support this type of propagation yet. +# This can change in the future. See MDEV-8748. +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a=3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where case `test`.`t1`.`a` when 3 then 1 else 0 end and `test`.`t1`.`a` = 3 +SELECT * FROM t1 WHERE a=3; +a +100 +SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END; +a +100 +SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END AND a=3; +a +100 +# String comparison in CASE, integer comparison in the equality, not Ok to propagate +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END AND a=3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where case `test`.`t1`.`a` when '100' then 1 else 0 end and `test`.`t1`.`a` = 3 +SELECT * FROM t1 WHERE a='100'; +a +100 +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END; +a +100 +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a='100'; +a +100 +# Integer comparison in CASE, string comparison in the equality, not Ok to propagate +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a='100'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '100' and case `test`.`t1`.`a` when 3 then 1 else 0 end +SELECT * FROM t1 WHERE a='100'; +a +100 +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END; +a +100 +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a='100'; +a +100 +# Multiple type comparison in CASE, string comparison in the equality, not Ok to propagate +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a='100'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '100' and case `test`.`t1`.`a` when 3 then 1 when '100' then 1 else 0 end +SELECT * FROM t1 WHERE a=3; +a +100 +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END; +a +100 +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a=3; +a +100 +# Multiple type comparison in CASE, integer comparison in the equality, not Ok to propagate +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a=3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where case `test`.`t1`.`a` when 3 then 1 when '100' then 1 else 0 end and `test`.`t1`.`a` = 3 +DROP TABLE t1; +# +# End of MDEV-8752 +# +# +# End of 10.1 test +# +select case 'foo' when time'10:00:00' then 'never' when '0' then 'bug' else 'ok' end as exp; +exp +ok +Warnings: +Warning 1292 Truncated incorrect time value: 'foo' +select 'foo' in (time'10:00:00','0'); +'foo' in (time'10:00:00','0') +0 +Warnings: +Warning 1292 Truncated incorrect time value: 'foo' +create table t1 (a time); +insert t1 values (100000), (102030), (203040); +select case 'foo' when a then 'never' when '0' then 'bug' else 'ok' end from t1; +case 'foo' when a then 'never' when '0' then 'bug' else 'ok' end +ok +ok +ok +Warnings: +Warning 1292 Truncated incorrect time value: 'foo' +Warning 1292 Truncated incorrect time value: 'foo' +Warning 1292 Truncated incorrect time value: 'foo' +select 'foo' in (a,'0') from t1; +'foo' in (a,'0') +0 +0 +0 +Warnings: +Warning 1292 Truncated incorrect time value: 'foo' +Warning 1292 Truncated incorrect time value: 'foo' +Warning 1292 Truncated incorrect time value: 'foo' +drop table t1; +select case '20:10:05' when date'2020-10-10' then 'never' when time'20:10:5' then 'ok' else 'bug' end as exp; +exp +ok +# +# End of 10.2 test +# +# +# MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions +# +CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED); +INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615); +SELECT +CASE -1 +WHEN -9223372036854775808 THEN 'one' + WHEN 18446744073709551615 THEN 'two' + END AS c; +c +NULL +PREPARE stmt FROM "SELECT + CASE -1 + WHEN -9223372036854775808 THEN 'one' + WHEN 18446744073709551615 THEN 'two' + END AS c"; +EXECUTE stmt; +c +NULL +EXECUTE stmt; +c +NULL +DEALLOCATE PREPARE stmt; +DROP TABLE t1; +# +# MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result +# +SELECT +CASE TIME'10:20:30' + WHEN 102030 THEN 'one' + WHEN TIME'10:20:31' THEN 'two' + END AS good, +CASE TIME'10:20:30' + WHEN 102030 THEN 'one' + WHEN TIME'10:20:31' THEN 'two' + WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three' + END AS was_bad_now_good; +good was_bad_now_good +one one +PREPARE stmt FROM "SELECT + CASE TIME'10:20:30' + WHEN 102030 THEN 'one' + WHEN TIME'10:20:31' THEN 'two' + END AS good, + CASE TIME'10:20:30' + WHEN 102030 THEN 'one' + WHEN TIME'10:20:31' THEN 'two' + WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three' + END AS was_bad_now_good"; +EXECUTE stmt; +good was_bad_now_good +one one +EXECUTE stmt; +good was_bad_now_good +one one +DEALLOCATE PREPARE stmt; +# +# MDEV-13864 Change Item_func_case to store the predicant in args[0] +# +SET NAMES latin1; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('a'),('b'),('c'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' and case 'a' when 'a' then `test`.`t1`.`a` else 'a' end = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' and case 'a' when 'a' then 'a' else `test`.`t1`.`a` end = 'a' +ALTER TABLE t1 MODIFY a VARBINARY(10); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +DROP TABLE t1; +# +# MDEV-17411 Wrong WHERE optimization with simple CASE and searched CASE +# +CREATE TABLE t1 (a INT, b INT, KEY(a)); +INSERT INTO t1 VALUES (1,1),(2,2),(3,3); +SELECT * FROM t1 WHERE CASE a WHEN b THEN 1 END=1; +a b +1 1 +2 2 +3 3 +SELECT * FROM t1 WHERE CASE WHEN a THEN b ELSE 1 END=3; +a b +3 3 +SELECT * FROM t1 WHERE +CASE a WHEN b THEN 1 END=1 AND +CASE WHEN a THEN b ELSE 1 END=3; +a b +3 3 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE +CASE a WHEN b THEN 1 END=1 AND +CASE WHEN a THEN b ELSE 1 END=3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where case `test`.`t1`.`a` when `test`.`t1`.`b` then 1 end = 1 and case when `test`.`t1`.`a` then `test`.`t1`.`b` else 1 end = 3 +DROP TABLE t1; +# +# End of 10.3 test +# |