MySQL Bugs: #29319: Falcon: searches fail if multi-column index exists

Bug #29319	Falcon: searches fail if multi-column index exists
Submitted:	23 Jun 2007 22:31	Modified:	2 Jul 2007 4:47
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	6.0.1-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Jim Starkey	CPU Architecture:	Any

Description:
I create a Falcon table with two columns.
I create one index for both columns.
I insert one row.
I search for the row, using 'and' and '<'.
I get nothing.

How to repeat:
mysql> create table t9 (s1 int, s2 int) engine=falcon;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t9 values (1, -1);
Query OK, 1 row affected (0.00 sec)

mysql> create index i on t9 (s1,s2);
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t9 where s1 = 1 and s2 < 0;
Empty set (0.00 sec)

Thank you for the bug report. Verified as described.

c:\dev\6.0>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 6.0.1-alpha-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database db5;
Query OK, 1 row affected (0.00 sec)

mysql> use db5
Database changed
mysql>  create table t9 (s1 int, s2 int) engine=falcon;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t9 values (1, -1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t9 where s1 = 1 and s2 < 0;
+------+------+
| s1   | s2   |
+------+------+
|    1 |   -1 |
+------+------+
1 row in set (0.00 sec)

mysql> create index i on t9 (s1,s2);
Query OK, 1 row affected (0.17 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t9 where s1 = 1 and s2 < 0;
Empty set (0.00 sec)

mysql>

Added test case falcon_bug_29319.test and pushed into 5.1-falcon tree.

Test(s) which will be run though they are marked as disabled:
  falcon_bug_29319     : Bug#29319 2007-06-24 hakank Currently failing

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

falcon_bug_29319               [ pass ]             92
-------------------------------------------------------

A note has been added to the 6.0.1 changelog: 

Using SELECT on a table that uses two INT columns with a single index would fail to return rows that queried both columns and complex comparison operators. (Bug#29319)