Bug #30536 Falcon: searches fail if partition, index

• Submitted: 21 Aug 2007 18:13
• Modified: 22 Nov 2007 11:17
• Reporter: Peter Gulutzan
• Status: Duplicate
• 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] MySQL Verification Team

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