Details
Major
Description
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(20));
INSERT INTO t1 VALUES ('a'),('b');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)='a' AND COALESCE(a)>='a';
SHOW WARNINGS;
returns
+-------+------+----------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 'a') | +-------+------+----------------------------------------------------------------------------------------+
I.e. the right AND part with COALESCE() was optimized away from the condition by equal expression propagation.
If I do the same for DATE data type:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-01' AND COALESCE(a)>=DATE'2001-01-01';
SHOW WARNINGS;
it returns:
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = DATE'2001-01-01') and (coalesce(`test`.`t1`.`a`) >= DATE'2001-01-01')) | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
i.e. equal expression propagation was not applied.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Repeatable with MySQL-5.7.8