MySQL Bugs: #32021: Using Date 000-00-01 in WHERE causes wrong result

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

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

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" ...

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/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.

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.

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.

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

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.)