Details
Major
Description
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a ENUM('5','6'));
INSERT INTO t1 VALUES ('5'),('6');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2 AND a<>1;
SHOW WARNINGS;
returns
+-------+------+---------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2) and (`test`.`t1`.`a` <> 1)) | +-------+------+---------------------------------------------------------------------------------------------------------+
It should be safe to rewrite the condition to:
SELECT * FROM t1 WHERE a=2 AND 2<>1;
and then remove the "AND 2<>1" part as a true constant:
SELECT * FROM t1 WHERE a=2;
Another example:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a ENUM('a','b','100'));
INSERT INTO t1 VALUES ('a'),('b'),('100');
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a=3;
returns
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+----------------------------------------------------------------------------------------------------------------------------------+
It should be safe to propagate a=3 into CASE in this example.
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-8728 Fix a number of problems in equal field and equal expression propagation
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions