Debian Codegraph @

mysql-test/suite/parts/t/optimizer.test

raw
1 # The server must support partitioning.
2 --source include/have_partition.inc
3
4 --disable_warnings
5 drop table if exists t1,t2;
6 --enable_warnings
7
8 --echo #
9 --echo # Bug#50939: Loose Index Scan unduly relies on engine to remember range
10 --echo # endpoints
11 --echo #
12 CREATE TABLE t1 (
13 a INT,
14 b INT,
15 KEY ( a, b )
16 ) PARTITION BY HASH (a) PARTITIONS 1;
17
18 CREATE TABLE t2 (
19 a INT,
20 b INT,
21 KEY ( a, b )
22 );
23
24 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
25
26 INSERT INTO t1 SELECT a + 5, b + 5 FROM t1;
27 INSERT INTO t1 SELECT a + 10, b + 10 FROM t1;
28 INSERT INTO t1 SELECT a + 20, b + 20 FROM t1;
29 INSERT INTO t1 SELECT a + 40, b + 40 FROM t1;
30
31 INSERT INTO t2 SELECT * FROM t1;
32
33 --echo # plans should be identical
34 EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a;
35 EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a;
36
37 --disable_ps2_protocol
38 FLUSH status;
39 SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a;
40 --echo # Should be no more than 4 reads.
41 SHOW status LIKE 'handler_read_key';
42
43 FLUSH status;
44 SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a;
45 --echo # Should be no more than 4 reads.
46 SHOW status LIKE 'handler_read_key';
47 --enable_ps2_protocol
48
49 DROP TABLE t1, t2;