diff options
Diffstat (limited to 'mysql-test/suite/maria/icp.result')
| -rw-r--r-- | mysql-test/suite/maria/icp.result | 30 |
1 files changed, 22 insertions, 8 deletions
diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 259078fb1ad..a421ba6d3ea 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -409,7 +409,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 PRIMARY 4 NULL 3 Using index condition; Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 4 Using index condition; Using where DROP TABLE t1; # # @@ -431,8 +431,8 @@ SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where -2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL # Using index 2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func # Using index condition +2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL # Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); pk i @@ -450,9 +450,10 @@ c1 INT NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); +insert into t1 select seq,seq from seq_100_to_110; EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 5 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 16 Using index condition; Rowid-ordered scan SET SESSION optimizer_switch='index_condition_pushdown=off'; SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); pk c1 @@ -460,6 +461,17 @@ pk c1 2 7 4 3 5 1 +100 100 +101 101 +102 102 +103 103 +104 104 +105 105 +106 106 +107 107 +108 108 +109 109 +110 110 DROP TABLE t1; set optimizer_switch= @save_optimizer_switch; # @@ -677,7 +689,6 @@ DROP TABLE t1; # CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); -insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); @@ -687,8 +698,8 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL # Using where; Using filesort -1 SIMPLE t2 ref a a 515 test.t1.a # Using where +1 SIMPLE t1 system PRIMARY NULL NULL NULL # +1 SIMPLE t2 ref a a 515 const # Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c @@ -698,8 +709,8 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL # Using where; Using filesort -1 SIMPLE t2 ref a a 515 test.t1.a # Using where +1 SIMPLE t1 system PRIMARY NULL NULL NULL # +1 SIMPLE t2 ref a a 515 const # Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c @@ -811,6 +822,8 @@ 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'; +set @save_optimizer_where_cost=@@optimizer_where_cost; +set @@optimizer_where_cost=1; EXPLAIN SELECT COUNT(*) FROM t1 AS t, t2 WHERE c = g @@ -834,6 +847,7 @@ OR a = 0 AND h < 'z' ); COUNT(*) 1478 SET optimizer_switch=@save_optimizer_switch; +set @@optimizer_where_cost=@save_optimizer_where_cost; DROP TABLE t1,t2; # check "Handler_pushed" status varuiables CREATE TABLE t1 ( |