|
This query returns NULL with a warning about datetime overflow.
SELECT DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR);
|
If I use now this expression in a WHERE condition on a TIMESTAMP field with DEFAULT CURRENT_TIMESTAMP:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY(a)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
|
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
|
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
|
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
|
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
|
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
|
EXPLAIN SELECT * FROM t1 WHERE a >= DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR);
|
it reports that one row will be checked:
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 4 | NULL | 1 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
This is wrong. It should report "Impossible where".
Note, if I change the condition wrapping DATE_ADD to COALESCE:
EXPLAIN SELECT * FROM t1 WHERE a >= COALESCE(DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR));
|
it starts to report "Impossible where" correctly:
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
|