Bug #29320 Falcon: searches fail if subpartition and select for update
Submitted: 23 Jun 2007 23:14 Modified: 22 Nov 2007 11:14
Reporter: Peter Gulutzan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:6.0.1-alpha-debug OS:Linux (SUSE 10 64-bit)
Assigned to: Assigned Account CPU Architecture:Any

[23 Jun 2007 23:14] Peter Gulutzan
Description:
I create a Falcon table with partitions, subpartitions, and an index.
Using two mysql client connections, I do SELECT FOR UPDATE a few times.
Eventually I get a situation where a SELECT returns 0 rows.
But a row exists, it's merely in use on another connection.

How to repeat:
Start two mysql clients. Call them T1 and T2.

On T1, say:
drop table if exists t9;
set @@autocommit=0;
create table t9 (s1 int, s2 int) engine=falcon
partition by list (s1)
subpartition by hash (s2)
subpartitions 2
(partition p1 values in (1), partition p2 values in (2));
create index i on t9 (s1,s2);
insert into t9 values (1,1000),(1,1001);
commit;
select * from t9 where s2 = 1000 for update;

On T2, say:
set @@autocommit=0;
select * from t9 where s2 = 1001 for update;

On T1, say:
select * from t9 where s2 = 1001 for update; /* hangs */

On T2, say:
select * from t9 where s2 = 1000 for update; /* returns 0 rows */
[24 Jun 2007 14:42] Miguel Solorzano
Thank you for the bug report. Verified as described.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.0.1-alpha-nt Source distribution

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

T2>use test
Database changed
T2>set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

T2>select * from t9 where s2 = 1001 for update;
+------+------+
| s1   | s2   |
+------+------+
|    1 | 1001 |
+------+------+
1 row in set (0.00 sec)

T2>select * from t9 where s2 = 1000 for update;
Empty set (0.01 sec)

T2>show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 6.0.1-alpha-nt      |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
5 rows in set (0.00 sec)
[18 Oct 2007 16:41] Kevin Lewis
This looks like another bug where the partition engine assumes that the list returned by Falcon is sorted.  See Bug#30480.
[22 Nov 2007 11:14] Mattias Jonsson
Duplicate of Bug#30480, will be verified with test case in 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