Bug #40614 Falcon datetime indexes can not handle 0000-00-00
Submitted: 10 Nov 2008 11:46 Modified: 13 Dec 2008 9:58
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S2 (Serious)
Version:6.0-falcon-team OS:Any
Assigned to: Lars-Erik Bjørk CPU Architecture:Any
Tags: F_ENCODING

[10 Nov 2008 11:46] Philip Stoev
Description:
MySQL considers 0000-00-00 to be a valid date and allows it to be inserted into datetime fields. Falcon however is unable to handle it.

How to repeat:
CREATE TABLE `table10` (
        `datetime_key` datetime,
        key (`datetime_key` )
);

INSERT IGNORE INTO table10 VALUES ('0000-00-00');

SELECT `datetime_key` FROM `table10` WHERE `datetime_key` < '2006-12-12';

This SELECT under Falcon will return zero rows and 1 row under Innodb.
[17 Nov 2008 13:49] Lars-Erik Bjørk
What really makes it interesting as is the following:

mysql> CREATE TABLE `table10` (
    ->         `datetime_key` datetime,
    ->         key (`datetime_key` )
    -> ) engine=falcon;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT IGNORE INTO table10 VALUES ('0000-00-00');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT `datetime_key` FROM `table10` WHERE `datetime_key` < '2006-12-12'; 
Empty set (0.00 sec)

mysql> SELECT `datetime_key` FROM `table10` WHERE `datetime_key` < '2007-12-12'; 
+---------------------+
| datetime_key        |
+---------------------+
| 0000-00-00 00:00:00 | 
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT `datetime_key` FROM `table10` WHERE `datetime_key` < '2005-12-12'; 
Empty set (0.00 sec)

mysql> SELECT `datetime_key` FROM `table10` WHERE `datetime_key` < '2004-12-12'; 
+---------------------+
| datetime_key        |
+---------------------+
| 0000-00-00 00:00:00 | 
+---------------------+
1 row in set (0.00 sec)

The record is returned only sometimes, but not always ...
[22 Nov 2008 12:13] Lars-Erik Bjørk
After we have found a hit in the index, registered in the bitmap,
we check the actual value of the record to see if it really
matches (the index may be dirty). Furter down the stack, in
Value::compare, we compare the stored value (0) to the key
value (f. ex 20061212000000). The stored value is represented as
a small int, whereas the key value is an int64. The comparison
is done by subtracting the key value from the stored value, and
returning this as an integer. However, because the special 0 (not
NULL) value is much smaller than the key value, the result will
be outside the range of an int. Therefore, when the int only uses
the last 4 bytes of the result, the sign-bit of the integer will
vary depending on the year part of the datetime value. This fools
Value::compare into returning that the 0 datetime-value is greater than a much
greater datetime-value for some values of year. The solution is
to check if the result is greater, equal or smaller than 0, looking at the
result as an int64 and then returning 1, 0 or -1, instead of returning the 
result of the subraction as an int directly.
[22 Nov 2008 12: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/59617

2916 lars-erik.bjork@sun.com	2008-11-22
      This is a patch for bug#40614 Falcon datetime indexes can not handle 0000-00-00
      
      
      After we have found a hit in the index, registered in the bitmap, we
      check the actual value of the record to see if it really matches (the
      index may be dirty). Further down the stack, in Value::compare, we
      compare the stored value (0) to the key value (f. ex
      20061212000000). The stored value is represented as a small int,
      whereas the key value is an int64. The comparison is done by
      subtracting the key value from the stored value, and returning this as
      an integer. However, because the special 0 (not NULL) value is much
      smaller than the key value, the result will be outside the range of an
      int. Therefore, when the int only uses the last 4 bytes of the result,
      the sign-bit of the integer will vary depending on the year part of
      the datetime value. This fools Value::compare into returning that the
      0 datetime-value is greater than a much greater datetime-value for
      some values of year. The solution is to check if the result is
      greater, equal or smaller than 0, looking at the result as an int64
      and then returning 1, 0 or -1, instead of returning the result of the
      subtraction as an int directly.
      
      
      Modified file 'storage/falcon/Value.cpp'
      ----------------------------------------
      Instead of returning the result of the subtraction directly, we now
      check if the result is greater, equal or smaller than 0, and return 1,
      0 or -1. This way we won't return the wrong result because we are no
      longer only taking the last 4 bytes of the value into consideration
      
      This change was made for both int64 and double comparisons.
      
      
      Added file 'mysql-test/suite/falcon/r/falcon_bug_40614.result'
      --------------------------------------------------------------
      This is the result file for the test.
      
      
      Added file 'mysql-test/suite/falcon/t/falcon_bug_40614.test'
      ------------------------------------------------------------ 
      This is the test that tests the patch. I have tested for plenty of year
      values, since this bug did not show for all values.
[24 Nov 2008 8:26] 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/59659

2916 lars-erik.bjork@sun.com	2008-11-24
      This is a patch for bug#40614 Falcon datetime indexes can not handle 0000-00-00
      
      
      After we have found a hit in the index, registered in the bitmap, we
      check the actual value of the record to see if it really matches (the
      index may be dirty). Further down the stack, in Value::compare, we
      compare the stored value (0) to the key value (f. ex
      20061212000000). The stored value is represented as a small int,
      whereas the key value is an int64. The comparison is done by
      subtracting the key value from the stored value, and returning this as
      an integer. However, because the special 0 (not NULL) value is much
      smaller than the key value, the result will be outside the range of an
      int. Therefore, when the int only uses the last 4 bytes of the result,
      the sign-bit of the integer will vary depending on the year part of
      the datetime value. This fools Value::compare into returning that the
      0 datetime-value is greater than a much greater datetime-value for
      some values of year. The solution is to compare the numbers without
      calculating any intermediate result, returning 1, 0 or -1.  I have
      taken the liberty to change this for some other comparisons than just
      the one failing in this bug.
      
      
      Modified file 'storage/falcon/Value.cpp'
      ---------------------------------------- 
      Instead of returning the result of the subtraction as an int, we now
      compare the values directly, returning 1, 0 or -1. This way we won't
      return the wrong result because we are no longer only taking the last
      4 bytes of the value into consideration
      
      This change was made for the places currently using subtraction to
      compare.
      
      
      Added file 'mysql-test/suite/falcon/r/falcon_bug_40614.result'
      --------------------------------------------------------------
      This is the result file for the test.
      
      
      Added file 'mysql-test/suite/falcon/t/falcon_bug_40614.test'
      ------------------------------------------------------------ 
      This is the test that tests the patch. I have tested for plenty of year
      values, since this bug did not show for all values.
[24 Nov 2008 8:57] 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/59660

2916 lars-erik.bjork@sun.com	2008-11-24
      This is a patch for bug#40614 Falcon datetime indexes can not handle 0000-00-00
      
      
      After we have found a hit in the index, registered in the bitmap, we
      check the actual value of the record to see if it really matches (the
      index may be dirty). Further down the stack, in Value::compare, we
      compare the stored value (0) to the key value (f. ex
      20061212000000). The stored value is represented as a small int,
      whereas the key value is an int64. The comparison is done by
      subtracting the key value from the stored value, and returning this as
      an integer. However, because the special 0 (not NULL) value is much
      smaller than the key value, the result will be outside the range of an
      int. Therefore, when the int only uses the last 4 bytes of the result,
      the sign-bit of the integer will vary depending on the year part of
      the datetime value. This fools Value::compare into returning that the
      0 datetime-value is greater than a much greater datetime-value for
      some values of year. The solution is to compare the numbers without
      calculating any intermediate result, returning 1, 0 or -1.  I have
      taken the liberty to change this for some other comparisons than just
      the one failing in this bug.
      
      
      Modified file 'storage/falcon/Value.cpp'
      ---------------------------------------- 
      Instead of returning the result of the subtraction as an int, we now
      compare the values directly, returning 1, 0 or -1. This way we won't
      return the wrong result because we are no longer only taking the last
      4 bytes of the value into consideration
      
      This change was made for the places currently using subtraction to
      compare.
      
      
      Added file 'mysql-test/suite/falcon/r/falcon_bug_40614.result'
      --------------------------------------------------------------
      This is the result file for the test.
      
      
      Added file 'mysql-test/suite/falcon/t/falcon_bug_40614.test'
      ------------------------------------------------------------ 
      This is the test that tests the patch. I have tested for plenty of year
      values, since this bug did not show for all values.
[11 Dec 2008 14:19] Bugs System
Pushed into 6.0.9-alpha  (revid:lars-erik.bjork@sun.com-20081124085629-8csc2gbvy9cf4l1g) (version source revid:hky@sun.com-20081127084516-nbu7693932vcz2st) (pib:5)
[13 Dec 2008 9:58] MC Brown
A note has been added to the 6.0.9 changelog: 

Date values of 000-00-00 inserted into a FALCON table were incorrectly recognized and returned when performing a SELECT on a field with an index.