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:
None 
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:26] Todd Lipcon
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)
[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.