diff options
Diffstat (limited to 'mysql-test/main/myisam_icp.result')
| -rw-r--r-- | mysql-test/main/myisam_icp.result | 47 |
1 files changed, 39 insertions, 8 deletions
diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index 2048205528d..0fdc3f11627 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -407,7 +407,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 4 Using where +1 SIMPLE t1 range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL 3 (50%) Using index condition; Using where; Rowid-ordered scan; Using filesort; Using rowid filter DROP TABLE t1; # # @@ -505,8 +505,8 @@ SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); @@ -588,6 +588,12 @@ i1 INTEGER NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t2 VALUES (4,1); +ANALYZE TABLE t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK EXPLAIN SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 WHERE t2.pk <> t1.d1 AND t2.pk = 4; @@ -657,7 +663,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; @@ -793,6 +799,12 @@ INSERT INTO t2 (g,h) VALUES (0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'), (3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'), (7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'); +ANALYZE TABLE t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status Table is already up to date SET @save_optimize_switch=@@optimizer_switch; SET optimizer_switch='materialization=on'; EXPLAIN @@ -802,7 +814,7 @@ AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) OR a = 0 AND h < 'z' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where -1 PRIMARY t2 ref g g 5 test.t.c 19 Using where +1 PRIMARY t2 ref g g 5 test.t.c 18 Using where 2 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 64 Using where; Using index 2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where SELECT COUNT(*) FROM t1 AS t, t2 @@ -913,7 +925,7 @@ SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx idx 4 const 1 Using where; Using temporary; Using filesort +1 SIMPLE t1 ref idx idx 4 const 2 Using where; Using temporary; Using filesort SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; a MIN(c) 5 y @@ -921,7 +933,7 @@ SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx idx 4 const 1 Using index condition; Using where; Using temporary; Using filesort +1 SIMPLE t1 ref idx idx 4 const 2 Using index condition; Using where; Using temporary; Using filesort SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; a MIN(c) 5 y @@ -976,11 +988,30 @@ set optimizer_switch='mrr=off'; # Must not use ICP: explain select * from t1 where a between 5 and 8 order by a desc, col desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 39 Using where +1 SIMPLE t1 range a a 5 NULL 40 Using where set optimizer_switch= @tmp_10000051; # Must not use ICP: explain select * from t1 where a=3 and col > 500 order by a desc, col desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 10 NULL 10 Using where drop table t0, t1; +# +# MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE +# +create table ten(a int); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k(a int); +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; +create table t1 (key1 int not null, filler char(100)); +insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B; +alter table t1 add key(key1); +explain select * from t1 where key1 < 3 or key1 > 99999; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range key1 key1 4 NULL 22 Using index condition; Rowid-ordered scan +select * from t1 where key1 < 3 or key1 > 99999; +key1 filler +0 filler-data +1 filler-data +2 filler-data +drop table ten,one_k,t1; set optimizer_switch=@myisam_icp_tmp; |