Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8752

Wrong result for SELECT..WHERE CASE enum_field WHEN 1 THEN 1 ELSE 0 END AND a='5'

        Details

          Description

          In this script:

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a ENUM('5','6') CHARACTER SET BINARY);
INSERT INTO t1 VALUES ('5'),('6');
SELECT * FROM t1 WHERE a='5';
SELECT * FROM t1 WHERE a=1;
SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END;

          all SELECT queries return the same row:

          +------+
| a    |
+------+
| 5    |
+------+

          Now if I join the first and the third conditions into the same condition:

          SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END AND a='5';

          it returns empty set. The expected result is to return the same row.

          Another example demonstrates that CASE does not propagate equal fields when it safely could:

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a ENUM('a','b'));
INSERT INTO t1 VALUES ('a'),('b');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a';
SHOW WARNINGS;

          The above script returns:

          +-------+------+--------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 'a') and (case `test`.`t1`.`a` when 'a' then 1 else 0 end)) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------+

          It could safely be rewritten as:

          SELECT * FROM t1 WHERE CASE 'a' WHEN 'a' THEN 1 ELSE 0 END AND a='a';

          and then remove the constant part:

          SELECT * FROM t1 WHERE a='a';

            Gliffy Diagrams

              Attachments

                Issue Links

                blocks

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8728 Fix a number of problems in equal field and equal expression propagation

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed

                  Activity

                  Hide
                  Permalink
                  bar Alexander Barkov added a comment -

                  MySQL-5.7.8 does not support propagation in this example:

                  DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a ENUM('a','b'));
INSERT INTO t1 VALUES ('a'),('b');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a';
SHOW WARNINGS;

                  +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                             |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                                                   |
| Note    | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 'a') and (case `test`.`t1`.`a` when 'a' then 1 else 0 end)) |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
                  Show
                  bar Alexander Barkov added a comment - MySQL-5.7.8 does not support propagation in this example: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a ENUM('a','b')); INSERT INTO t1 VALUES ('a'),('b'); EXPLAIN EXTENDED SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a'; SHOW WARNINGS; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. | | Note | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 'a') and (case `test`.`t1`.`a` when 'a' then 1 else 0 end)) | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+

                    People

                    • Assignee:
                      bar Alexander Barkov
                      Reporter:
                      bar Alexander Barkov
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: