77 lines
2.4 KiB
Text
77 lines
2.4 KiB
Text
--source include/not_hypergraph.inc
|
|
|
|
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;
|