diff options
Diffstat (limited to 'mysql-test/r/subselect_mat_cost_bugs.result')
| -rw-r--r-- | mysql-test/r/subselect_mat_cost_bugs.result | 24 |
1 files changed, 15 insertions, 9 deletions
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index 9ba318a58f1..96f904c722c 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -141,14 +141,14 @@ CREATE TABLE t3 ( f2 varchar(1)) ; EXPLAIN SELECT f2 FROM t3 WHERE ( SELECT MAX( pk ) FROM t1 WHERE EXISTS ( -SELECT DISTINCT f1 -FROM t2 +SELECT max(f1) +FROM t2 GROUP BY f1 ) ) IS NULL ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 SUBQUERY t1 system NULL NULL NULL NULL 1 -3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary +3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort drop table t1, t2, t3; # # LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed @@ -156,6 +156,8 @@ drop table t1, t2, t3; CREATE TABLE t2 ( f2 int(11)) ; CREATE TABLE t1 ( f3 int(11), KEY (f3)) ; INSERT INTO t1 VALUES (6),(4); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT * FROM (SELECT * FROM t2) AS a2 WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); @@ -171,6 +173,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 3 SUBQUERY t1 index NULL f3 5 NULL 2 Using index 2 DERIVED t2 ALL NULL NULL NULL NULL 2 +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2; # # LP BUG#715027 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed @@ -179,6 +182,8 @@ CREATE TABLE t1 ( f1 int(11), PRIMARY KEY (f1)) ; INSERT INTO t1 VALUES (28),(29); CREATE TABLE t2 ( f2 int(11), f3 int(11), f10 varchar(1)) ; INSERT INTO t2 VALUES (NULL,6,'f'),(4,2,'d'); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 @@ -206,6 +211,7 @@ field1 Warnings: Warning 1292 Truncated incorrect INTEGER value: 'f' Warning 1292 Truncated incorrect INTEGER value: 'd' +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2; # # LP BUG#718578 Yet another Assertion `!table || @@ -257,8 +263,8 @@ WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY alias1 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY alias2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED alias1 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED alias2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) SELECT * FROM t2 WHERE ( f12 ) IN ( SELECT alias2.f3 @@ -277,8 +283,8 @@ FROM t1 AS alias1, t1 AS alias2 WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY alias1 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY alias2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED alias1 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED alias2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) SELECT * FROM t2 WHERE ( f12 ) IN ( SELECT alias2.f3 @@ -383,14 +389,14 @@ set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_ explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); c1 c2 set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); c1 c2 drop table t1, t2; |