Bug #32021 Using Date 000-00-01 in WHERE causes wrong result
Submitted: 1 Nov 2007 9:25 Modified: 14 Dec 2007 19:33
Reporter: Mikael Ronström Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1 BK OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Triage: D2 (Serious)

[1 Nov 2007 9:25] Mikael Ronström
Description:
Using Dates 000-00-01 and 99 days ahead cause WRONG
query results

How to repeat:
create table t1 (a date, primary key (a) using hash) engine=memory;
insert into t1 values ('0000-01-01');
select * from t1;
select * from t1 where a between '0000-00-01' and '0000-00-02';
insert into t1 values ('0000-00-01'), ('0001-01-01');
select * from t1;
select * from t1 where a between '0000-00-01' and '0000-00-02';

This test case reports no rows in result set of last query which is a bug.

create table t1 (a date, primary key (a) using hash) engine=memory;
insert into t1 values ('0000-00-01');
select * from t1;
select * from t1 where a between '0000-00-01' and '0000-00-02';
insert into t1 values ('0000-01-01'), ('0001-01-01');
select * from t1;
select * from t1 where a between '0000-00-01' and '0000-00-02';

This is your test case where the last select reports the correct result.

Suggested fix:
I found that the problem is that the item used for select checks for some
reason store some state which changes the behaviour of the query
dependent on the order the rows arrive. This state is an item_cache.

I did also analyse the reason of why this happens:
The date '0000-00-01' is stored as the value 1

In get_datetime_value the value is converted using the following:
    if (item->field_type() == MYSQL_TYPE_DATE || value < 100000000L)
      value*= 1000000L;

Thus the value is set 1 million instead of 1, further down this value is
inserted into a cache item.

This means that the next val_int is performed the value is 1 million but
the comparison above is comparing against 100 million and thus the
cached value is changed to 1 million * 1 million and grieve strikes :)

The problem dates are the 99 first days of 0000, these will be multiplied
1 million but will still not be as large as 100 million which is what is
compared against.

At the same time I'll convert this bug to a Server Data Types category where I
will also place the new bug
[1 Nov 2007 12:00] Hartmut Holzgraefe
0000-00-01 is an invalid date by itself
plus we specify in the documentation that
we only support dates starting 1000-01-01
anyway ...

  "The supported range is '1000-01-01' to '9999-12-31'."
  http://dev.mysql.com/doc/refman/5.0/en/datetime.html

unless we change that i'd say that this is either
"Not a Bug" or at least a "Won't Fix" ...
[14 Nov 2007 20:02] Sveta Smirnova
Thank you for the report.

Verified as described.
[16 Nov 2007 5:15] 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/37918

ChangeSet@1.2619, 2007-11-16 09:15:36+04:00, ramil@mysql.com +3 -0
  Fix for bug #32021: Using Date 000-00-01 in WHERE causes wrong result
  
  Problem: caching 00000000-00000099 dates as integer values we're 
  improperly shifting them up twice in the get_datetime_value().
  
  Fix: don't shift cached values up for the second time.
[17 Nov 2007 6:13] 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/37988

ChangeSet@1.2619, 2007-11-17 10:13:43+04:00, ramil@mysql.com +4 -0
  Fix for bug #32021: Using Date 000-00-01 in WHERE causes wrong result
  
  Problem: caching 00000000-00000099 dates as integer values we're 
  improperly shifting them up twice in the get_datetime_value().
  
  Fix: don't shift cached DATETIME values up for the second time.
[21 Nov 2007 4:01] 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/38182

ChangeSet@1.2627, 2007-11-21 08:01:00+04:00, ramil@mysql.com +4 -0
  Fix for bug #32021: Using Date 000-00-01 in WHERE causes wrong result
    
  Problem: caching 00000000-00000099 dates as integer values we're 
  improperly shifting them up twice in the get_datetime_value().
    
  Fix: don't shift cached DATETIME values up for the second time.
[28 Nov 2007 10:24] Bugs System
Pushed into 6.0.4-alpha
[28 Nov 2007 10:26] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 19:33] Paul Dubois
Noted in 5.1.23, 6.0.4 changelogs.

Using dates in the range '0000-00-01' to '0000-00-99' range in the
WHERE clause could result in an incorrect result set. (These dates
are not in the supported range for DATE, but different results for a
given query could occur depending on position of records containing
the dates within a table.)