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:
None 
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 18:31] Peter Gulutzan
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)
[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.