| Bug #27426 | Falcon: searches fail if datetime column and index exists | ||
|---|---|---|---|
| Submitted: | 25 Mar 2007 18:31 | Modified: | 3 Dec 2007 14:23 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Falcon storage engine | Severity: | S2 (Serious) |
| Version: | 5.2.4-falcon-alpha-debug | OS: | Linux (SUSE 10.0 / 64-bit) |
| Assigned to: | Christopher Powers | CPU Architecture: | Any |
[25 Mar 2007 19:08]
Hakan Küçükyılmaz
Verified with latest change set on Linux 32-bit. Added additional test to test case falcon_bug_208.test. Best regards, Hakan
[27 Mar 2007 7:35]
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/22979 ChangeSet@1.2507, 2007-03-27 09:35:31+02:00, hakank@lu0008.wdf.sap.corp +8 -0 Extracted test for BUG#27426.
[27 Mar 2007 7:37]
Hakan Küçükyılmaz
Added test case falcon_bug_27426.test. Regards, Hakan
[23 Oct 2007 19:53]
Kevin Lewis
>Ann wrote; >Bug#27426 appears to be a real wrong answer due to indexing issues >with datetime data. It should probably be higher priority. The >examples below show at range comparisons seem to work for values >that are close to the stored value, but not for others that are >further away. mysql> create table t (s1 datetime unique) engine=falcon; Query OK, 0 rows affected (0.09 sec) mysql> insert into t values ('1965-06-29 11:59:58'); Query OK, 1 row affected (0.05 sec) mysql> select * from t where s1 < '2000-06-29 12:59:50'; +---------------------+ | s1 | +---------------------+ | 1965-06-29 11:59:58 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from t where s1 < '2001-06-29 12:59:50'; Empty set (0.01 sec) mysql> select * from t where s1 > '1861-06-29 12:59:50'; +---------------------+ | s1 | +---------------------+ | 1965-06-29 11:59:58 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from t where s1 > '1761-06-29 12:59:50'; Empty set (0.00 sec)
[24 Oct 2007 7:16]
Christopher Powers
DATETIME values are stored as 64-bit integers. The index scan compares two DATETIMEs by subtracting the 64-bit values, but returns a 32-bit result, which can be overflowed if the values are large enough. Replaced the 64-bit subtraction in Value::compare() with a greater/less than comparison. Changeset 1.2588.3.86.
[30 Nov 2007 2:47]
Hakan Küçükyılmaz
falcon_bug_27426 passes now TEST RESULT TIME (ms) ------------------------------------------------------- falcon.falcon_bug_27426 [ pass ] 154 ------------------------------------------------------- Stopping All Servers All 1 tests were successful.
[30 Nov 2007 20:43]
Bugs System
Pushed into 6.0.4-alpha
[3 Dec 2007 14:23]
MC Brown
A note has been added to the 6.0.4 changelog: Searching a Falcon table that uses DATETIME columns with an index could return incorrect results.
Description: I create a Falcon table with a unique datetime column. I insert a row with a date in 1965. I search for all rows with dates before 9999. The row that I inserted does not appear. How to repeat: mysql> create table t (s1 datetime unique) engine=falcon; Query OK, 0 rows affected (0.01 sec) mysql> insert into t values ('1965-06-29 11:59:58'); Query OK, 1 row affected (0.00 sec) mysql> select * from t where s1 < '9999-12-31 00:00:00'; Empty set (0.00 sec)