1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
let $engine=innodb;
--source include/skip_scan_test.inc
--echo #
--echo # Bug#28089360 WL#11322: SIG11 AT QEP_SHARED_OWNER::JOIN | SQL/SQL_OPT_EXEC_SHARED.H:458
--echo #
CREATE TABLE t1 (f1 INT(11), f2 VARCHAR(1), KEY k1 (f2, f1));
INSERT INTO t1 VALUES (-682212662,'c'), (-1974921822,'C'), (1260604936,'9');
CREATE TABLE t2 (f1 INT(11));
INSERT INTO t2 VALUES (824388284), (1186821161);
CREATE VIEW v1 AS select f1, f2 from t1;
DELETE FROM t2 WHERE (f1, f1) IN (SELECT f1,f2 FROM v1 WHERE f1 >= 2);
DROP VIEW v1;
DROP TABLE t1, t2;
--echo #
--echo # Bug #29602393 ASSERTION `BITMAP_IS_SET(KEY_INFO->TABLE->READ_SET,
--echo # KEY_INFO->KEY_PART.*FAILED
--echo #
CREATE TABLE t1(f1 INT, f2 VARCHAR(64) NOT NULL, PRIMARY KEY (f1))
PARTITION BY RANGE(f1)
(
PARTITION p1 VALUES LESS THAN (3),
PARTITION p2 VALUES LESS THAN (maxvalue)
);
INSERT INTO t1 VALUES (1, 'abcde'), (2, 'abcde'), (3, 'abcde');
CREATE INDEX idx1 ON t1(f2);
ANALYZE TABLE t1;
EXPLAIN SELECT /*+ SKIP_SCAN(t1 idx1) */ count(*) FROM t1 WHERE f1 <= 3;
SELECT /*+ SKIP_SCAN(t1 idx1) */ count(*) FROM t1 WHERE f1 <= 3;
DROP TABLE t1;
--echo #
--echo # Bug 33251616 - NO_SKIP_SCAN hint disables skip scan for all indexes
--echo #
CREATE TABLE t1(c1 INT, c2 INT, c3 INT, c4 INT);
CREATE UNIQUE INDEX i1_t1 ON t1(c1,c2,c3);
CREATE UNIQUE INDEX i2_t1 ON t1(c1,c2,c4);
INSERT INTO t1 VALUES (1,1,1,1), (1,1,2,2), (1,3,3,3), (1,4,4,4), (1,5,5,5),
(2,1,1,1), (2,2,2,2), (2,3,3,3), (2,4,4,4), (2,5,5,5);
INSERT INTO t1 SELECT c1, c2, c3+5, c4+10 FROM t1;
INSERT INTO t1 SELECT c1, c2, c3+10, c4+20 FROM t1;
INSERT INTO t1 SELECT c1, c2, c3+20, c4+40 FROM t1;
INSERT INTO t1 SELECT c1, c2, c3+40, c4+80 FROM t1;
ANALYZE TABLE t1;
EXPLAIN SELECT c1, c2 FROM t1 WHERE c2 > 40;
# the other index(i2_t1) should be used for skip_scan
EXPLAIN SELECT /*+NO_SKIP_SCAN(t1 i1_t1)*/ c1, c2 FROM t1 WHERE c2 > 40;
# index i1_t1 should be used for skip_scan
EXPLAIN SELECT /*+NO_SKIP_SCAN(t1 i2_t1)*/ c1, c2 FROM t1 WHERE c2 > 40;
# skip_scan should not be used for table t1
EXPLAIN SELECT /*+NO_SKIP_SCAN(t1)*/ c1, c2 FROM t1 WHERE c2 > 40;
# force skip_scan to use index i2_t1
EXPLAIN SELECT /*+SKIP_SCAN(t1 i2_t1)*/ c1, c2 FROM t1 WHERE c2 > 40;
SET OPTIMIZER_SWITCH = 'skip_scan=off';
# skip_scan should not be used
EXPLAIN SELECT c1, c2 FROM t1 WHERE c2 > 40;
DROP TABLE t1;
SET OPTIMIZER_SWITCH = default;
|
