Debian Codegraph @

mysql-test/suite/innodb/r/end_range_check.result

raw
1 # Bug #23481444 OPTIMISER CALL ROW_SEARCH_MVCC() AND READ
2 # THE INDEX APPLIED BY UNCOMMITTED ROWS.
3 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
4 f2 INT NOT NULL, f3 INT NOT NULL,
5 KEY(f2, f3))ENGINE=INNODB;
6 CREATE PROCEDURE populate_t1(IN BASE INT, IN SIZE INT)
7 BEGIN
8 DECLARE i INT DEFAULT BASE;
9 WHILE (i <= SIZE) DO
10 INSERT INTO t1(f2, f3) values (i, i);
11 SET i = i + 1;
12 END WHILE;
13 END|
14 CALL populate_t1(1, 10000);
15 INSERT INTO t1(f2, f3) VALUES(10000, 10000);
16 BEGIN;
17 CALL populate_t1(10001, 20000);
18 SELECT f2, f3 FROM t1 WHERE f2=10000 and f3 between 9999 and 10000;
19 f2 f3
20 10000 10000
21 10000 10000
22 DROP TABLE t1;
23 CREATE TABLE t1(f2 INT NOT NULL, f3 INT NOT NULL,
24 PRIMARY KEY(f2, f3))ENGINE=INNODB;
25 CALL populate_t1(1, 10000);
26 BEGIN;
27 CALL populate_t1(10001, 20000);
28 SELECT f2, f3 FROM t1 WHERE f2=10000 and f3 between 9999 and 10000;
29 f2 f3
30 10000 10000
31 DROP TABLE t1;
32 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
33 f2 INT NOT NULL, f3 INT NOT NULL,
34 KEY(f2), KEY(f3))ENGINE=INNODB;
35 CALL populate_t1(1, 10000);
36 BEGIN;
37 CALL populate_t1(10001, 20000);
38 SELECT count(*) FROM t1 FORCE INDEX(f2, f3) WHERE f2 < 10000 or f3 < 10000;
39 count(*)
40 9999
41 DROP TABLE t1;
42 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
43 f2 INT NOT NULL, f3 INT NOT NULL,
44 KEY(f2, f3))ENGINE=INNODB;
45 CALL populate_t1(1, 10000);
46 CALL populate_t1(100000, 100200);
47 BEGIN;
48 CALL populate_t1(10001, 20000);
49 SELECT COUNT(*) FROM t1
50 WHERE f2 BETWEEN 9999 AND 10000 OR f2 >= 100000;
51 COUNT(*)
52 203
53 COMMIT;
54 DROP TABLE t1;
55 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
56 f2 INT NOT NULL, f3 INT NOT NULL,
57 f4 INT AS (f2) VIRTUAL, key(f3, f4))
58 PARTITION BY KEY() PARTITIONS 2;
59 CALL populate_t1(1, 10000);
60 BEGIN;
61 CALL populate_t1(10001, 20000);
62 SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE f3 = 10000
63 AND f4 BETWEEN 9999 AND 10000;
64 f1 f2 f3 f4
65 10000 10000 10000 10000
66 COMMIT;
67 drop table t1;
68 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
69 f2 INT NOT NULL, f3 INT NOT NULL,
70 f4 INT AS (f2) VIRTUAL,
71 f5 INT AS (f2) VIRTUAL, key(f4, f5))
72 PARTITION BY KEY() PARTITIONS 2;
73 CALL populate_t1(1, 10000);
74 BEGIN;
75 CALL populate_t1(10001, 20000);
76 SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE f4 = 10000
77 AND f5 BETWEEN 9999 AND 10000;
78 f1 f2 f3 f4 f5
79 10000 10000 10000 10000 10000
80 COMMIT;
81 DROP TABLE t1;
82 DROP PROCEDURE populate_t1;
83 CREATE TABLE t2(f1 int not null, f2 char(100), f3 INT,
84 KEY(f2(20), f3))ENGINE=INNODB;
85 CREATE PROCEDURE populate_t2(IN BASE INT, IN SIZE INT)
86 BEGIN
87 DECLARE i INT DEFAULT BASE;
88 WHILE (i <= SIZE) DO
89 INSERT INTO t2(f1, f2, f3) values (i, repeat('a', 100), i);
90 SET i = i + 1;
91 END WHILE;
92 END|
93 CALL populate_t2(1, 10000);
94 BEGIN;
95 CALL populate_t2(10001, 20000);
96 SELECT /*+ NO_ICP(t2) */ f1 FROM t2 WHERE f2 = repeat('a', 100)
97 and f3 BETWEEN 10000 and 10001;
98 f1
99 10000
100 DROP PROCEDURE populate_t2;
101 DROP TABLE t2;