Bug #30536 Falcon: searches fail if partition, index
Submitted: 21 Aug 2007 18:13 Modified: 22 Nov 2007 11:17
Reporter: Peter Gulutzan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:6.0.2-alpha-debug OS:Linux (SUSE 10 64-bit)
Assigned to: Assigned Account CPU Architecture:Any

[21 Aug 2007 18:13] Peter Gulutzan
Description:
I create a Falcon table with a single partition.
I create an index.
I insert two numbers. One of the numbers is -6.
I search for "< -5".
I get nothing.

Compare
Bug#22939 Falcon: searches for negative numbers fail if index exists and partition
which was closed long ago.

How to repeat:
mysql> create table tf (s1 tinyint) engine=falcon partition by list (0 * s1) (partition p0 values in (0));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tf values (-4),(-6);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create index i on tf (s1);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tf where s1 < -5;
Empty set (0.01 sec)
[21 Aug 2007 22:15] Miguel Solorzano
Thank you for the bug report. Verified as described.
[22 Aug 2007 18:24] Peter Gulutzan
It happens with positive numbers too.
It happens with innocent-looking partition functions too.

Two more test cases:

drop table tv;
create table tv (s1 int) engine=falcon
 partition by list (0 * s1) (partition p0 values in (0));
create index i on tv (s1);
insert into tv values (1),(2),(1);
select * from tv where s1 < 2; /* fails */

drop table tv;
create table tv (s1 int) engine=falcon
 partition by list (s1) (partition p0 values in (1,2));
create index i on tv (s1);
insert into tv values (1),(2),(1);
select * from tv where s1 < 2; /* fails */
[4 Sep 2007 23:28] Peter Gulutzan
Another test case:

drop table if exists t7;
create table t7 (s1 varchar(5) character set latin2 collate latin2_czech_cs)
         engine=falcon
         partition by key(s1);
create unique index i on t7 (s1);
insert into t7 values ('w'),('W'),('z'),('Z'),('x'),('X'),('y'),('Y');
select s1 from t7 where s1 between 'x' and 'z'; /* fails */
[16 Oct 2007 11:34] Hakan Küçükyılmaz
Raised to S2 because of missing data
[18 Oct 2007 16:43] Kevin Lewis
This looks like another bug related to Bug#30480 where the partition engine assumes that the list returned from Falcon is sorted.
[31 Oct 2007 16:34] Calvin Sun
Upgrade to P2 due to wrong result.
[22 Nov 2007 11:17] Mattias Jonsson
Duplicate of Bug#30480, will be verified in test case of that bug
[12 Dec 2007 23:00] Bugs System
Pushed into 6.0.5-alpha
[12 Dec 2007 23:02] Bugs System
Pushed into 5.1.23-rc