| Bug #31221 | Optimizer incorrectly identifies impossible WHERE clause | ||
|---|---|---|---|
| Submitted: | 27 Sep 2007 5:26 | Modified: | 1 Nov 2007 2:41 |
| Reporter: | Todd Lipcon | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.45-log, 5.0, 5.1, 5.2 BK | OS: | Any |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
| Tags: | regression | ||
[27 Sep 2007 5:34]
Todd Lipcon
Forgot to mention -- I believe this error is fairly new. We started to experience this on 8/3/07 when we switched from what I think was a 5.0.41 server to a 5.0.45. I tested on a 5.0.26-standard-log server and this problem is not present.
[27 Sep 2007 10:52]
Sveta Smirnova
Thank you for the report. Verified as described.
[17 Oct 2007 14:40]
Georgi Kodinov
I believe the correct behavior here is to return 0 rows for the query that compares NOW() to a DATE field if the time field is non-empty (00:00:00.0000). This is how the server behaves if there is no index. on the DATE column.
[17 Oct 2007 14:47]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/35753 ChangeSet@1.2544, 2007-10-17 17:47:27+03:00, gkodinov@magare.gmz +13 -0 Bug #31221: Optimizer incorrectly identifies impossible WHERE clause No warning was generated when a TIMESTAMP with a non-zero time part was converted to a DATE value. This caused index lookup to assume that this is a valid conversion and was returning rows that match a comparison between a TIMESTAMP value and a DATE keypart. Fixed by generating a warning on such a truncation.
[17 Oct 2007 15:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/35759 ChangeSet@1.2544, 2007-10-17 18:54:56+03:00, gkodinov@magare.gmz +13 -0 Bug #31221: Optimizer incorrectly identifies impossible WHERE clause No warning was generated when a TIMESTAMP with a non-zero time part was converted to a DATE value. This caused index lookup to assume that this is a valid conversion and was returning rows that match a comparison between a TIMESTAMP value and a DATE keypart. Fixed by generating a warning on such a truncation.
[18 Oct 2007 12:19]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/35835 ChangeSet@1.2544, 2007-10-18 15:19:04+03:00, gkodinov@magare.gmz +13 -0 Bug #31221: Optimizer incorrectly identifies impossible WHERE clause No warning was generated when a TIMESTAMP with a non-zero time part was converted to a DATE value. This caused index lookup to assume that this is a valid conversion and was returning rows that match a comparison between a TIMESTAMP value and a DATE keypart. Fixed by generating a warning on such a truncation.
[19 Oct 2007 15:08]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/35931 ChangeSet@1.2534, 2007-10-19 17:07:08+02:00, df@pippilotta.erinye.com +13 -0 export patch for bug#31221
[29 Oct 2007 8:42]
Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:45]
Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:49]
Bugs System
Pushed into 6.0.4-alpha
[1 Nov 2007 2:41]
Paul DuBois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs. An assertion designed detect a bug in the ROLLUP implementation incorrectly failed when used in subquery context with non-cacheable statements.
[1 Nov 2007 2:43]
Paul DuBois
Sorry, the previous entry was intended for another bug report.
[1 Nov 2007 2:53]
Paul DuBois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs. When a TIMESTAMP with a non-zero time part was converted to a DATE value, no warning was generated. This caused index lookups to assume that this is a valid conversion and was returning rows that match a comparison between a TIMESTAMP value and a DATE keypart. Now a warning is generated so that TIMESTAMP with a non-zero time part will not match DATE values.
[1 Nov 2007 10:15]
Bugs System
Pushed into 6.0.4-alpha
[1 Nov 2007 10:16]
Bugs System
Pushed into 5.1.23-rc
[1 Nov 2007 10:17]
Bugs System
Pushed into 5.0.52
[8 Nov 2007 13:34]
Paul DuBois
Fixed in 5.0.50, not 5.0.52.
Description: The query optimizer decides that a WHERE condition is impossible in circumstances when it is not. By changing the query slightly, the optimization is removed and the correct result set is returned. How to repeat: CREATE TEMPORARY TABLE test_table ( `views` int(11) NOT NULL default '0', `clicks` int(11) NOT NULL default '0', `day` date NOT NULL default '0000-00-00', `hour` tinyint(4) NOT NULL default '0', `bannerid` smallint(6) NOT NULL default '0', `zoneid` smallint(6) NOT NULL default '0', `source` varchar(50) NOT NULL, PRIMARY KEY (`day`,`hour`,`bannerid`,`zoneid`,`source`), KEY `bannerid_day` (`bannerid`,`day`), KEY `zoneid` (`zoneid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- Query OK, 0 rows affected (0.01 sec) INSERT INTO test_table VALUES (1, 1, DATE(NOW()), HOUR(NOW()), 1, 1, ''); -- Query OK, 1 row affected (0.01 sec) SELECT * FROM test_table WHERE day = NOW(); -- Empty set (0.00 sec) EXPLAIN SELECT * FROM test_table WHERE day = NOW() \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Impossible WHERE noticed after reading const tables 1 row in set (0.00 sec) INSERT INTO test_table VALUES (1, 1, DATE(NOW()), HOUR(NOW()), 1, 2, ''); --Query OK, 1 row affected (0.04 sec) SELECT * FROM test_table WHERE day = NOW(); +-------+--------+------------+------+----------+--------+--------+ | views | clicks | day | hour | bannerid | zoneid | source | +-------+--------+------------+------+----------+--------+--------+ | 1 | 1 | 2007-09-27 | 1 | 1 | 1 | | | 1 | 1 | 2007-09-27 | 1 | 1 | 2 | | +-------+--------+------------+------+----------+--------+--------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT * FROM test_table WHERE day = NOW() \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test_table type: ref possible_keys: PRIMARY key: PRIMARY key_len: 3 ref: const rows: 1 Extra: 1 row in set (0.00 sec) SELECT * FROM test_table WHERE day = NOW() AND hour = 1 AND bannerid = 1 AND zoneid = 2; +-------+--------+------------+------+----------+--------+--------+ | views | clicks | day | hour | bannerid | zoneid | source | +-------+--------+------------+------+----------+--------+--------+ | 1 | 1 | 2007-09-27 | 1 | 1 | 2 | | +-------+--------+------------+------+----------+--------+--------+ 1 row in set (0.00 sec) SELECT * FROM test_table WHERE day = NOW() AND hour = 1 AND bannerid = 1 AND zoneid = 2 AND source = ''; Empty set (0.00 sec) SELECT * FROM test_table WHERE day = DATE(NOW()) AND hour = 1 AND bannerid = 1 AND zoneid = 2 AND source = ''; +-------+--------+------------+------+----------+--------+--------+ | views | clicks | day | hour | bannerid | zoneid | source | +-------+--------+------------+------+----------+--------+--------+ | 1 | 1 | 2007-09-27 | 1 | 1 | 2 | | +-------+--------+------------+------+----------+--------+--------+ 1 row in set (0.00 sec)