Bug #31221 Optimizer incorrectly identifies impossible WHERE clause

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)

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.

Thank you for the report.

Verified as described.

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.

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.

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.

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.

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

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

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.

Sorry, the previous entry was intended for another bug report.

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.

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.52

Fixed in 5.0.50, not 5.0.52.