[Commits] 52c0d6f1dfb: MDEV-20109: Optimizer ignores distinct key created for materialized...

revision-id: 52c0d6f1dfb0a53156018e1f601fcaae0d334d33 (mariadb-10.4.7-32-g52c0d6f1dfb) parent(s): 235cf969d21ba3406a9325d952fda47c589e58d6 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-08-29 14:00:25 +0300 message: MDEV-20109: Optimizer ignores distinct key created for materialized... Sj_materialization_picker::check_qep(): fix error in cost/fanout calculations: - for each join prefix, add #prefix_rows / TIME_FOR_COMPARE to the cost, like best_extension_by_limited_search does - Remove the fanout produced by the subquery tables. optimize_wo_join_buffering() (used by LooseScan and FirstMatch) - also add #prefix_rows / TIME_FOR_COMPARE to the cost of each prefix. --- mysql-test/main/analyze_stmt_privileges2.result | 142 ++++++++-------- mysql-test/main/cte_nonrecursive.result | 24 +-- mysql-test/main/group_by.result | 3 +- mysql-test/main/index_merge_myisam.result | 5 +- mysql-test/main/innodb_ext_key.result | 8 +- mysql-test/main/opt_trace.result | 214 +++++++++++++++--------- mysql-test/main/opt_tvc.result | 72 ++++---- mysql-test/main/subselect.result | 8 +- mysql-test/main/subselect3.result | 18 +- mysql-test/main/subselect3_jcl6.result | 20 +-- mysql-test/main/subselect_mat.result | 16 +- mysql-test/main/subselect_no_mat.result | 10 +- mysql-test/main/subselect_no_scache.result | 8 +- mysql-test/main/subselect_sj.result | 60 +++++-- mysql-test/main/subselect_sj.test | 25 +++ mysql-test/main/subselect_sj2.result | 23 +-- mysql-test/main/subselect_sj2_jcl6.result | 22 +-- mysql-test/main/subselect_sj2_mat.result | 51 +++--- mysql-test/main/subselect_sj_jcl6.result | 61 ++++--- mysql-test/main/subselect_sj_mat.result | 26 +-- mysql-test/main/table_value_constr.result | 24 +-- sql/opt_subselect.cc | 7 +- sql/sql_class.h | 5 +- sql/sql_select.cc | 15 +- 24 files changed, 507 insertions(+), 360 deletions(-) diff --git a/mysql-test/main/analyze_stmt_privileges2.result b/mysql-test/main/analyze_stmt_privileges2.result index cf38810b598..f269aaf540b 100644 --- a/mysql-test/main/analyze_stmt_privileges2.result +++ b/mysql-test/main/analyze_stmt_privileges2.result @@ -376,13 +376,13 @@ SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 0.00 100.00 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 #------------------------------------------------------------------------ # I/R/U/D/S on the inner view @@ -491,14 +491,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -598,14 +598,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant INSERT on the table @@ -1591,14 +1591,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything: SELECT access to the column `a` @@ -1708,14 +1708,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant SELECT, INSERT, UPDATE, DELETE on the table @@ -1940,14 +1940,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -2048,14 +2048,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL ######################################################################### # Inner view permission tests @@ -2697,14 +2697,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 14 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 14 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 14 14.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 14 14.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -2804,14 +2804,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant INSERT on the inner view @@ -3987,14 +3987,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 35 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 35 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -4094,14 +4094,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL ######################################################################### # Outer view permission tests @@ -4614,14 +4614,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant INSERT on the outer view @@ -5221,14 +5221,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 44 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 44 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL disconnect con1; connection default; DROP USER 'privtest'@localhost; diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index 2556fd4b06b..12a7e2ea789 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1159,9 +1159,9 @@ with cte as union (select a from t1 where a < 2); a -7 -5 4 +5 +7 1 prepare stmt from "with cte as (select a from t1 where a between 4 and 7 group by a) @@ -1170,15 +1170,15 @@ union (select a from t1 where a < 2)"; execute stmt; a -7 -5 4 +5 +7 1 execute stmt; a -7 -5 4 +5 +7 1 deallocate prepare stmt; with cte as @@ -1188,9 +1188,9 @@ union (select a from cte where exists( select a from t1 where cte.a=t1.a )); a 1 -7 -5 4 +5 +7 prepare stmt from "with cte as (select a from t1 where a between 4 and 7 group by a) (select a from t1 where a < 2) @@ -1199,15 +1199,15 @@ union execute stmt; a 1 -7 -5 4 +5 +7 execute stmt; a 1 -7 -5 4 +5 +7 deallocate prepare stmt; with cte as (select a from t1 where a between 4 and 7) diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result index 2eaec8a08b4..44ebce66b22 100644 --- a/mysql-test/main/group_by.result +++ b/mysql-test/main/group_by.result @@ -1608,7 +1608,8 @@ EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 4 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 144 SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1, t2; CREATE TABLE t1( diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 484ee626b98..7e57c916d04 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -558,8 +558,9 @@ where exists (select 1 from t2, t3 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3); FirstMatch(t1) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3) select * from t1 where exists (select 1 from t2, t3 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index b4572174fcb..2ec3a9ae951 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -732,8 +732,8 @@ SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index a,b b 7 NULL 10 Using index -1 PRIMARY t1 ref b b 3 test.t.b 2 Using index -1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; FirstMatch(t) +1 PRIMARY t1 ref b b 3 test.t.b 2 Using index; Start temporary +1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); @@ -745,8 +745,8 @@ SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index a,b b 7 NULL 10 Using index -1 PRIMARY t1 ref b b 3 test.t.b 2 Using index -1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; FirstMatch(t) +1 PRIMARY t1 ref b b 3 test.t.b 2 Using index; Start temporary +1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 518da2888cc..5dfe3241862 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -4308,11 +4308,11 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 FirstMatch(t_outer_1); Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 End temporary; Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Start temporary; Using join buffer (incremental, BNL join) 1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (incremental, BNL join) -1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) -1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; FirstMatch(t_outer_2); Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; End temporary; Using join buffer (incremental, BNL join) select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and @@ -4580,7 +4580,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 27, - "cost_for_plan": 34.174, + "cost_for_plan": 44.641, "rest_of_plan": [ { "plan_prefix": [ @@ -4601,7 +4601,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 81, - "cost_for_plan": 52.379, + "cost_for_plan": 62.846, "rest_of_plan": [ { "plan_prefix": [ @@ -4623,7 +4623,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 729, - "cost_for_plan": 200.19 + "cost_for_plan": 210.66 } ] }, @@ -4646,7 +4646,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 243, - "cost_for_plan": 84.79, + "cost_for_plan": 95.256, "pruned_by_heuristic": true } ] @@ -4665,7 +4665,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 9, - "cost_for_plan": 30.564, + "cost_for_plan": 41.031, "rest_of_plan": [ { "plan_prefix": [ @@ -4686,7 +4686,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 81, - "cost_for_plan": 48.779, + "cost_for_plan": 59.246, "rest_of_plan": [ { "plan_prefix": [ @@ -4708,8 +4708,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 729, - "cost_for_plan": 196.59, - "pruned_by_cost": true + "cost_for_plan": 207.06 } ] }, @@ -4732,7 +4731,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 81, - "cost_for_plan": 48.779, + "cost_for_plan": 59.246, "pruned_by_heuristic": true } ] @@ -4751,7 +4750,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 27, - "cost_for_plan": 34.174, + "cost_for_plan": 44.641, "pruned_by_heuristic": true } ] @@ -4787,7 +4786,76 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 729, "cost_for_plan": 172.44, - "pruned_by_cost": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2" + ], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rows_for_plan": 81, + "cost_for_plan": 304.05, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rows_for_plan": 729, + "cost_for_plan": 451.86, + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rows_for_plan": 243, + "cost_for_plan": 336.46, + "pruned_by_cost": true + } + ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], @@ -4866,7 +4934,52 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 729, "cost_for_plan": 172.44, - "pruned_by_heuristic": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_3" + ], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rows_for_plan": 2187, + "cost_for_plan": 611.85, + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_3" + ], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rows_for_plan": 6561, + "cost_for_plan": 1486.7, + "pruned_by_cost": true + } + ] } ] }, @@ -5303,70 +5416,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "fix_semijoin_strategies_for_picked_join_order": [ - { - "semi_join_strategy": "firstmatch", - "join_order": [ - { - "table": "t_inner_4" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 162.42, - "chosen": true - } - ] - }, - { - "table": "t_inner_3" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 489.74, - "chosen": true - } - ] - } - ] - }, - { - "semi_join_strategy": "firstmatch", - "join_order": [ - { - "table": "t_inner_1" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 18.046, - "chosen": true - } - ] - }, - { - "table": "t_inner_2" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 54.415, - "chosen": true - } - ] - } - ] - } - ] + "fix_semijoin_strategies_for_picked_join_order": [] }, { "attaching_conditions_to_tables": { @@ -5386,11 +5436,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "attached": null }, { - "table": "t_outer_2", + "table": "t_inner_4", "attached": null }, { - "table": "t_inner_4", + "table": "t_outer_2", "attached": null }, { diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index d503dab4d99..5329a9f64be 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -46,12 +46,12 @@ a b 2 5 explain extended select * from t1 where a in (1,2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from t1 where a in ( @@ -59,12 +59,12 @@ select * from (values (1),(2)) as tvc_0 ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # AND-condition with IN-predicates in WHERE-part select * from t1 where a in (1,2) and @@ -90,15 +90,15 @@ explain extended select * from t1 where a in (1,2) and b in (1,5); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where 1 explain extended select * from t1 where a in ( @@ -111,15 +111,15 @@ select * from (values (1),(5)) as tvc_1 ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where 1 # subquery with IN-predicate select * from t1 where a in @@ -206,12 +206,12 @@ from t1 where a in (1,2) ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from ( select * @@ -224,12 +224,12 @@ as tvc_0 ) ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # non-recursive CTE with IN-predicate with tvc_0 as ( @@ -265,12 +265,12 @@ where a in (1,2) ) select * from tvc_0; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from ( select * @@ -283,12 +283,12 @@ as tvc_0 ) ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # VIEW with IN-predicate create view v1 as select * @@ -316,20 +316,20 @@ a b 2 5 explain extended select * from v1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from v2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 drop view v1,v2; # subselect defined by derived table with IN-predicate select * from t1 @@ -519,12 +519,12 @@ a b 1 2 explain extended select * from t1 where (a,b) in ((1,2),(3,4)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` and `test`.`t1`.`b` = `tvc_0`.`_col_2` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where 1 set @@in_predicate_conversion_threshold= 2; # trasformation works for the one IN predicate and doesn't work for the other set @@in_predicate_conversion_threshold= 5; @@ -538,12 +538,12 @@ explain extended select * from t2 where (a,b) in ((1,2),(8,9)) and (a,c) in ((1,3),(8,0),(5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`_col_1` and `test`.`t2`.`c` = `tvc_0`.`_col_2` and (`tvc_0`.`_col_1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where (`test`.`t2`.`a`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) set @@in_predicate_conversion_threshold= 2; # # mdev-14281: conversion of NOT IN predicate into subquery predicate diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index fc0049db997..5e446a171fe 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -5703,8 +5703,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); @@ -5714,8 +5714,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 index idx idx 5 NULL 3 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result index ca151daf146..3c33182b3ef 100644 --- a/mysql-test/main/subselect3.result +++ b/mysql-test/main/subselect3.result @@ -1139,8 +1139,8 @@ create table t3 (a int); insert into t3 select A.a + 10*B.a from t0 A, t0 B; explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) create table t4 (pk int primary key); insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 @@ -1276,12 +1276,12 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; create table t2 as select * from t1; explain select * from t2 where a in (select b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) explain select * from t2 where (b,a) in (select a,b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) drop table t1,t2; set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int, b int); @@ -1339,9 +1339,9 @@ insert into t2 select * from t2; explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where -1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where -1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t1) +1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join) drop table t0,t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result index adb3ec80394..69f720a95b7 100644 --- a/mysql-test/main/subselect3_jcl6.result +++ b/mysql-test/main/subselect3_jcl6.result @@ -1149,8 +1149,8 @@ create table t3 (a int); insert into t3 select A.a + 10*B.a from t0 A, t0 B; explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) create table t4 (pk int primary key); insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 @@ -1286,12 +1286,12 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; create table t2 as select * from t1; explain select * from t2 where a in (select b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) explain select * from t2 where (b,a) in (select a,b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) drop table t1,t2; set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int, b int); @@ -1348,10 +1348,10 @@ create table t2 as select a as a, a as b from t0 where a < 3; insert into t2 select * from t2; explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t1); Using join buffer (incremental, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY X hash_ALL NULL #hash#$hj 5 test.t1.a 6 Using where; Start temporary; Using join buffer (flat, BNLH join) +1 PRIMARY Y hash_ALL NULL #hash#$hj 5 test.t1.b 6 Using where; Using join buffer (incremental, BNLH join) +1 PRIMARY Z hash_ALL NULL #hash#$hj 5 test.t1.c 6 Using where; End temporary; Using join buffer (incremental, BNLH join) drop table t0,t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 4e03d9663ef..ee680d40b75 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -1510,8 +1510,8 @@ set @@optimizer_switch=@optimizer_switch_local_default; SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) @@ -2437,8 +2437,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 9 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2450,8 +2450,8 @@ alter table t1 add key(id); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2507,8 +2507,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 4ebf04b1f55..ecd973da09d 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -5683,9 +5683,9 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 -1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using join buffer (flat, BNL join) -1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(ot4) +1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) +1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join) DROP TABLE IF EXISTS ot1, ot4, it2, it3; # # Bug#729039: NULL keys used to evaluate subquery @@ -5712,8 +5712,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index idx idx 5 NULL 3 Using index; LooseScan -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 2 Using index; FirstMatch(t1) DROP TABLE t1,t2; # # BUG#752992: Wrong results for a subquery with 'semijoin=on' diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 71d61e797f3..318ad02d473 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -5709,8 +5709,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); @@ -5720,8 +5720,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 index idx idx 5 NULL 3 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index 98143246673..3bd08e303b8 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -729,8 +729,8 @@ SELECT int_key FROM ot1 WHERE int_nokey IN (SELECT it2.int_key FROM it1 LEFT JOIN it2 ON it2.datetime_key); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 -1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it1 index NULL int_key 4 NULL 2 Using index 2 MATERIALIZED it2 ALL int_key,datetime_key NULL NULL NULL 20 Using where DROP TABLE ot1, it1, it2; @@ -972,11 +972,11 @@ SELECT `varchar_key` , `varchar_nokey` FROM t1 WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 15 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and `test`.`t2`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) +Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) SELECT varchar_nokey FROM t2 WHERE ( `varchar_nokey` , `varchar_nokey` ) IN ( @@ -1246,8 +1246,8 @@ INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); EXPLAIN SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; LooseScan -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); i 1 @@ -1757,8 +1757,8 @@ insert into t3 values('three'),( 'four'); insert into t3 values('three'),( 'four'); explain select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); @@ -1991,12 +1991,13 @@ CREATE VIEW v4 AS SELECT DISTINCT f2 FROM t4 ; explain extended SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -1 PRIMARY t4 ref f2 f2 5 test.t2.f3 2 100.00 Using index; FirstMatch(t2) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t4 index f2 f2 5 NULL 2 100.00 Using index Warnings: -Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f2` = `test`.`t2`.`f3` and `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); f1 f2 f3 f3 2 0 0 0 @@ -2495,8 +2496,8 @@ WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 -1 PRIMARY t1 ref a a 5 const 1 Using index -1 PRIMARY t2 ref a a 5 func 1 Using index +1 PRIMARY t2 ref a a 5 const 1 Using index +1 PRIMARY t1 ref a a 5 func 1 Using index 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 0 SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); @@ -2706,8 +2707,8 @@ a 19 explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) drop table t0,t1,t3; set optimizer_switch= @tmp_923246; # @@ -2923,8 +2924,8 @@ WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where -1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); FirstMatch(t2) +1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where; Start temporary +1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary SELECT * FROM t2 WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1 @@ -3264,4 +3265,27 @@ create table t2 (a2 varchar(25)) ; insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); drop table t1,t2; # End of 5.5 test +# +# MDEV-20109: Optimizer ignores distinct key created for materialized +# semi-join subquery when searching for best execution plan +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; +create table t3 (a int); +create table t4 (a int); +insert into t3 select A.a +1000*B.a from t2 A, t1 B; +insert into t4 select floor(rand()*1000) from t2 limit 500; +# The following must not use this query plan that does a cross join: +# | 1 | PRIMARY | <subquery2> | ALL | distinct_key | ... | 500 | | +# | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | +# +# Instead, it should use eq_ref on the materialized table. +explain select * from t3 where a in (select a from t4 group by a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 10000 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 500 +drop table t1, t2, t3, t4; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test index b693f7b5b93..a2d6c7e427c 100644 --- a/mysql-test/main/subselect_sj.test +++ b/mysql-test/main/subselect_sj.test @@ -2940,5 +2940,30 @@ drop table t1,t2; --echo # End of 5.5 test +--echo # +--echo # MDEV-20109: Optimizer ignores distinct key created for materialized +--echo # semi-join subquery when searching for best execution plan +--echo # +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; + +create table t3 (a int); +create table t4 (a int); +insert into t3 select A.a +1000*B.a from t2 A, t1 B; +insert into t4 select floor(rand()*1000) from t2 limit 500; + +--echo # The following must not use this query plan that does a cross join: +--echo # | 1 | PRIMARY | <subquery2> | ALL | distinct_key | ... | 500 | | +--echo # | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | +--echo # +--echo # Instead, it should use eq_ref on the materialized table. + +explain select * from t3 where a in (select a from t4 group by a); + +drop table t1, t2, t3, t4; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result index a127c18280e..31453801220 100644 --- a/mysql-test/main/subselect_sj2.result +++ b/mysql-test/main/subselect_sj2.result @@ -131,8 +131,9 @@ set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY t2 ref b b 5 test.t1.a 1 Using index; FirstMatch(t1) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 index b b 5 NULL 20 Using index select * from t1; a b 1 1 @@ -159,8 +160,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 32 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -227,8 +228,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 52 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -730,8 +731,9 @@ alter table t3 add primary key(id), add key(a); The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index -1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; FirstMatch(t2) +1 PRIMARY t2 index a a 5 NULL 1000 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 @@ -757,9 +759,10 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t2) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch((sj-nest)) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 drop table t2, t3; # # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result index 56c11e8c9af..3321ba221f3 100644 --- a/mysql-test/main/subselect_sj2_jcl6.result +++ b/mysql-test/main/subselect_sj2_jcl6.result @@ -171,9 +171,9 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot hash_ALL NULL #hash#$hj 5 test.it.a 32 Using where; Using join buffer (flat, BNLH join) -2 MATERIALIZED it ALL NULL NULL NULL NULL 22 Using where +1 PRIMARY ot ALL NULL NULL NULL NULL 32 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -239,9 +239,9 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot hash_ALL NULL #hash#$hj 5 test.it.a 52 Using where; Using join buffer (flat, BNLH join) -2 MATERIALIZED it ALL NULL NULL NULL NULL 22 Using where +1 PRIMARY ot ALL NULL NULL NULL NULL 52 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -1410,9 +1410,10 @@ SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b WHERE c IN (SELECT t4.b FROM t4 JOIN t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) -1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t3); Using join buffer (incremental, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t1 ref b b 4 test.t3.b 1 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b WHERE c IN (SELECT t4.b FROM t4 JOIN t2); b c @@ -1438,9 +1439,10 @@ EXPLAIN SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t4 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.b 1 Using where; FirstMatch(t2) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 1 Using where +2 MATERIALIZED t3 eq_ref PRIMARY PRIMARY 4 test.t4.b 1 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); pk a b 1 6 8 diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 73f682755da..7245e74b242 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -133,8 +133,9 @@ set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY t2 ref b b 5 test.t1.a 1 Using index; FirstMatch(t1) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 index b b 5 NULL 20 Using index select * from t1; a b 1 1 @@ -161,8 +162,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 32 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -229,8 +230,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 52 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -732,8 +733,9 @@ alter table t3 add primary key(id), add key(a); The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index -1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; FirstMatch(t2) +1 PRIMARY t2 index a a 5 NULL 1000 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 @@ -759,9 +761,10 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t2) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch((sj-nest)) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 drop table t2, t3; # # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 @@ -1638,12 +1641,12 @@ set optimizer_switch='materialization=on,semijoin=on'; EXPLAIN EXTENDED SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.i2 5 100.00 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); i1 7 @@ -1651,12 +1654,12 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.i2 5 100.00 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0 +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0 SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); i1 @@ -1748,8 +1751,8 @@ OR ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index PRIMARY PRIMARY 4 NULL 13 Using where; Using index -2 MATERIALIZED <subquery3> ALL distinct_key NULL NULL NULL 8 -2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 +2 MATERIALIZED <subquery3> eq_ref distinct_key distinct_key 67 func 1 3 MATERIALIZED B range PRIMARY PRIMARY 4 NULL 8 Using where SELECT SQL_NO_CACHE t.id FROM t1 t @@ -1914,18 +1917,16 @@ AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 12 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t2_2.id_product 1 Using where; Using index -1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t2_3 ref id_t2,id_product id_product 5 test.t3.id_product 44 Using index condition; Using where; Start temporary; End temporary +1 PRIMARY t2_5 ref id_t2,id_product id_product 5 test.t3.id_product 44 Using index condition; Using where; Start temporary; End temporary 1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t3.id_product,const 1 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where -1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using where; Using index; Using join buffer (flat, BNL join) 3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 Using where -5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where -6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 31 Using index condition; Using where 2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 51 -4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 33 Using index condition; Using where +5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where set optimizer_switch='rowid_filter=default'; drop table t1,t2,t3,t4,t5; set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index acfafde6d7e..711ae32a931 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -742,8 +742,8 @@ SELECT int_key FROM ot1 WHERE int_nokey IN (SELECT it2.int_key FROM it1 LEFT JOIN it2 ON it2.datetime_key); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 -1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it1 index NULL int_key 4 NULL 2 Using index 2 MATERIALIZED it2 ALL int_key,datetime_key NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) DROP TABLE ot1, it1, it2; @@ -985,11 +985,11 @@ SELECT `varchar_key` , `varchar_nokey` FROM t1 WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 15 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and `test`.`t2`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) +Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) SELECT varchar_nokey FROM t2 WHERE ( `varchar_nokey` , `varchar_nokey` ) IN ( @@ -1259,8 +1259,8 @@ INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); EXPLAIN SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; LooseScan -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); i 1 @@ -1770,8 +1770,8 @@ insert into t3 values('three'),( 'four'); insert into t3 values('three'),( 'four'); explain select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); @@ -2509,8 +2509,8 @@ WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 -1 PRIMARY t1 ref a a 5 const 1 Using index -1 PRIMARY t2 ref a a 5 func 1 Using index +1 PRIMARY t2 ref a a 5 const 1 Using index +1 PRIMARY t1 ref a a 5 func 1 Using index 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 0 SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); @@ -2720,8 +2720,8 @@ a 19 explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) drop table t0,t1,t3; set optimizer_switch= @tmp_923246; # @@ -2937,8 +2937,8 @@ WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where -1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); FirstMatch(t2) +1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary SELECT * FROM t2 WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1 @@ -3278,6 +3278,29 @@ create table t2 (a2 varchar(25)) ; insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); drop table t1,t2; # End of 5.5 test +# +# MDEV-20109: Optimizer ignores distinct key created for materialized +# semi-join subquery when searching for best execution plan +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; +create table t3 (a int); +create table t4 (a int); +insert into t3 select A.a +1000*B.a from t2 A, t1 B; +insert into t4 select floor(rand()*1000) from t2 limit 500; +# The following must not use this query plan that does a cross join: +# | 1 | PRIMARY | <subquery2> | ALL | distinct_key | ... | 500 | | +# | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | +# +# Instead, it should use eq_ref on the materialized table. +explain select * from t3 where a in (select a from t4 group by a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 10000 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 500 +drop table t1, t2, t3, t4; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off @@ -3485,8 +3508,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) @@ -3499,8 +3522,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index 203dbee6374..432f6c648fc 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -491,15 +491,15 @@ where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and a1 = c1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 4 100.00 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 4 99.22 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 99.22 Using where 4 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 99.22 Using where 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 99.22 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and `test`.`t3`.`c2` = `test`.`t3`.`c2` and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where `test`.`t1`.`a1` > '0' and <cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1` and <cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2` union /* select#3 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where `test`.`t2`.`b1` < '9' and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1` and <cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))) and `test`.`t3`.`c2` > '0' +Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where `test`.`t1`.`a1` > '0' and <cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1` and <cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2` union /* select#3 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where `test`.`t2`.`b1` < '9' and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1` and <cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))) and `test`.`t3`.`c2` > '0' select * from t1, t3 where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and (c1, c2) in (select c1, c2 from t3 @@ -1132,11 +1132,11 @@ insert into t3 values (30); explain extended select a from t1 where a in (select c from t2 where d >= 20); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 6 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t2`.`c` and `test`.`t2`.`d` >= 20 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`d` >= 20 select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1543,8 +1543,8 @@ set @@optimizer_switch=@optimizer_switch_local_default; SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) @@ -2473,8 +2473,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 9 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2486,8 +2486,8 @@ alter table t1 add key(id); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2543,8 +2543,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 51198ea47d6..a9b085fe31e 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -743,21 +743,21 @@ a b explain extended select * from t1 where a in (values (1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a in (values (1) union select 2); @@ -978,21 +978,21 @@ a b explain extended select * from t1 where a = any (values (1),(2)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a = any (values (1) union select 2); diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 599642b3a26..fd3ac1e9155 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2456,7 +2456,7 @@ bool optimize_semijoin_nests(JOIN *join, table_map all_table_map) &subjoin_out_rows); sjm->materialization_cost.convert_from_cost(subjoin_read_time); - sjm->rows= subjoin_out_rows; + sjm->rows_with_duplicates= sjm->rows= subjoin_out_rows; // Don't use the following list because it has "stale" items. use // ref_pointer_array instead: @@ -3082,11 +3082,14 @@ bool Sj_materialization_picker::check_qep(JOIN *join, disable_jbuf, prefix_rec_count, &curpos, &dummy); prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read); prefix_cost= COST_ADD(prefix_cost, curpos.read_time); + prefix_cost= COST_ADD(prefix_cost, + prefix_rec_count / (double) TIME_FOR_COMPARE); + //TODO: take into account join condition selectivity here } *strategy= SJ_OPT_MATERIALIZE_SCAN; *read_time= prefix_cost; - *record_count= prefix_rec_count; + *record_count= prefix_rec_count / mat_info->rows_with_duplicates; *handled_fanout= mat_nest->sj_inner_tables; return TRUE; } diff --git a/sql/sql_class.h b/sql/sql_class.h index 2b46fc69365..5ffc8bde269 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -5995,7 +5995,10 @@ class SJ_MATERIALIZATION_INFO : public Sql_alloc uint tables; /* Number of tables in the sj-nest */ - /* Expected #rows in the materialized table */ + /* Number of rows in the materialized table, before the de-duplication */ + double rows_with_duplicates; + + /* Expected #rows in the materialized table, after de-duplication */ double rows; /* diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e79e96bf210..fd34842d37a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8816,6 +8816,7 @@ void JOIN::get_prefix_cost_and_fanout(uint n_tables, record_count= COST_MULT(record_count, best_positions[i].records_read); read_time= COST_ADD(read_time, best_positions[i].read_time); } + /* TODO: Take into account condition selectivities here */ } *read_time_arg= read_time;// + record_count / TIME_FOR_COMPARE; *record_count_arg= record_count; @@ -16703,10 +16704,20 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, reopt_remaining_tables &= ~rs->table->map; rec_count= COST_MULT(rec_count, pos.records_read); cost= COST_ADD(cost, pos.read_time); - - + cost= COST_ADD(cost, rec_count / (double) TIME_FOR_COMPARE); + //TODO: take into account join condition selectivity here + double pushdown_cond_selectivity= 1.0; + table_map real_table_bit= rs->table->map; + if (join->thd->variables.optimizer_use_condition_selectivity > 1) + { + pushdown_cond_selectivity= table_cond_selectivity(join, i, rs, + reopt_remaining_tables & + ~real_table_bit); + } + (*outer_rec_count) *= pushdown_cond_selectivity; if (!rs->emb_sj_nest) *outer_rec_count= COST_MULT(*outer_rec_count, pos.records_read); + } join->cur_sj_inner_tables= save_cur_sj_inner_tables;