Bug#50939: Loose Index Scan unduly relies on engine to

remember range endpoints

The Loose Index Scan optimization keeps track of a sequence
of intervals. For the current interval it maintains the
current interval's endpoints. But the maximum endpoint was
not stored in the SQL layer; rather, it relied on the
storage engine to retain this value in-between reads. By
coincidence this holds for MyISAM and InnoDB. Not for the
partitioning engine, however.

Fixed by making the key values iterator 
(QUICK_RANGE_SELECT) keep track of the current maximum endpoint.
This is also more efficient as we save a call through the
handler API in case of open-ended intervals.

The code to calculate endpoints was extracted into 
separate methods in QUICK_RANGE_SELECT, and it was possible to
get rid of some code duplication as part of fix.

1 files changed, 41 insertions, 0 deletions

diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 5d5ad180f1a..2d2a6f75d73 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1313,4 +1313,45 @@ SELECT * FROM t1 FORCE INDEX (PRIMARY)
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#50939: Loose Index Scan unduly relies on engine to remember range 
+--echo # endpoints
+--echo #
+CREATE TABLE t1 (
+ a INT,
+ b INT,
+ KEY ( a, b )
+) PARTITION BY HASH (a) PARTITIONS 1;
+
+CREATE TABLE t2 (
+ a INT,
+ b INT,
+ KEY ( a, b )
+);
+
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+
+INSERT INTO t1 SELECT a +  5, b +  5 FROM t1;
+INSERT INTO t1 SELECT a + 10, b + 10 FROM t1;
+INSERT INTO t1 SELECT a + 20, b + 20 FROM t1;
+INSERT INTO t1 SELECT a + 40, b + 40 FROM t1;
+
+INSERT INTO t2 SELECT * FROM t1;
+
+--echo # plans should be identical
+EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a;
+EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a;
+
+FLUSH status;
+SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a;
+--echo # Should be no more than 4 reads.
+SHOW status LIKE 'handler_read_key';
+
+FLUSH status;
+SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a;
+--echo # Should be no more than 4 reads.
+SHOW status LIKE 'handler_read_key';
+
+DROP TABLE t1, t2;
+
 --echo End of 5.1 tests