mirror of
https://github.com/MariaDB/server.git
synced 2025-08-16 15:31:32 +02:00
9fc124f8f9
SQL Standard specifies that a <simple case> should return the <result> from the left-most <simple when clause> where the comparison evaluates to TRUE. That is, it should not stop comparing on NULLs
592 lines
21 KiB
Text
592 lines
21 KiB
Text
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 NOT 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
|
|
#
|
|
# 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
|
|
# End of 10.11 test
|