diff options
author | Alexander Barkov <bar@mariadb.com> | 2018-10-11 13:39:53 +0400 |
---|---|---|
committer | Alexander Barkov <bar@mariadb.com> | 2018-10-11 13:39:53 +0400 |
commit | 4de0d920bede330ddf7790d0aee4bf90a00122ae (patch) | |
tree | 96b05f6cbb46e867b014aff4cc8583f93579ba6f /mysql-test/main/case.result | |
parent | 30629e196d8f40ac7a769730c7641286aa2cfd44 (diff) | |
download | mariadb-git-4de0d920bede330ddf7790d0aee4bf90a00122ae.tar.gz |
MDEV-17411 Wrong WHERE optimization with simple CASE and searched CASE
Diffstat (limited to 'mysql-test/main/case.result')
-rw-r--r-- | mysql-test/main/case.result | 27 |
1 files changed, 27 insertions, 0 deletions
diff --git a/mysql-test/main/case.result b/mysql-test/main/case.result index 1fd1ad86a8e..926ac000eb0 100644 --- a/mysql-test/main/case.result +++ b/mysql-test/main/case.result @@ -546,5 +546,32 @@ 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 # |