1drop table if exists t1, t2; 2select CASE "b" when "a" then 1 when "b" then 2 END; 3CASE "b" when "a" then 1 when "b" then 2 END 42 5select CASE "c" when "a" then 1 when "b" then 2 END; 6CASE "c" when "a" then 1 when "b" then 2 END 7NULL 8select CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END; 9CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END 103 11select CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END; 12CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END 13ok 14select CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END; 15CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END 16ok 17select CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end; 18CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end 19a 20select CASE when 1=0 then "true" else "false" END; 21CASE when 1=0 then "true" else "false" END 22false 23select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END; 24CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END 25one 26explain extended select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END; 27id select_type table type possible_keys key key_len ref rows filtered Extra 281 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 29Warnings: 30Note 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` 31select CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END; 32CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END 33two 34select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0; 35(CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0 362 37select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0; 38(CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0 392.00 40select case 1/0 when "a" then "true" else "false" END; 41case 1/0 when "a" then "true" else "false" END 42false 43Warnings: 44Warning 1365 Division by 0 45select case 1/0 when "a" then "true" END; 46case 1/0 when "a" then "true" END 47NULL 48Warnings: 49Warning 1365 Division by 0 50select (case 1/0 when "a" then "true" END) | 0; 51(case 1/0 when "a" then "true" END) | 0 52NULL 53Warnings: 54Warning 1365 Division by 0 55select (case 1/0 when "a" then "true" END) + 0.0; 56(case 1/0 when "a" then "true" END) + 0.0 57NULL 58Warnings: 59Warning 1365 Division by 0 60select case when 1>0 then "TRUE" else "FALSE" END; 61case when 1>0 then "TRUE" else "FALSE" END 62TRUE 63select case when 1<0 then "TRUE" else "FALSE" END; 64case when 1<0 then "TRUE" else "FALSE" END 65FALSE 66create table t1 (a int); 67insert into t1 values(1),(2),(3),(4); 68select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase; 69fcase count(*) 700 2 712 1 723 1 73explain extended select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase; 74id select_type table type possible_keys key key_len ref rows filtered Extra 751 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort 76Warnings: 77Note 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 78select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase; 79fcase count(*) 80nothing 2 81one 1 82two 1 83drop table t1; 84create table t1 (row int not null, col int not null, val varchar(255) not null); 85insert into t1 values (1,1,'orange'),(1,2,'large'),(2,1,'yellow'),(2,2,'medium'),(3,1,'green'),(3,2,'small'); 86select max(case col when 1 then val else null end) as color from t1 group by row; 87color 88orange 89yellow 90green 91drop table t1; 92SET NAMES latin1; 93CREATE TABLE t1 SELECT 94CASE WHEN 1 THEN _latin1'a' COLLATE latin1_danish_ci ELSE _latin1'a' END AS c1, 95CASE WHEN 1 THEN _latin1'a' ELSE _latin1'a' COLLATE latin1_danish_ci END AS c2, 96CASE WHEN 1 THEN 'a' ELSE 1 END AS c3, 97CASE WHEN 1 THEN 1 ELSE 'a' END AS c4, 98CASE WHEN 1 THEN 'a' ELSE 1.0 END AS c5, 99CASE WHEN 1 THEN 1.0 ELSE 'a' END AS c6, 100CASE WHEN 1 THEN 1 ELSE 1.0 END AS c7, 101CASE WHEN 1 THEN 1.0 ELSE 1 END AS c8, 102CASE WHEN 1 THEN 1.0 END AS c9, 103CASE WHEN 1 THEN 0.1e1 else 0.1 END AS c10, 104CASE WHEN 1 THEN 0.1e1 else 1 END AS c11, 105CASE WHEN 1 THEN 0.1e1 else '1' END AS c12 106; 107SHOW CREATE TABLE t1; 108Table Create Table 109t1 CREATE TABLE `t1` ( 110 `c1` varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci DEFAULT NULL, 111 `c2` varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci DEFAULT NULL, 112 `c3` varchar(1) NOT NULL, 113 `c4` varchar(1) NOT NULL, 114 `c5` varchar(4) NOT NULL, 115 `c6` varchar(4) NOT NULL, 116 `c7` decimal(2,1) NOT NULL, 117 `c8` decimal(2,1) NOT NULL, 118 `c9` decimal(2,1) DEFAULT NULL, 119 `c10` double NOT NULL, 120 `c11` double NOT NULL, 121 `c12` varchar(5) NOT NULL 122) ENGINE=MyISAM DEFAULT CHARSET=latin1 123DROP TABLE t1; 124SELECT CASE 125WHEN 1 126THEN _latin1'a' COLLATE latin1_danish_ci 127ELSE _latin1'a' COLLATE latin1_swedish_ci 128END; 129ERROR HY000: Illegal mix of collations (latin1_danish_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'case' 130SELECT CASE _latin1'a' COLLATE latin1_general_ci 131WHEN _latin1'a' COLLATE latin1_danish_ci THEN 1 132WHEN _latin1'a' COLLATE latin1_swedish_ci THEN 2 133END; 134ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_danish_ci,EXPLICIT), (latin1_swedish_ci,EXPLICIT) for operation 'case' 135SELECT 136CASE _latin1'a' COLLATE latin1_general_ci WHEN _latin1'A' THEN '1' ELSE 2 END, 137CASE _latin1'a' COLLATE latin1_bin WHEN _latin1'A' THEN '1' ELSE 2 END, 138CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_swedish_ci THEN '1' ELSE 2 END, 139CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_bin THEN '1' ELSE 2 END 140; 141CASE _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 1421 2 1 2 143CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a'); 144ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'coalesce' 145CREATE TABLE t1 SELECT COALESCE('a' COLLATE latin1_swedish_ci,'b' COLLATE latin1_bin); 146ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce' 147CREATE TABLE t1 SELECT 148COALESCE(1), COALESCE(1.0),COALESCE('a'), 149COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'), 150COALESCE('a' COLLATE latin1_bin,'b'); 151explain extended SELECT 152COALESCE(1), COALESCE(1.0),COALESCE('a'), 153COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'), 154COALESCE('a' COLLATE latin1_bin,'b'); 155id select_type table type possible_keys key key_len ref rows filtered Extra 1561 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 157Warnings: 158Note 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')` 159SHOW CREATE TABLE t1; 160Table Create Table 161t1 CREATE TABLE `t1` ( 162 `COALESCE(1)` int(1) NOT NULL, 163 `COALESCE(1.0)` decimal(2,1) NOT NULL, 164 `COALESCE('a')` varchar(1) NOT NULL, 165 `COALESCE(1,1.0)` decimal(2,1) NOT NULL, 166 `COALESCE(1,'1')` varchar(1) NOT NULL, 167 `COALESCE(1.1,'1')` varchar(4) NOT NULL, 168 `COALESCE('a' COLLATE latin1_bin,'b')` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL 169) ENGINE=MyISAM DEFAULT CHARSET=latin1 170DROP TABLE t1; 171CREATE TABLE t1 SELECT IFNULL('a' COLLATE latin1_swedish_ci, 'b' COLLATE latin1_bin); 172ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'ifnull' 173SELECT 'case+union+test' 174UNION 175SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END; 176case+union+test 177case+union+test 178nobug 179SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END; 180CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END 181nobug 182SELECT 'case+union+test' 183UNION 184SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END; 185case+union+test 186case+union+test 187nobug 188create table t1(a float, b int default 3); 189insert into t1 (a) values (2), (11), (8); 190select min(a), min(case when 1=1 then a else NULL end), 191min(case when 1!=1 then NULL else a end) 192from t1 where b=3 group by b; 193min(a) min(case when 1=1 then a else NULL end) min(case when 1!=1 then NULL else a end) 1942 2 2 195drop table t1; 196CREATE TABLE t1 (EMPNUM INT); 197INSERT INTO t1 VALUES (0), (2); 198CREATE TABLE t2 (EMPNUM DECIMAL (4, 2)); 199INSERT INTO t2 VALUES (0.0), (9.0); 200SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM, 201t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2 202FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM; 203CEMPNUM EMPMUM1 EMPNUM2 2040.00 0 0.00 2052.00 2 NULL 206SELECT IFNULL(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM, 207t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2 208FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM; 209CEMPNUM EMPMUM1 EMPNUM2 2100.00 0 0.00 2112.00 2 NULL 212DROP TABLE t1,t2; 213End of 4.1 tests 214create table t1 (a int, b bigint unsigned); 215create table t2 (c int); 216insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997), 217(3,11120436154190595086); 218insert into t2 (c) values (1), (2), (3); 219select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 220join t2 on t1.a=t2.c order by d; 221a d 2221 4572794622775114594 2233 11120436154190595086 2242 18196094287899841997 225select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 226join t2 on t1.a=t2.c where b=11120436154190595086 order by d; 227a d 2283 11120436154190595086 229drop table t1, t2; 230End of 5.0 tests 231# 232# Bug#19875294 ASSERTION `SRC' FAILED IN MY_STRNXFRM_UNICODE 233# (SIG 6 -STRINGS/CTYPE-UTF8.C:5151) 234# 235set @@sql_mode=''; 236CREATE TABLE t1(c1 SET('','')CHARACTER SET ucs2); 237Warnings: 238Note 1291 Column 'c1' has duplicated value '' in SET 239INSERT INTO t1 VALUES(990101.102); 240Warnings: 241Warning 1265 Data truncated for column 'c1' at row 1 242SELECT COALESCE(c1)FROM t1 ORDER BY 1; 243COALESCE(c1) 244 245DROP TABLE t1; 246set @@sql_mode=default; 247CREATE TABLE t1(a YEAR); 248SELECT 1 FROM t1 WHERE a=1 AND CASE 1 WHEN a THEN 1 ELSE 1 END; 2491 250DROP TABLE t1; 251create table t1 (f1 time); 252insert t1 values ('00:00:00'),('00:01:00'); 253select case t1.f1 when '00:00:00' then 1 end from t1; 254case t1.f1 when '00:00:00' then 1 end 2551 256NULL 257drop table t1; 258# 259# MDEV-9745 Crash with CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 4 END 260# 261CREATE TABLE t1 SELECT CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 4 END AS a; 262DESCRIBE t1; 263Field Type Null Key Default Extra 264a decimal(1,0) YES NULL 265DROP TABLE t1; 266CREATE TABLE t1 SELECT CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 40 END AS a; 267DESCRIBE t1; 268Field Type Null Key Default Extra 269a decimal(2,0) YES NULL 270DROP TABLE t1; 271# 272# Start of 10.1 test 273# 274# 275# MDEV-8752 Wrong result for SELECT..WHERE CASE enum_field WHEN 1 THEN 1 ELSE 0 END AND a='5' 276# 277CREATE TABLE t1 (a ENUM('5','6') CHARACTER SET BINARY); 278INSERT INTO t1 VALUES ('5'),('6'); 279SELECT * FROM t1 WHERE a='5'; 280a 2815 282SELECT * FROM t1 WHERE a=1; 283a 2845 285SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END; 286a 2875 288SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END AND a='5'; 289a 2905 291# Multiple comparison types in CASE, not Ok to propagate 292EXPLAIN EXTENDED 293SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END AND a='5'; 294id select_type table type possible_keys key key_len ref rows filtered Extra 2951 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 296Warnings: 297Note 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 298DROP TABLE t1; 299CREATE TABLE t1 (a ENUM('a','b','100')); 300INSERT INTO t1 VALUES ('a'),('b'),('100'); 301SELECT * FROM t1 WHERE a='a'; 302a 303a 304SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END; 305a 306a 307SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a'; 308a 309a 310# String comparison in CASE and in the equality, ok to propagate 311EXPLAIN EXTENDED 312SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a'; 313id select_type table type possible_keys key key_len ref rows filtered Extra 3141 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 315Warnings: 316Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' 317SELECT * FROM t1 WHERE a=3; 318a 319100 320SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END; 321a 322100 323SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a=3; 324a 325100 326# Integer comparison in CASE and in the equality, not ok to propagate 327# ENUM does not support this type of propagation yet. 328# This can change in the future. See MDEV-8748. 329EXPLAIN EXTENDED 330SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a=3; 331id select_type table type possible_keys key key_len ref rows filtered Extra 3321 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 333Warnings: 334Note 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 335SELECT * FROM t1 WHERE a=3; 336a 337100 338SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END; 339a 340100 341SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END AND a=3; 342a 343100 344# String comparison in CASE, integer comparison in the equality, not Ok to propagate 345EXPLAIN EXTENDED 346SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END AND a=3; 347id select_type table type possible_keys key key_len ref rows filtered Extra 3481 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 349Warnings: 350Note 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 351SELECT * FROM t1 WHERE a='100'; 352a 353100 354SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END; 355a 356100 357SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a='100'; 358a 359100 360# Integer comparison in CASE, string comparison in the equality, not Ok to propagate 361EXPLAIN EXTENDED 362SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a='100'; 363id select_type table type possible_keys key key_len ref rows filtered Extra 3641 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 365Warnings: 366Note 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 367SELECT * FROM t1 WHERE a='100'; 368a 369100 370SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END; 371a 372100 373SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a='100'; 374a 375100 376# Multiple type comparison in CASE, string comparison in the equality, not Ok to propagate 377EXPLAIN EXTENDED 378SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a='100'; 379id select_type table type possible_keys key key_len ref rows filtered Extra 3801 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 381Warnings: 382Note 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 383SELECT * FROM t1 WHERE a=3; 384a 385100 386SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END; 387a 388100 389SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a=3; 390a 391100 392# Multiple type comparison in CASE, integer comparison in the equality, not Ok to propagate 393EXPLAIN EXTENDED 394SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a=3; 395id select_type table type possible_keys key key_len ref rows filtered Extra 3961 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 397Warnings: 398Note 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 399DROP TABLE t1; 400# 401# End of MDEV-8752 402# 403# 404# End of 10.1 test 405# 406select case 'foo' when time'10:00:00' then 'never' when '0' then 'bug' else 'ok' end; 407case 'foo' when time'10:00:00' then 'never' when '0' then 'bug' else 'ok' end 408ok 409Warnings: 410Warning 1292 Truncated incorrect time value: 'foo' 411select 'foo' in (time'10:00:00','0'); 412'foo' in (time'10:00:00','0') 4130 414Warnings: 415Warning 1292 Truncated incorrect time value: 'foo' 416create table t1 (a time); 417insert t1 values (100000), (102030), (203040); 418select case 'foo' when a then 'never' when '0' then 'bug' else 'ok' end from t1; 419case 'foo' when a then 'never' when '0' then 'bug' else 'ok' end 420ok 421ok 422ok 423Warnings: 424Warning 1292 Truncated incorrect time value: 'foo' 425Warning 1292 Truncated incorrect time value: 'foo' 426Warning 1292 Truncated incorrect time value: 'foo' 427select 'foo' in (a,'0') from t1; 428'foo' in (a,'0') 4290 4300 4310 432Warnings: 433Warning 1292 Truncated incorrect time value: 'foo' 434Warning 1292 Truncated incorrect time value: 'foo' 435Warning 1292 Truncated incorrect time value: 'foo' 436drop table t1; 437select case '20:10:05' when date'2020-10-10' then 'never' when time'20:10:5' then 'ok' else 'bug' end; 438case '20:10:05' when date'2020-10-10' then 'never' when time'20:10:5' then 'ok' else 'bug' end 439ok 440# 441# End of 10.2 test 442# 443# 444# MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions 445# 446CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED); 447INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615); 448SELECT 449CASE -1 450WHEN -9223372036854775808 THEN 'one' 451 WHEN 18446744073709551615 THEN 'two' 452 END AS c; 453c 454NULL 455PREPARE stmt FROM "SELECT 456 CASE -1 457 WHEN -9223372036854775808 THEN 'one' 458 WHEN 18446744073709551615 THEN 'two' 459 END AS c"; 460EXECUTE stmt; 461c 462NULL 463EXECUTE stmt; 464c 465NULL 466DEALLOCATE PREPARE stmt; 467DROP TABLE t1; 468# 469# MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result 470# 471SELECT 472CASE TIME'10:20:30' 473 WHEN 102030 THEN 'one' 474 WHEN TIME'10:20:31' THEN 'two' 475 END AS good, 476CASE TIME'10:20:30' 477 WHEN 102030 THEN 'one' 478 WHEN TIME'10:20:31' THEN 'two' 479 WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three' 480 END AS was_bad_now_good; 481good was_bad_now_good 482one one 483PREPARE stmt FROM "SELECT 484 CASE TIME'10:20:30' 485 WHEN 102030 THEN 'one' 486 WHEN TIME'10:20:31' THEN 'two' 487 END AS good, 488 CASE TIME'10:20:30' 489 WHEN 102030 THEN 'one' 490 WHEN TIME'10:20:31' THEN 'two' 491 WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three' 492 END AS was_bad_now_good"; 493EXECUTE stmt; 494good was_bad_now_good 495one one 496EXECUTE stmt; 497good was_bad_now_good 498one one 499DEALLOCATE PREPARE stmt; 500# 501# MDEV-13864 Change Item_func_case to store the predicant in args[0] 502# 503SET NAMES latin1; 504CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); 505INSERT INTO t1 VALUES ('a'),('b'),('c'); 506EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a'; 507id select_type table type possible_keys key key_len ref rows filtered Extra 5081 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 509Warnings: 510Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' 511EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a'; 512id select_type table type possible_keys key key_len ref rows filtered Extra 5131 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 514Warnings: 515Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' 516EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a'; 517id select_type table type possible_keys key key_len ref rows filtered Extra 5181 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 519Warnings: 520Note 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' 521EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a'; 522id select_type table type possible_keys key key_len ref rows filtered Extra 5231 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 524Warnings: 525Note 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' 526ALTER TABLE t1 MODIFY a VARBINARY(10); 527EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a'; 528id select_type table type possible_keys key key_len ref rows filtered Extra 5291 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 530Warnings: 531Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' 532EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a'; 533id select_type table type possible_keys key key_len ref rows filtered Extra 5341 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 535Warnings: 536Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' 537EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a'; 538id select_type table type possible_keys key key_len ref rows filtered Extra 5391 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 540Warnings: 541Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' 542EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a'; 543id select_type table type possible_keys key key_len ref rows filtered Extra 5441 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 545Warnings: 546Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' 547DROP TABLE t1; 548# 549# MDEV-17411 Wrong WHERE optimization with simple CASE and searched CASE 550# 551CREATE TABLE t1 (a INT, b INT, KEY(a)); 552INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 553SELECT * FROM t1 WHERE CASE a WHEN b THEN 1 END=1; 554a b 5551 1 5562 2 5573 3 558SELECT * FROM t1 WHERE CASE WHEN a THEN b ELSE 1 END=3; 559a b 5603 3 561SELECT * FROM t1 WHERE 562CASE a WHEN b THEN 1 END=1 AND 563CASE WHEN a THEN b ELSE 1 END=3; 564a b 5653 3 566EXPLAIN EXTENDED 567SELECT * FROM t1 WHERE 568CASE a WHEN b THEN 1 END=1 AND 569CASE WHEN a THEN b ELSE 1 END=3; 570id select_type table type possible_keys key key_len ref rows filtered Extra 5711 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 572Warnings: 573Note 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 574DROP TABLE t1; 575# 576# End of 10.3 test 577# 578