EVOLUTION-MANAGER

Edit File: optimizer.test

# The server must support partitioning.
--source include/have_partition.inc

--disable_warnings
drop table if exists t1,t2;
--enable_warnings

--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;