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) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `c4` varchar(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `c5` varchar(4) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `c6` varchar(4) CHARACTER SET latin1 COLLATE latin1_swedish_ci 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) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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(2) NOT NULL,
  `COALESCE(1.0)` decimal(2,1) NOT NULL,
  `COALESCE('a')` varchar(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `COALESCE(1,1.0)` decimal(2,1) NOT NULL,
  `COALESCE(1,'1')` varchar(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `COALESCE(1.1,'1')` varchar(4) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `COALESCE('a' COLLATE latin1_bin,'b')` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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')) CHARSET=latin1;
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
#
# MDEV-25415 CASE function handles NULL inconsistently
#
select case 'X' when null then 1 when 'X' then 2 else 3 end;
case 'X' when null then 1 when 'X' then 2 else 3 end
2
select case 'X' when 1/1 then 1 when 'X' then 2 else 3 end;
case 'X' when 1/1 then 1 when 'X' then 2 else 3 end
2
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'X'
select case 'X' when 1/0 then 1 when 'X' then 2 else 3 end;
case 'X' when 1/0 then 1 when 'X' then 2 else 3 end
2
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'X'
Warning 1365 Division by 0
#
# MDEV-23278: Incorrect Calculation with AVG() Function
#
create table avg_calc_test (
id int(11) not null auto_increment,
name varchar(255) default null,
primary key (id)
) auto_increment=1 default charset=latin1;
insert into avg_calc_test (name) values ('orange'), ('blue'), ('black'), ('orange'), ('black');
select * from (
select
avg(case when name = 'orange' then 3 when name = 'blue' then 2 when name = 'black' then 1 else 0 end) as metric_val
from avg_calc_test
) t;
metric_val
2.0000
create table t1 as
select
avg(case when name = 'orange' then 3 when name = 'blue' then 2 when name = 'black' then 1 else 0 end) as metric_val
from avg_calc_test;
select * from t1;
metric_val
2.0000
create or replace table t1 as
select
avg(least(1,2)) as metric_val
from avg_calc_test;
select * from t1;
metric_val
1.0000
create or replace table t1 as
select avg(coalesce(1,2)) as metric_val from avg_calc_test;
select * from t1;
metric_val
1.0000
drop table avg_calc_test, t1;
# End of 10.11 test
#
# MDEV-36716 A case expression with ROW arguments in THEN crashes
#
SELECT CASE WHEN TRUE THEN ROW(1,2) ELSE ROW(2,1) END;
ERROR HY000: Illegal parameter data types row and row for operation 'case'
SELECT CASE WHEN TRUE THEN ROW(1,2) ELSE NULL END;
ERROR HY000: Illegal parameter data types row and null for operation 'case'
SELECT CASE WHEN TRUE THEN NULL ELSE ROW(2,1) END;
ERROR HY000: Illegal parameter data types null and row for operation 'case'
SELECT CASE WHEN TRUE THEN ROW(2,1) END;
ERROR HY000: Illegal parameter data type row for operation 'case'
SELECT CASE WHEN ROW(1,2) THEN 0 WHEN ROW(2,1) THEN 1 END;
ERROR HY000: Illegal parameter data type row for operation 'case'
SELECT CASE WHEN ROW(1,2) THEN 0 END;
ERROR HY000: Illegal parameter data type row for operation 'case'
SELECT CASE WHEN NULL THEN 0 WHEN ROW(1,2) THEN 0 END;
ERROR HY000: Illegal parameter data type row for operation 'case'
SELECT CASE WHEN ROW(1,2) THEN 0 WHEN NULL THEN 0 END;
ERROR HY000: Illegal parameter data type row for operation 'case'
SELECT CASE TRUE WHEN TRUE THEN ROW(1,2) ELSE ROW(2,1) END;
ERROR HY000: Illegal parameter data types row and row for operation 'case'
SELECT CASE TRUE WHEN TRUE THEN ROW(1,2) ELSE NULL END;
ERROR HY000: Illegal parameter data types row and null for operation 'case'
SELECT CASE TRUE WHEN TRUE THEN NULL ELSE ROW(2,1) END;
ERROR HY000: Illegal parameter data types null and row for operation 'case'
SELECT CASE TRUE WHEN TRUE THEN ROW(2,1) END;
ERROR HY000: Illegal parameter data type row for operation 'case'
SELECT CASE ROW(1,2) WHEN ROW(1,2) THEN 0 END;
ERROR HY000: Illegal parameter data type row for operation 'CASE WHEN'
SELECT CASE NULL WHEN ROW(1,2) THEN 0 END;
ERROR 21000: Operand should contain 1 column(s)
SELECT CASE ROW(1,2) WHEN NULL THEN 0 END;
ERROR 21000: Operand should contain 2 column(s)
SELECT oracle_schema.DECODE(TRUE, TRUE, /*then1*/ROW(1,2), /*else*/ROW(2,1));
ERROR HY000: Illegal parameter data types row and row for operation 'decode'
SELECT oracle_schema.DECODE(TRUE, TRUE, /*then1*/ROW(1,2), /*else*/NULL);
ERROR HY000: Illegal parameter data types row and null for operation 'decode'
SELECT oracle_schema.DECODE(TRUE, TRUE, /*then1*/NULL, /*else*/ROW(2,1));
ERROR HY000: Illegal parameter data types null and row for operation 'decode'
SELECT oracle_schema.DECODE(TRUE, TRUE, /*then1*/ROW(2,1));
ERROR HY000: Illegal parameter data type row for operation 'decode'
SELECT oracle_schema.DECODE(/*predicant*/ROW(1,2), /*when1*/ROW(1,2), /*then1*/0);
ERROR HY000: Illegal parameter data type row for operation 'CASE WHEN'
SELECT oracle_schema.DECODE(/*predicant*/NULL,     /*when1*/ROW(1,2), /*then1*/0);
ERROR 21000: Operand should contain 1 column(s)
SELECT oracle_schema.DECODE(/*predicant*/ROW(1,2), /*when1*/NULL,     /*then1*/0);
ERROR 21000: Operand should contain 2 column(s)
SELECT IF(0, ROW(1,2), ROW(1,2));
ERROR HY000: Illegal parameter data types row and row for operation 'if'
SELECT IF(0, NULL, ROW(1,2));
ERROR HY000: Illegal parameter data type row for operation 'if'
SELECT IF(0, ROW(1,2), NULL);
ERROR HY000: Illegal parameter data type row for operation 'if'
SELECT NVL2('0', ROW(1,2), ROW(1,2));
ERROR HY000: Illegal parameter data types row and row for operation 'nvl2'
SELECT NVL2('0', NULL, ROW(1,2));
ERROR HY000: Illegal parameter data type row for operation 'nvl2'
SELECT NVL2('0', ROW(1,2), NULL);
ERROR HY000: Illegal parameter data type row for operation 'nvl2'
SELECT NVL2(NULL, ROW(1,2), ROW(1,2));
ERROR HY000: Illegal parameter data types row and row for operation 'nvl2'
SELECT NVL2(NULL, NULL, ROW(1,2));
ERROR HY000: Illegal parameter data type row for operation 'nvl2'
SELECT NVL2(NULL, ROW(1,2), NULL);
ERROR HY000: Illegal parameter data type row for operation 'nvl2'
SELECT COALESCE(ROW(1,2), ROW(1,2));
ERROR HY000: Illegal parameter data types row and row for operation 'coalesce'
SELECT COALESCE(ROW(1,2));
ERROR HY000: Illegal parameter data type row for operation 'coalesce'
SELECT COALESCE(ROW(1,2),NULL);
ERROR HY000: Illegal parameter data types row and null for operation 'coalesce'
SELECT COALESCE(NULL,ROW(1,2));
ERROR HY000: Illegal parameter data types null and row for operation 'coalesce'
SELECT IFNULL(ROW(1,2), ROW(1,2));
ERROR HY000: Illegal parameter data types row and row for operation 'ifnull'
SELECT IFNULL(NULL, ROW(1,2));
ERROR HY000: Illegal parameter data types null and row for operation 'ifnull'
SELECT IFNULL(ROW(1,2), NULL);
ERROR HY000: Illegal parameter data types row and null for operation 'ifnull'
SELECT NULLIF(ROW(1,2), ROW(1,2));
ERROR HY000: Illegal parameter data type row for operation 'nullif'
SELECT NULLIF(NULL, ROW(1,2));
ERROR 21000: Operand should contain 1 column(s)
SELECT NULLIF(ROW(1,2), NULL);
ERROR HY000: Illegal parameter data type row for operation 'nullif'
# End of 12.0 tests
#
# MDEV-37534: Assertion `a == &type_handler_row || a == &type_handler_null` failed on `CREATE TABLE ... ROW()`
#
CREATE TABLE t (c INT CHECK(c=CASE c WHEN ROW(1, 1) THEN c=0 END IN (c)=1));
ERROR HY000: Illegal parameter data types int and row for operation 'case..when'
select CASE 1 WHEN ROW(1, 2) THEN 0 END from dual;
ERROR HY000: Illegal parameter data types int and row for operation 'case..when'
select CASE WHEN NULLIF(1, ROW(1,1)) THEN 0 ELSE 1 END from dual;
ERROR HY000: Illegal parameter data types int and row for operation 'nullif'
select NULLIF(1, ROW(1,2));
ERROR HY000: Illegal parameter data types int and row for operation 'nullif'
# End of 12.3 tests