MySQL Bugs: #22902: Query not able to use an index when comparing against results of SYSDATE()

Bug #22902	Query not able to use an index when comparing against results of SYSDATE()
Submitted:	2 Oct 2006 19:28	Modified:	30 Jul 2007 19:15
Reporter:	Matthew Montgomery	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.24, 5.1 BK	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	NOW, Optimizer, SYSDATE

Description:
Due to the change in 5.0.13 to make SYSDATE() function non-deterministc MySQL is now unable to compare use an index for queries using this function in a WHERE clause.  Is there any method which can be applied to allow the results of SYSDATE() to be compared to an index?

Currently there is a large application which is using SYSDATE() heavily for the sake of SQL code portablity, but requires the properties of NOW();  The option --sysdate-is-now is suffiecent for the immediate need but any use of SYSDATE() within a funciton or stored procedure will be unoptimized.

Related bugs:
Bug #12562 
Bug #15101 

How to repeat:
mysql> desc select count(*) from incidents where created > DATE_ADD( sysdate(), INTERVAL 1 day);
+----+-------------+-----------+-------+---------------+-------------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-------------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | incidents | index | NULL | incidents$created | 4 | NULL | 666232 | Using where; Using index |
+----+-------------+-----------+-------+---------------+-------------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> desc select count(*) from incidents where created > DATE_ADD( now(), INTERVAL 1 day);
+----+-------------+-----------+-------+-------------------+-------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+-------------------+-------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | incidents | range | incidents$created | incidents$created | 4 | NULL | 1 | Using where; Using index |
+----+-------------+-----------+-------+-------------------+-------------------+---------+------+------+--------------------------+
1 row in set (0.02 sec)

Thank you for the report.

Verified as described on Linux using last 5.0 and 5.1 BK sources and following table:

CREATE TABLE `incidents` (
  `created` date DEFAULT NULL,
  KEY `created` (`created`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

filled by 31 random rows.

Optimizer can't use indexes for the SYSDATE() because it isn't static and there is nothing can be done for it.

This has become a Documentation bug. Resetting category and status accordingly.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.