MySQL Bugs: #31928: Search fails on '1000-00-00' date after sql

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

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)

Thank you for the report.

Verified as described.

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.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

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.