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

[17 Oct 2005 23:36] jocelyn fournier
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.
[17 Oct 2005 23:49] jocelyn fournier
Note that running the server with option --sql_mode=ALLOW_INVALID_DATES solve the performance issue.
[21 Oct 2005 21:48] Evgeny Potemkin
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.
[21 Oct 2005 23:54] jocelyn fournier
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
[27 Oct 2005 13:29] 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/internals/31561
[3 Nov 2005 10:53] 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/internals/31872
[3 Nov 2005 12:01] Evgeny Potemkin
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
[3 Nov 2005 14:43] 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/internals/31887
[9 Nov 2005 4:02] Paul Dubois
Noted in 5.0.16 changelog.