drop table if exists t1, t2; select CASE "b" when "a" then 1 when "b" then 2 END; CASE "b" when "a" then 1 when "b" then 2 END 2 select CASE "c" when "a" then 1 when "b" then 2 END; CASE "c" when "a" then 1 when "b" then 2 END NULL select 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 END 3 select 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" END ok select 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" END ok select 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" end a select CASE when 1=0 then "true" else "false" END; CASE when 1=0 then "true" else "false" END false select 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" END one 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 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 `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" END two select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0; (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0 2 select (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.0 2.00 select case 1/0 when "a" then "true" else "false" END; case 1/0 when "a" then "true" else "false" END false Warnings: Warning 1365 Division by 0 select case 1/0 when "a" then "true" END; case 1/0 when "a" then "true" END NULL Warnings: Warning 1365 Division by 0 select (case 1/0 when "a" then "true" END) | 0; (case 1/0 when "a" then "true" END) | 0 NULL Warnings: Warning 1365 Division by 0 select (case 1/0 when "a" then "true" END) + 0.0; (case 1/0 when "a" then "true" END) + 0.0 NULL Warnings: Warning 1365 Division by 0 select case when 1>0 then "TRUE" else "FALSE" END; case when 1>0 then "TRUE" else "FALSE" END TRUE select case when 1<0 then "TRUE" else "FALSE" END; case when 1<0 then "TRUE" else "FALSE" END 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 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 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 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; case 'foo' when time'10:00:00' then 'never' when '0' then 'bug' else 'ok' end 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; case '20:10:05' when date'2020-10-10' then 'never' when time'20:10:5' then 'ok' else 'bug' end 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; # # End of 10.3 test #