MySQL Bugs: #28687: Search fails on '0000-00-00' date after sql

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

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)

Thank you for the bug report. Verified as described on FC 6.0 32-bit.

Why it's a bug ?
What result would you consider as correct ?

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.

okay. I didn't understand from the bugreport that you got different results with and without index.
sorry for confusion

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.

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

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.