Bug #31928 Search fails on '1000-00-00' date after sql_mode change
Submitted: 29 Oct 2007 20:09 Modified: 18 Dec 2007 4:44
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:6.0.4-alpha-debug, 5.0, 5.1 BK OS:Linux (SUSE 10 64-bit)
Assigned to: Georgi Kodinov CPU Architecture:Any
Triage: D2 (Serious)

[29 Oct 2007 20:09] Peter Gulutzan
Description:
I create a table with a date column.
While sql_mode = '', I insert '1000-00-00'.
I change sql_mode to 'traditional'.
I search for '1000-00-00'.
I find nothing.
Failure occurs only if there's an index.

I expect there is a close relationship with
Bug#28687 Search fails on '0000-00-00' date after sql_mode change
But bug#28687 is now fixed, '0000-00-00' works fine.
So I report this as a separate bug.

How to repeat:
mysql> create table t (s1 date) engine=myisam;
Query OK, 0 rows affected (0.03 sec)

mysql> create index i on t (s1);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t values ('1000-00-00'),('1000-00-00');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t where s1 = '1000-00-00';
+------------+
| s1         |
+------------+
| 1000-00-00 |
| 1000-00-00 |
+------------+
2 rows in set (0.01 sec)

mysql> set sql_mode=traditional;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where s1 = '1000-00-00';
Empty set, 4 warnings (0.00 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> drop index i on t;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set sql_mode=traditional;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where s1 = '1000-00-00';
+------------+
| s1         |
+------------+
| 1000-00-00 |
| 1000-00-00 |
+------------+
2 rows in set, 2 warnings (0.01 sec)
[29 Oct 2007 20:53] Sveta Smirnova
Thank you for the report.

Verified as described.
[7 Nov 2007 16:03] 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/37271

ChangeSet@1.2562, 2007-11-07 18:02:12+02:00, gkodinov@magare.gmz +3 -0
  Bug #31928: Search fails on '1000-00-00' date after sql_mode change
  
  When constructing a key image stricter date checking (from sql_mode)
  should not be enabled, because it will reject invalid dates that the
  server would otherwise accept for searching when there's no index.
   
  Fixed by disabling strict date checking when constructing a key image.
[14 Dec 2007 8:15] Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:18] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:21] Bugs System
Pushed into 6.0.5-alpha
[18 Dec 2007 4:44] Paul Dubois
Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

Changing the SQL mode to cause dates with zero parts to be considered
invalid (such as '1000-00-00') could result in indexed and
non-indexed searches returning different results for a column that
contained such dates.