Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
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.
Attachments
Issue Links
- relates to
-
MDEV-8728 Fix a number of problems in equal field and equal expression propagation
-
- Closed
-