MySQL Bugs: #29320: Falcon: searches fail if subpartition and select for update

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

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 */

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)

This looks like another bug where the partition engine assumes that the list returned by Falcon is sorted.  See Bug#30480.

Duplicate of Bug#30480, will be verified with test case in that bug

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc