Description:
In the function index_flags():
The flag HA_READ_ORDER means the index returns rows in order.
If this flag is not set then MySQL will sort the rows coming from the index
scan.
But index_flags() is only called on open table. For the PBXT storage engine, I need to set this flag on a per query basis.
The PBXT storage engine normally returns rows from on index scan in index order.
However, due to possible concurrent updates there are situations (that can be determined by the type of query) where this is not the case.
How to repeat:
Using the PBXT storage engine a SELECT FOR UPDATE with an ORDER BY is an example of this problem.
Create the following table:
DROP TABLE IF EXISTS test_tab;
CREATE TABLE test_tab (ID int primary key, Value int, Name varchar(20), index(Value, Name)) ENGINE=pbxt;
INSERT test_tab values(1, 1, 'A');
INSERT test_tab values(2, 1, 'B');
INSERT test_tab values(3, 1, 'C');
INSERT test_tab values(4, 2, 'D');
INSERT test_tab values(5, 2, 'E');
INSERT test_tab values(6, 2, 'F');
INSERT test_tab values(7, 2, 'G');
select * from test_tab;
Now, using 2 connections (C1 and C2) do the following:
-- C1
begin;
select * from test_tab where id = 5 for update;
+----+-------+------+
| ID | Value | Name |
+----+-------+------+
| 5 | 2 | E |
+----+-------+------+
1 row in set (0.00 sec)
-- C2
begin;
select * from test_tab where value >= 2 order by value, name for update;
# hangs ...
-- C1
update test_tab set value = 3 where id = 6;
commit;
Rows matched: 1 Changed: 1 Warnings: 0
-- C2
+----+-------+------+
| ID | Value | Name |
+----+-------+------+
| 4 | 2 | D |
| 5 | 2 | E |
| 6 | 3 | F |
| 7 | 2 | G |
+----+-------+------+
4 rows in set (3.85 sec)
Suggested fix:
These are the comments by Sergei Golubchik concerning this:
The simplest solution, I would say, to add a check for HA_READ_ORDER in
test_if_order_by_key(). It's a one-liner.
But in this case you should be ready that index_flags() will be tested
for HA_READ_ORDER both when the table is opened and per query. And the
semantics of the call becomes a bit vague, I don't like it :(
Removing index_flags() call from open_binary_frm() is much more
involving, though.
