Bug #55198 Optimizer fails to evaluate an impossible condition

• Submitted: 12 Jul 2010 23:00
• Modified: 14 Jul 2010 7:46
• Reporter: Gerry Narvaja
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.1.47
• OS: Linux (Linux <hostname> 2.6.18-128.7.1.el5 #1 SMP Mon Aug 24 08:21:56 EDT 2009 x86_6)
• CPU Architecture: Any
• Tags: dates, Optimizer, SELECT

[12 Jul 2010 23:00] Gerry Narvaja

Description:
The optimizer fails to recognize an 'impossible' condition and when running on tables with 100M of rows, it can keep running for several days. 

NOTE: The dates are created within a program, using fixed values for simplicity sake.

See attached file for data sample.

Query #1:

SELECT * FROM event, customer_event WHERE id = event_id and created_date >= '2010-07-12 00:00:00' AND created_date < '2010-07-12 00:00:00';

+----+-------------+----------------+--------+--------------------------------+---------+---------+------------------------------+------+-------------+
| id | select_type | table          | type   | possible_keys                  | key     | key_len | ref                          | rows | Extra       |
+----+-------------+----------------+--------+--------------------------------+---------+---------+------------------------------+------+-------------+
|  1 | SIMPLE      | customer_event | ALL    | PRIMARY                        | NULL    | NULL    | NULL                         |   10 |             |
|  1 | SIMPLE      | event          | eq_ref | PRIMARY,event_created_date_idx | PRIMARY | 8       | test.customer_event.event_id |    1 | Using where |
+----+-------------+----------------+--------+--------------------------------+---------+---------+------------------------------+------+-------------+
2 rows in set (0.00 sec)

Query #2:

SELECT * FROM event use index (event_created_date_idx), customer_event WHERE id = event_id and created_date >= '2010-07-12 00:00:00' AND created_date < '2010-07-12 00:00:00';

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

The expected result for Query #1's EXPLAIN should be the same as Query #2.

How to repeat:
Run the above queries on the provided data.

[12 Jul 2010 23:00] Gerry Narvaja

mysqldump of the data needed to reproduce the bug

Attachment: test_data.sql (text/x-sql), 5.93 KiB.

[13 Jul 2010 5:24] James Day

Initially it looks as though this might be bug #53334 (duplicated by bug #54609) that is fixed in 5.1.48.

[13 Jul 2010 6:18] Sveta Smirnova

Thank you for the report.

Both queries return "Impossible WHERE noticed after reading const tables " in my environment, so for me it looks like James's assumption is correct. Please try version 5.1.48 in your environment so we can confirm bug is fixed.

[14 Jul 2010 7:46] James Day

This is a duplicate of bug #54609. Confirmed that an upgrade to version 5.1.48 fixed the problem.