Bug #27426 Falcon: searches fail if datetime column and index exists

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)

Verified with latest change set on Linux 32-bit. Added additional test to test case falcon_bug_208.test.

Best regards, Hakan

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.

Added test case falcon_bug_27426.test.

Regards, Hakan

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

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.

falcon_bug_27426 passes now

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

falcon.falcon_bug_27426        [ pass ]            154
-------------------------------------------------------
Stopping All Servers
All 1 tests were successful.

Pushed into 6.0.4-alpha

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.