diff options
Diffstat (limited to 'mysql-test/r/subselect_mat.result')
| -rw-r--r-- | mysql-test/r/subselect_mat.result | 108 |
1 files changed, 90 insertions, 18 deletions
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index efc348a26ce..aa0ac73abd2 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1474,7 +1474,7 @@ EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan +2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); pk 2 @@ -2169,14 +2169,16 @@ drop table t1; CREATE TABLE t1 ( pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); -SELECT sq1.f2 FROM t1 AS sq1 -WHERE EXISTS ( SELECT * FROM t1 AS sq2 +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); f2 foo +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; EXPLAIN -SELECT sq1.f2 FROM t1 AS sq1 -WHERE EXISTS ( SELECT * FROM t1 AS sq2 +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY sq1 ALL NULL NULL NULL NULL 2 Using where @@ -2184,10 +2186,9 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 # this checks the result set above -set @save_optimizer_switch= @@optimizer_switch; set optimizer_switch= 'materialization=off,semijoin=off'; -SELECT sq1.f2 FROM t1 AS sq1 -WHERE EXISTS ( SELECT * FROM t1 AS sq2 +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); f2 foo @@ -2202,13 +2203,15 @@ CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; INSERT INTO t2 VALUES (8),(7),(1); CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); -SELECT * FROM t1 +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); f1 6 EXPLAIN EXTENDED -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -2221,9 +2224,8 @@ Warnings: Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`f1` = `test`.`t3`.`f3`)))) # this checks the result set above -set @save_optimizer_switch= @@optimizer_switch; set optimizer_switch= 'materialization=off,semijoin=off'; -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); f1 @@ -2237,10 +2239,10 @@ CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT); INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3); CREATE TABLE t2 (f2 INT); INSERT INTO t2 VALUES (1),(2),(3),(4),(5); -# t1.pk is always IN ( SELECT f2 FROM t2 ), -# so the IN condition should be true for every row, +# t1.pk is always IN ( SELECT f2 FROM t2 ), +# so the IN condition should be true for every row, # and thus COUNT(*) should always return 5 -SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; pk f1 sq 1 4 5 @@ -2249,7 +2251,7 @@ pk f1 sq 4 6 5 5 3 5 EXPLAIN EXTENDED -SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 @@ -2262,7 +2264,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache>< # this checks the result set above set @save_optimizer_switch= @@optimizer_switch; set optimizer_switch= 'materialization=off,semijoin=off'; -SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; pk f1 sq 1 4 5 @@ -2454,6 +2456,76 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); drop table t1,t2; drop view v1; # End of 5.5 tests +# +# MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int); +insert into t1 +select A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100 +from t0 A, t0 B, t0 C; +create table t2 (a int, b int, c int); +insert into t2 select A.a, A.a, A.a from t1 A; +insert into t2 select * from t2; +insert into t2 select * from t2; +create table t3 as select * from t2 limit 1; +# The testcase only makes sense if the following uses Materialization: +explain +select * from t1 where (a,b) in (select max(a),b from t2 group by b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 1000 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4000 Using temporary +flush status; +replace into t3 +select * from t1 where (a,b) in (select max(a),b from t2 group by b); +# Sequential reads: +# 1K is read from t1 +# 4K is read from t2 +# 1K groups is read from the tmp. table +# +# Lookups: +# 4K lookups in group by table +# 1K lookups in temp.table +# +# Writes: +# 2x 1K writes to temporary tables (grouping table and subquery materialization table +# +# The point is that neither counter should be in the millions (this +# will happen if Materialization is not used +show status where Variable_name like 'Handler_read%' or Variable_name like 'Handler_%write%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 5000 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 6003 +Handler_tmp_write 2000 +Handler_write 1000 +drop table t0,t1,t2,t3; +# +# MDEV-7971: Assertion `name != __null' failed in ACL_internal_schema_registry::lookup +# on 2nd execution os PS with multi-table update +# +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (f2 INT); +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (f3 INT); +INSERT INTO t3 VALUES (5),(6); +PREPARE stmt FROM ' + UPDATE t1, t2 + SET f1 = 5 + WHERE 8 IN ( SELECT MIN(f3) FROM t3 ) +'; +EXECUTE stmt; +EXECUTE stmt; +DROP TABLE t1,t2,t3; set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; @@ -2574,7 +2646,7 @@ EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition +2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); pk 2 |