|
SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20');
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a TIME);
|
INSERT INTO t1 VALUES('10:20:30'),('00:00:00');
|
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00');
|
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11');
|
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11');
|
The first and the second SELECT query correctly return one row:
+----------+
|
| a |
|
+----------+
|
| 00:00:00 |
|
+----------+
|
The third query erroneously returns empty set.
EXPLAIN SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11');
|
returns:
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
The problem happens in Arg_comparator::datetime() called from propagate_cond_constants.
Arg_comparator tries to compare the TIME and the DATE literals and fails.
If I rewrite the query like this:
SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11';
|
it returns the correct row:
+----------+
|
| a |
|
+----------+
|
| 00:00:00 |
|
+----------+
|
But equal expression propagation is not performed:
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11';
|
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = TIME'00:00:00') and (coalesce(`test`.`t1`.`a`) = DATE'2015-09-11')) |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|
which also looks like a bug.
Summary: both queries:
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11');
|
SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11';
|
are expected to return one row, and perform equal expression propagation.
- the first query performs propagation but returns a wrong result
- the second query returns a correct result but does not perform propagation
|