MySQL Bugs: #14093: Query takes a lot of time when date format is not valid

Bug #14093	Query takes a lot of time when date format is not valid
Submitted:	17 Oct 2005 23:36	Modified:	9 Nov 2005 4:02
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.13-rc/BK source	OS:	Linux (linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
Hi,

When issuing a query on an invalid date, the query takes a lot of time, but I shouldn't. (no problem on 4.1.xx)

Regards,
  Jocelyn

How to repeat:
Download the ftp://ftp.mysql.com/pub/mysql/upload/th0.tar.gz

Then :

SELECT 1 FROM threadhardwarefr0 WHERE date>='2005-09-30' ORDER BY date ASC LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

=> OK

SELECT 1 FROM threadhardwarefr0 WHERE date>='0000-00-00' ORDER BY date ASC LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

=> OK 

SELECT 1 FROM threadhardwarefr0 WHERE date>='2005-09-31' ORDER BY date ASC LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 3 warnings  (2.20 sec)

=> It should not take so much time

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2005-09-31' for column 'date' at row 1 |
| Warning | 1292 | Incorrect datetime value: '2005-09-31' for column 'date' at row 1 |
| Warning | 1292 | Incorrect datetime value: '2005-09-31' for column 'date' at row 1 |
+---------+------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

On a bigger table, it takes more than 200 seconds to issue the query.

The last two queries have the same execution plan :

mysql> EXPLAIN SELECT 1 FROM threadhardwarefr0 WHERE date>='2005-09-31' ORDER BY date ASC LIMIT 1;
+----+-------------+-------------------+-------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table             | type  | possible_keys | key  | key_len | ref  | rows  | Extra                    |
+----+-------------+-------------------+-------+---------------+------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | threadhardwarefr0 | index | date          | date | 8       | NULL | 87626 | Using where; Using index |
+----+-------------+-------------------+-------+---------------+------+---------+------+-------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql> EXPLAIN SELECT 1 FROM threadhardwarefr0 WHERE date>='0000-00-00' ORDER BY date ASC LIMIT 1;
+----+-------------+-------------------+-------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table             | type  | possible_keys | key  | key_len | ref  | rows  | Extra                    |
+----+-------------+-------------------+-------+---------------+------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | threadhardwarefr0 | index | date          | date | 8       | NULL | 87626 | Using where; Using index |
+----+-------------+-------------------+-------+---------------+------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

Suggested fix:
The query should not take so much time.

Note that running the server with option --sql_mode=ALLOW_INVALID_DATES solve the performance issue.

If date constant is valid then it's converted to internal integer representation and later compared with field as integer. Wrong values can't be converted and compared to field as a string, which is obviously slower.

Hi,

I understand the new MySQL 5 behaviour is to not allow storing of invalid date by default, but comparing with invalid date is not something forbidden.
In this case, why not converting the invalid field to '0000-00-00' internally (since it returns the same result anyway), it would speed up this kind of pre MySQL 5 queries a lot. 
(moreover it will not be obvious for a user to figure out why MySQL 5 is significantly slower with its application if he has not added checks in its website date form field for example).

Thanks,
  Jocelyn

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/internals/31561

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/internals/31872

Invalid date like 2000-02-32 wasn't converted to int, which lead to not
using index and comparison with field as astring, which results in slow
query execution.

Fixed in 5.0.16, cset  1.1939.10.1

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/internals/31887

Noted in 5.0.16 changelog.