Debian Codegraph @

mysql-test/suite/innodb/t/end_range_check.test

raw
1 --source include/big_test.inc
2 --source include/not_valgrind.inc
3
4 --echo # Bug #23481444 OPTIMISER CALL ROW_SEARCH_MVCC() AND READ
5 --echo # THE INDEX APPLIED BY UNCOMMITTED ROWS.
6
7 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
8 f2 INT NOT NULL, f3 INT NOT NULL,
9 KEY(f2, f3))ENGINE=INNODB;
10
11 DELIMITER |;
12 CREATE PROCEDURE populate_t1(IN BASE INT, IN SIZE INT)
13 BEGIN
14 DECLARE i INT DEFAULT BASE;
15 WHILE (i <= SIZE) DO
16 INSERT INTO t1(f2, f3) values (i, i);
17 SET i = i + 1;
18 END WHILE;
19 END|
20 DELIMITER ;|
21 CALL populate_t1(1, 10000);
22 INSERT INTO t1(f2, f3) VALUES(10000, 10000);
23
24 BEGIN;
25 CALL populate_t1(10001, 20000);
26
27 connect (con1,localhost,root,,);
28 connection con1;
29 SELECT f2, f3 FROM t1 WHERE f2=10000 and f3 between 9999 and 10000;
30 connection default;
31 DROP TABLE t1;
32
33 CREATE TABLE t1(f2 INT NOT NULL, f3 INT NOT NULL,
34 PRIMARY KEY(f2, f3))ENGINE=INNODB;
35
36 CALL populate_t1(1, 10000);
37
38 BEGIN;
39 CALL populate_t1(10001, 20000);
40
41 connection con1;
42 SELECT f2, f3 FROM t1 WHERE f2=10000 and f3 between 9999 and 10000;
43 connection default;
44 DROP TABLE t1;
45
46 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
47 f2 INT NOT NULL, f3 INT NOT NULL,
48 KEY(f2), KEY(f3))ENGINE=INNODB;
49 CALL populate_t1(1, 10000);
50
51 BEGIN;
52 CALL populate_t1(10001, 20000);
53
54 connection con1;
55 SELECT count(*) FROM t1 FORCE INDEX(f2, f3) WHERE f2 < 10000 or f3 < 10000;
56 connection default;
57 DROP TABLE t1;
58
59 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
60 f2 INT NOT NULL, f3 INT NOT NULL,
61 KEY(f2, f3))ENGINE=INNODB;
62
63 CALL populate_t1(1, 10000);
64 CALL populate_t1(100000, 100200);
65
66 BEGIN;
67 CALL populate_t1(10001, 20000);
68
69 connection con1;
70 SELECT COUNT(*) FROM t1
71 WHERE f2 BETWEEN 9999 AND 10000 OR f2 >= 100000;
72 connection default;
73 COMMIT;
74
75 DROP TABLE t1;
76
77 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
78 f2 INT NOT NULL, f3 INT NOT NULL,
79 f4 INT AS (f2) VIRTUAL, key(f3, f4))
80 PARTITION BY KEY() PARTITIONS 2;
81
82 CALL populate_t1(1, 10000);
83 BEGIN;
84 CALL populate_t1(10001, 20000);
85
86 connection con1;
87 SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE f3 = 10000
88 AND f4 BETWEEN 9999 AND 10000;
89 connection default;
90 COMMIT;
91 drop table t1;
92
93 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
94 f2 INT NOT NULL, f3 INT NOT NULL,
95 f4 INT AS (f2) VIRTUAL,
96 f5 INT AS (f2) VIRTUAL, key(f4, f5))
97 PARTITION BY KEY() PARTITIONS 2;
98
99 CALL populate_t1(1, 10000);
100 BEGIN;
101 CALL populate_t1(10001, 20000);
102
103 connection con1;
104 SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE f4 = 10000
105 AND f5 BETWEEN 9999 AND 10000;
106 connection default;
107 COMMIT;
108 DROP TABLE t1;
109 DROP PROCEDURE populate_t1;
110
111 CREATE TABLE t2(f1 int not null, f2 char(100), f3 INT,
112 KEY(f2(20), f3))ENGINE=INNODB;
113 DELIMITER |;
114 CREATE PROCEDURE populate_t2(IN BASE INT, IN SIZE INT)
115 BEGIN
116 DECLARE i INT DEFAULT BASE;
117 WHILE (i <= SIZE) DO
118 INSERT INTO t2(f1, f2, f3) values (i, repeat('a', 100), i);
119 SET i = i + 1;
120 END WHILE;
121 END|
122 DELIMITER ;|
123
124 CALL populate_t2(1, 10000);
125
126 BEGIN;
127 CALL populate_t2(10001, 20000);
128
129 connection con1;
130 SELECT /*+ NO_ICP(t2) */ f1 FROM t2 WHERE f2 = repeat('a', 100)
131 and f3 BETWEEN 10000 and 10001;
132 disconnect con1;
133 connection default;
134 DROP PROCEDURE populate_t2;
135 DROP TABLE t2;