|
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.
|