Bug #28687 Search fails on '0000-00-00' date after sql_mode change
Submitted: 25 May 2007 19:19 Modified: 31 Oct 2007 1:22
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.19-beta-debug, 5.0 OS:Linux (SUSE 10 64-bit)
Assigned to: Georgi Kodinov CPU Architecture:Any

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

This may be related to bug#28682.

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

mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (s1 date) engine=myisam;
Query OK, 0 rows affected (0.05 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 ('0000-00-00'),('0000-00-00');
Query OK, 2 rows affected (0.05 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 = '0000-00-00';
Empty set, 2 warnings (0.03 sec)
[26 May 2007 0:53] MySQL Verification Team
Thank you for the bug report. Verified as described on FC 6.0 32-bit.
[1 Jun 2007 11:48] Sergei Golubchik
Why it's a bug ?
What result would you consider as correct ?
[1 Jun 2007 13:26] Peter Gulutzan
Why is it a bug?

If there is an index I see no rows.

If there is no index I see two rows.

If you can provide me with an example
(from some other DBMS, from the manual,
from some other bug report) where it's
been considered correct that a SELECT
returns different result sets depending
whether the column is indexed or not, then
perhaps I will understand the question better.

What would I consider as correct?

I will distinguish between "non-bug" and
"correct". It's a non-bug if MySQL
consistently returns 0 rows, or consistently
returns 2 rows, It's correct if MySQL
consistently returns 2 rows.

There was some controversy about what
strict / traditional / no_zero_date / etc.
"should" mean. When asked what is correct,
I will say that there should be an error
(not a warning) for an illegal value, with
emphasis on assignment. But that is not
relevant to this bug report.
[1 Jun 2007 14:12] Sergei Golubchik
okay. I didn't understand from the bugreport that you got different results with and without index.
sorry for confusion
[22 Oct 2007 16:32] 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/36054

ChangeSet@1.2550, 2007-10-22 19:32:18+03:00, gkodinov@magare.gmz +3 -0
  Bug #28687: Search fails on '0000-00-00' date after sql_mode change
  
  When doing indexed search the server constructs a key image for 
  faster comparison to the stored keys. While doing that it must not
  perform (and stop if they fail) the additional date checks that can 
  be turned on by the SQL mode because there already may be values in 
  the table that don't comply with the error checks.
  Fixed by ignoring these SQL mode bits while making the key image.
[29 Oct 2007 8:43] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:46] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:49] Bugs System
Pushed into 6.0.4-alpha
[31 Oct 2007 1:22] Paul DuBois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

After changing the SQL mode to a restrictive value that would
make already-inserted dates in a column be considered invalid,
searches returned different results depending on whether the
column was indexed.