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: | |
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
[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.