diff options
| author | Monty <monty@mariadb.org> | 2022-05-31 17:36:32 +0300 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2022-06-18 15:23:13 +0200 |
| commit | 9e2c6592ec5b9343f581cea2493e006f879e4f29 (patch) | |
| tree | 3192deeed64bec8393c87bda6d0df8521aaaeab9 | |
| parent | 07a31de109f7aa5ea78f59f3fdae93a3141b899a (diff) | |
| download | mariadb-git-9e2c6592ec5b9343f581cea2493e006f879e4f29.tar.gz | |
Improve pruning in greedy_search by sorting tables during search
MDEV-28073 Slow query performance in MariaDB when using many tables
The faster we can find a good query plan, the more options we have for
finding and pruning (ignoring) bad plans.
This patch adds sorting of plans to best_extension_by_limited_search().
The plans, from best_access_path() are sorted according to the numbers
of found rows. This allows us to faster find 'good tables' and we are
thus able to eliminate 'bad plans' faster.
One side effect of this patch is that if two tables have equal cost,
the table that which was used earlier in the query is preferred.
This allows users to improve plans by reordering eq_ref tables in the
order they would like them to be uses.
Result changes caused by the patch:
- Traces are different as now we print the cost for using tables before
we start considering them in the plan.
- Table order are changed for some plans. In most cases this is because
the plans are equal and tables are in this case sorted according to
their usage in the original query.
- A few plans was changed as the optimizer was able to find a better
plan (that was pruned by the original code).
Other things:
- Added a new statistic variable: "optimizer_join_prefixes_check_calls",
which counts number of calls to best_extension_by_limited_search().
This can be used to check the prune efficiency in greedy_search().
- Added variable "JOIN_TAB::embedded_dependent" to be able to handle
XX IN (SELECT..) in the greedy_optimizer. The idea is that we
should prune a table if any of the tables in embedded_dependent is
not yet read.
- When using many tables in a query, there will be some additional
memory usage as we need to pre-allocate table of
table_count*table_count*sizeof(POSITION) objects (POSITION is 312
bytes for now) to hold the pre-calculated best_access_path()
information. This memory usage is offset by the expected
performance improvement when using many tables in a query.
- Removed the code from an earlier patch to keep the table order in
join->best_ref in the original order. This is not needed anymore as we
are now sorting the tables for each best_extension_by_limited_search()
call.
33 files changed, 2519 insertions, 1294 deletions
diff --git a/mysql-test/main/greedy_optimizer.result b/mysql-test/main/greedy_optimizer.result index d7c7e603c68..56309823741 100644 --- a/mysql-test/main/greedy_optimizer.result +++ b/mysql-test/main/greedy_optimizer.result @@ -155,9 +155,9 @@ Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) @@ -179,9 +179,9 @@ Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) @@ -237,9 +237,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join) 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value @@ -261,9 +261,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value @@ -311,9 +311,9 @@ Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) @@ -335,9 +335,9 @@ Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) @@ -389,9 +389,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join) 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value @@ -413,9 +413,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value @@ -467,9 +467,9 @@ Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) @@ -491,9 +491,9 @@ Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) @@ -543,9 +543,9 @@ Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) @@ -567,9 +567,9 @@ Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) @@ -619,9 +619,9 @@ Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) @@ -643,9 +643,9 @@ Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index 3025e3ec3f0..02e199bc58a 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -385,9 +385,9 @@ 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 t2 eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index 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); a diff --git a/mysql-test/main/join_nested.result b/mysql-test/main/join_nested.result index 5f26b03e0d1..856269ea793 100644 --- a/mysql-test/main/join_nested.result +++ b/mysql-test/main/join_nested.result @@ -964,13 +964,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where 1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`a` > 0 and `test`.`t4`.`a` > 0 and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`a` > 0)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t8`.`b` = `test`.`t9`.`b` or `test`.`t8`.`c` is null) INSERT INTO t8 VALUES (-3,12,0), (-1,14,0), (-5,15,0), (-1,11,0), (-4,13,0); @@ -1014,13 +1014,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where 1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`a` > 0 and `test`.`t4`.`a` > 0 and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t8`.`a` >= 0 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`a` > 0)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t8`.`b` = `test`.`t9`.`b` or `test`.`t8`.`c` is null) INSERT INTO t1 VALUES (-1,133,0), (-2,12,0), (-3,11,0), (-5,15,0); @@ -1065,13 +1065,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ref idx_a idx_a 5 const 2 100.00 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t1 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where 1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2 and `test`.`t1`.`a` > 0) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t8`.`b` = `test`.`t9`.`b` or `test`.`t8`.`c` is null) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -1111,20 +1111,20 @@ t0.b=t1.b AND a b a b a b a b a b a b a b a b a b a b 1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1 1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2 +1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1 -1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 -1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 -1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 -1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1 -1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2 -1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2 +1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 -1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2 +1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 +1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 +1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 -1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2 +1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1 +1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2 +1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2 SELECT t2.a,t2.b FROM t2; @@ -1744,9 +1744,9 @@ ON t4.carrier = t1.carrier; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index package_id package_id 5 NULL 45 Using where; Using index 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1 +1 SIMPLE t3 ref package_id package_id 5 test.t2.package_id 1 Using index 1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1 Using where 1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index -1 SIMPLE t3 ref package_id package_id 5 test.t2.package_id 1 Using index SELECT COUNT(*) FROM ((t2 JOIN t1 ON t2.package_id = t1.id) JOIN t3 ON t3.package_id = t1.id) diff --git a/mysql-test/main/join_nested.test b/mysql-test/main/join_nested.test index 864218371f4..f0c6a9b65e0 100644 --- a/mysql-test/main/join_nested.test +++ b/mysql-test/main/join_nested.test @@ -637,6 +637,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); +--sorted_result SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result index 5db6d030965..554ed84db76 100644 --- a/mysql-test/main/join_nested_jcl6.result +++ b/mysql-test/main/join_nested_jcl6.result @@ -973,13 +973,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`a` > 0 and `test`.`t4`.`a` > 0 and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`a` > 0 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t8`.`b` = `test`.`t9`.`b` or `test`.`t8`.`c` is null) INSERT INTO t8 VALUES (-3,12,0), (-1,14,0), (-5,15,0), (-1,11,0), (-4,13,0); @@ -1023,13 +1023,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`a` > 0 and `test`.`t4`.`a` > 0 and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t8`.`a` >= 0 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`a` > 0 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t8`.`b` = `test`.`t9`.`b` or `test`.`t8`.`c` is null) INSERT INTO t1 VALUES (-1,133,0), (-2,12,0), (-3,11,0), (-5,15,0); @@ -1074,13 +1074,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ref idx_a idx_a 5 const 2 100.00 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t1 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2 and `test`.`t1`.`a` > 0) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t8`.`b` = `test`.`t9`.`b` or `test`.`t8`.`c` is null) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -1118,14 +1118,16 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); a b a b a b a b a b a b a b a b a b a b -1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2 +1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1 +1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2 1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2 -1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 -1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 +1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2 +1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 +1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2 @@ -1133,8 +1135,6 @@ a b a b a b a b a b a b a b a b a b a b 1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2 -1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1 -1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2 SELECT t2.a,t2.b FROM t2; a b @@ -1753,9 +1753,9 @@ ON t4.carrier = t1.carrier; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index package_id package_id 5 NULL 45 Using where; Using index 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 ref package_id package_id 5 test.t2.package_id 1 Using index 1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1 Using where 1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index -1 SIMPLE t3 ref package_id package_id 5 test.t2.package_id 1 Using index SELECT COUNT(*) FROM ((t2 JOIN t1 ON t2.package_id = t1.id) JOIN t3 ON t3.package_id = t1.id) diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index 522b668e07b..58f24c236f9 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2009,10 +2009,10 @@ EXPLAIN EXTENDED SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t3 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 10 10.00 Using join buffer (flat, BNLH join) -1 SIMPLE t2 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t1.b 27 3.70 Using index; Using join buffer (incremental, BNLH join) +1 SIMPLE t2 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t1.b 27 3.70 Using index; Using join buffer (flat, BNLH join) +1 SIMPLE t3 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 10 10.00 Using join buffer (incremental, BNLH join) Warnings: -Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t2` join `test`.`t1` join `test`.`t3` where `test`.`t3`.`b` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t1`.`b` +Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t2` join `test`.`t1` join `test`.`t3` where `test`.`t2`.`b` = `test`.`t1`.`b` and `test`.`t3`.`b` = `test`.`t1`.`b` PREPARE stmt FROM 'SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b'; EXECUTE stmt; @@ -2644,9 +2644,9 @@ WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND t2.REVTYPE=2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where 1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where 1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where `test`.`t2`.`REVTYPE` = 2 and `test`.`t4`.`id` = `test`.`t3`.`person_id` and `test`.`t3`.`id` = `test`.`t2`.`profile_id` and `test`.`t1`.`id` = `test`.`t2`.`REV` and `test`.`t1`.`timestamp` < 1294664900039 and `test`.`t1`.`timestamp` > 1294644616416 SELECT * @@ -2669,9 +2669,9 @@ WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND t2.REVTYPE=2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where 1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where 1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t4` join `test`.`t3` join `test`.`t2` join `test`.`t1` where `test`.`t2`.`REVTYPE` = 2 and `test`.`t1`.`id` = `test`.`t2`.`REV` and `test`.`t3`.`id` = `test`.`t2`.`profile_id` and `test`.`t4`.`id` = `test`.`t3`.`person_id` and `test`.`t1`.`timestamp` < 1294664900039 and `test`.`t1`.`timestamp` > 1294644616416 SELECT * diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result index 6f87048cdc1..955e73c4c73 100644 --- a/mysql-test/main/join_outer_innodb.result +++ b/mysql-test/main/join_outer_innodb.result @@ -440,8 +440,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where 1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index -1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 +1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index 1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where @@ -462,8 +462,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where 1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index -1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 +1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index 1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index b579cfc6ac0..c3dd72e5988 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2016,10 +2016,10 @@ EXPLAIN EXTENDED SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t3 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 10 10.00 Using join buffer (flat, BNLH join) -1 SIMPLE t2 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t1.b 27 3.70 Using index; Using join buffer (incremental, BNLH join) +1 SIMPLE t2 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t1.b 27 3.70 Using index; Using join buffer (flat, BNLH join) +1 SIMPLE t3 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 10 10.00 Using join buffer (incremental, BNLH join) Warnings: -Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t2` join `test`.`t1` join `test`.`t3` where `test`.`t3`.`b` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t1`.`b` +Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t2` join `test`.`t1` join `test`.`t3` where `test`.`t2`.`b` = `test`.`t1`.`b` and `test`.`t3`.`b` = `test`.`t1`.`b` PREPARE stmt FROM 'SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b'; EXECUTE stmt; @@ -2651,9 +2651,9 @@ WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND t2.REVTYPE=2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where -1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where `test`.`t2`.`REVTYPE` = 2 and `test`.`t4`.`id` = `test`.`t3`.`person_id` and `test`.`t3`.`id` = `test`.`t2`.`profile_id` and `test`.`t1`.`id` = `test`.`t2`.`REV` and `test`.`t1`.`timestamp` < 1294664900039 and `test`.`t1`.`timestamp` > 1294644616416 SELECT * @@ -2676,9 +2676,9 @@ WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND t2.REVTYPE=2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where -1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t4` join `test`.`t3` join `test`.`t2` join `test`.`t1` where `test`.`t2`.`REVTYPE` = 2 and `test`.`t1`.`id` = `test`.`t2`.`REV` and `test`.`t3`.`id` = `test`.`t2`.`profile_id` and `test`.`t4`.`id` = `test`.`t3`.`person_id` and `test`.`t1`.`timestamp` < 1294664900039 and `test`.`t1`.`timestamp` > 1294644616416 SELECT * diff --git a/mysql-test/main/mysqld--help.test b/mysql-test/main/mysqld--help.test index 95ae49b5c89..ab2a61982da 100644 --- a/mysql-test/main/mysqld--help.test +++ b/mysql-test/main/mysqld--help.test @@ -38,7 +38,8 @@ perl; test-sql-discovery query-cache-info password-reuse-check query-response-time metadata-lock-info locales unix-socket wsrep file-key-management cracklib-password-check user-variables - thread-pool-groups thread-pool-queues thread-pool-stats thread-pool-waits/; + thread-pool-groups thread-pool-queues thread-pool-stats + thread-pool-waits hashicorp provider/; # And substitute the content some environment variables with their # names: diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 6f0d13441ff..9aa85c6bd08 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -143,7 +143,11 @@ select * from v1 { "cost": 2.204394531, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 1, "cost_for_plan": 2.404394531 } @@ -293,7 +297,11 @@ select * from (select * from t1 where t1.a=1)q { "cost": 2.204394531, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 1, "cost_for_plan": 2.404394531 } @@ -449,7 +457,11 @@ select * from v2 { "cost": 2.204394531, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 1, "cost_for_plan": 2.404394531, "cost_for_sorting": 1 @@ -520,7 +532,11 @@ select * from v2 { "cost": 2, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "<derived2>", "rows_for_plan": 2, "cost_for_plan": 2.4 } @@ -656,7 +672,11 @@ explain select * from v2 { "cost": 2.021972656, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t2", "rows_for_plan": 10, "cost_for_plan": 4.021972656 } @@ -772,7 +792,11 @@ explain select * from v1 { "cost": 2.021972656, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 10, "cost_for_plan": 4.021972656, "cost_for_sorting": 10 @@ -837,7 +861,11 @@ explain select * from v1 { "cost": 10, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "<derived2>", "rows_for_plan": 10, "cost_for_plan": 12 } @@ -1009,6 +1037,27 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "uses_join_buffering": false } }, + "table": "t2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 100, + "cost": 2.317382812, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 100, + "cost": 2.317382812, + "uses_join_buffering": false + } + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 100, "cost_for_plan": 22.31738281, "rest_of_plan": [ @@ -1039,7 +1088,11 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "cost": 200.0585794, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": ["t1"], + "table": "t2", "rows_for_plan": 100, "cost_for_plan": 242.3759623, "pruned_by_hanging_leaf": true @@ -1049,22 +1102,6 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "plan_prefix": [], "table": "t2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 100, - "cost": 2.317382812, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 100, - "cost": 2.317382812, - "uses_join_buffering": false - } - }, "rows_for_plan": 100, "cost_for_plan": 22.31738281, "rest_of_plan": [ @@ -1095,7 +1132,11 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "cost": 200.0585794, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": ["t2"], + "table": "t1", "rows_for_plan": 100, "cost_for_plan": 242.3759623, "pruned_by_cost": true @@ -1271,7 +1312,11 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "cost": 6.25, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 5, "cost_for_plan": 7.25 } @@ -1461,7 +1506,11 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "cost": 2.2, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 8, "cost_for_plan": 3.8, "cost_for_sorting": 8 @@ -1659,7 +1708,11 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "cost": 2.35, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 9, "cost_for_plan": 4.15, "cost_for_sorting": 9 @@ -1846,7 +1899,11 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "cost": 2.35, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 9, "cost_for_plan": 4.15, "cost_for_sorting": 9 @@ -2132,7 +2189,11 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "cost": 21.14242739, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 21, "cost_for_plan": 25.34242739 } @@ -2386,7 +2447,11 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "cost": 2.006835938, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": ["t2"], + "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } @@ -2525,7 +2590,11 @@ explain select * from t1 left join t2 on t2.a=t1.a { "cost": 2.006835938, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937, "rest_of_plan": [ @@ -2554,7 +2623,11 @@ explain select * from t1 left join t2 on t2.a=t1.a { "cost": 4, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": ["t1"], + "table": "t2", "rows_for_plan": 4, "cost_for_plan": 7.606835937, "pruned_by_hanging_leaf": true @@ -2738,7 +2811,11 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and "cost": 2.006835938, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": ["t3", "t2"], + "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } @@ -2945,7 +3022,11 @@ explain extended select * from t1 where a in (select pk from t10) { "cost": 2.021972656, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t10", "rows_for_plan": 10, "cost_for_plan": 4.021972656 } @@ -2975,6 +3056,27 @@ explain extended select * from t1 where a in (select pk from t10) { "uses_join_buffering": false } }, + "table": "t10", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.021972656, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 2.021972656, + "uses_join_buffering": false + } + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 3, "cost_for_plan": 2.606591797, "semijoin_strategy_choice": [], @@ -2997,7 +3099,11 @@ explain extended select * from t1 where a in (select pk from t10) { "cost": 2.021972656, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": ["t1"], + "table": "t10", "rows_for_plan": 30, "cost_for_plan": 10.62856445, "semijoin_strategy_choice": [ @@ -3026,22 +3132,6 @@ explain extended select * from t1 where a in (select pk from t10) { { "plan_prefix": [], "table": "t10", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 10, - "cost": 2.021972656, - "uses_join_buffering": false - } - }, "rows_for_plan": 10, "cost_for_plan": 4.021972656, "semijoin_strategy_choice": [], @@ -3427,7 +3517,11 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "cost": 0.126073957, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 1, "cost_for_plan": 0.326073957, "pruned_by_hanging_leaf": true @@ -3556,7 +3650,11 @@ select f1(a) from t1 { "cost": 2.006835938, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } @@ -3652,7 +3750,11 @@ select f2(a) from t1 { "cost": 2.006835938, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } @@ -3700,7 +3802,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2092 +2190 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3714,7 +3816,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 1992 0 + 2090 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3722,7 +3824,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2092 0 +select * from t1 2190 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -4035,6 +4137,27 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "uses_join_buffering": false } }, + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "range", + "resulting_rows": 3, + "cost": 0.746757383, + "chosen": true + } + ], + "chosen_access_method": { + "type": "range", + "records": 3, + "cost": 0.746757383, + "uses_join_buffering": false + } + } + }, + { + "plan_prefix": [], + "table": "t0", "rows_for_plan": 3, "cost_for_plan": 1.346757383, "rest_of_plan": [ @@ -4064,7 +4187,11 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "cost": 3.001757383, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": ["t0"], + "table": "t1", "rows_for_plan": 3, "cost_for_plan": 4.948514767, "pruned_by_hanging_leaf": true @@ -4074,22 +4201,6 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "plan_prefix": [], "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "range", - "resulting_rows": 3, - "cost": 0.746757383, - "chosen": true - } - ], - "chosen_access_method": { - "type": "range", - "records": 3, - "cost": 0.746757383, - "uses_join_buffering": false - } - }, "rows_for_plan": 3, "cost_for_plan": 1.346757383, "rest_of_plan": [ @@ -4120,7 +4231,11 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "cost": 3.003514767, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": ["t1"], + "table": "t0", "rows_for_plan": 6, "cost_for_plan": 5.55027215, "pruned_by_cost": true @@ -4264,7 +4379,11 @@ explain select * from (select rand() from t1)q { "cost": 2.005126953, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 3, "cost_for_plan": 2.605126953 } @@ -4328,7 +4447,11 @@ explain select * from (select rand() from t1)q { "cost": 3, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "<derived2>", "rows_for_plan": 3, "cost_for_plan": 3.6 } @@ -4535,6 +4658,27 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "uses_join_buffering": false } }, + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } + } + }, + { + "plan_prefix": [], + "table": "t_inner_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "rest_of_plan": [ @@ -4556,7 +4700,11 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "cost": 2.005126953, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": ["t_inner_1"], + "table": "t_inner_2", "rows_for_plan": 9, "cost_for_plan": 6.410253906 } @@ -4565,22 +4713,6 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "plan_prefix": [], "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "pruned_by_heuristic": true @@ -4611,6 +4743,44 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "uses_join_buffering": false } }, + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } + }, + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], @@ -4634,6 +4804,27 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "uses_join_buffering": true } }, + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t1"], + "table": "t_inner_1", "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], @@ -4656,7 +4847,11 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "cost": 2.005126953, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": ["t1", "t_inner_1"], + "table": "t_inner_2", "rows_for_plan": 27, "cost_for_plan": 13.81538086, "semijoin_strategy_choice": [ @@ -4685,22 +4880,6 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "plan_prefix": ["t1"], "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], @@ -4711,22 +4890,6 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "plan_prefix": [], "table": "t_inner_1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], @@ -4735,22 +4898,6 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "plan_prefix": [], "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], @@ -5065,6 +5212,95 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": false } }, + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } + }, + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": false + } + }, + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": false + } + }, + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": false + } + } + }, + { + "plan_prefix": [], + "table": "t_outer_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], @@ -5088,12 +5324,84 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 9, - "cost_for_plan": 6.410253906, + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + }, + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + }, + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_outer_2", + "rows_for_plan": 27, + "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ @@ -5111,27 +5419,68 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 81, - "cost_for_plan": 24.62563477, - "semijoin_strategy_choice": [ - { - "strategy": "FirstMatch", + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", "records": 3, - "read_time": 44.75893555 - }, - { - "strategy": "DuplicateWeedout", + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", "records": 3, - "read_time": 37.22563477 - }, - { - "chosen_strategy": "DuplicateWeedout" + "cost": 2.005126953, + "uses_join_buffering": true } - ], + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_1", + "rows_for_plan": 81, + "cost_for_plan": 28.22563477, + "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], - "table": "t_outer_2", + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -5148,16 +5497,68 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 27, - "cost_for_plan": 44.64101563, - "semijoin_strategy_choice": [], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_2", + "rows_for_plan": 729, + "cost_for_plan": 176.0410156, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 27, + "read_time": 389.4047852 + }, + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 289.4410156 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", - "t_outer_2" + "t_inner_2" ], "table": "t_inner_4", "best_access_path": { @@ -5176,16 +5577,42 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_2" + ], + "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 62.84614258, + "cost_for_plan": 307.6461426, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", "t_inner_2", - "t_outer_2", "t_inner_4" ], "table": "t_inner_3", @@ -5204,19 +5631,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 210.6615234, + "cost_for_plan": 455.4615234, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, - "read_time": 424.025293 + "read_time": 668.825293 }, { "strategy": "DuplicateWeedout", "records": 27, - "read_time": 324.0615234 + "read_time": 568.8615234 }, { "chosen_strategy": "DuplicateWeedout" @@ -5228,65 +5665,33 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", - "t_outer_2" + "t_inner_2" ], "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 243, - "cost_for_plan": 95.25639648, + "cost_for_plan": 340.0563965, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, - "rows_for_plan": 9, - "cost_for_plan": 41.03076172, + "rows_for_plan": 243, + "cost_for_plan": 78.83076172, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", "t_inner_4" ], - "table": "t_outer_2", + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -5303,17 +5708,43 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 81, - "cost_for_plan": 59.24614258, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_4" + ], + "table": "t_inner_2", + "rows_for_plan": 2187, + "cost_for_plan": 518.2461426, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", "t_inner_4", - "t_outer_2" + "t_inner_2" ], "table": "t_inner_3", "best_access_path": { @@ -5331,30 +5762,85 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, - "rows_for_plan": 729, - "cost_for_plan": 207.0615234, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_4", + "t_inner_2" + ], + "table": "t_inner_3", + "rows_for_plan": 19683, + "cost_for_plan": 4456.861523, "semijoin_strategy_choice": [ { + "strategy": "FirstMatch", + "records": 27, + "read_time": 9562.749707 + }, + { "strategy": "DuplicateWeedout", "records": 27, - "read_time": 320.4615234 + "read_time": 7413.361523 }, { - "chosen_strategy": "DuplicateWeedout" + "chosen_strategy": "FirstMatch" } - ] + ], + "pruned_by_cost": true } ] }, { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", "t_inner_4" ], "table": "t_inner_3", + "rows_for_plan": 2187, + "cost_for_plan": 518.2461426, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 176.0410156, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_3" + ], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -5370,42 +5856,90 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, - "rows_for_plan": 81, - "cost_for_plan": 59.24614258, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_3" + ], + "table": "t_inner_2", + "rows_for_plan": 6561, + "cost_for_plan": 1490.256396, "semijoin_strategy_choice": [], - "pruned_by_heuristic": true + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_3" + ], + "table": "t_inner_4", + "rows_for_plan": 2187, + "cost_for_plan": 615.4461426, + "semijoin_strategy_choice": [], + "pruned_by_cost": true } ] - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "rows_for_plan": 27, - "cost_for_plan": 44.64101563, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true } ] }, { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_2", + "rows_for_plan": 243, + "cost_for_plan": 60.63588867, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_4", + "rows_for_plan": 81, + "cost_for_plan": 28.22563477, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_3", + "rows_for_plan": 243, + "cost_for_plan": 60.63588867, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_1", + "rows_for_plan": 9, + "cost_for_plan": 6.410253906, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + }, "table": "t_outer_2", "best_access_path": { "considered_access_paths": [ @@ -5423,6 +5957,44 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_outer_2", "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [], @@ -5446,6 +6018,44 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], + "table": "t_inner_2", "rows_for_plan": 729, "cost_for_plan": 172.4410156, "semijoin_strategy_choice": [ @@ -5483,6 +6093,32 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2" + ], + "table": "t_inner_4", "rows_for_plan": 81, "cost_for_plan": 304.0461426, "semijoin_strategy_choice": [], @@ -5511,7 +6147,17 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", "rows_for_plan": 729, "cost_for_plan": 451.8615234, "semijoin_strategy_choice": [ @@ -5528,8 +6174,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "chosen_strategy": "DuplicateWeedout" } - ], - "pruned_by_cost": true + ] } ] }, @@ -5541,48 +6186,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_2" ], "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 243, "cost_for_plan": 336.4563965, "semijoin_strategy_choice": [], - "pruned_by_cost": true + "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, "rows_for_plan": 243, "cost_for_plan": 75.23076172, "semijoin_strategy_choice": [], @@ -5611,18 +6224,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 2187, - "cost_for_plan": 514.6461426, - "semijoin_strategy_choice": [], - "pruned_by_cost": true - }, - { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_4" - ], "table": "t_inner_3", "best_access_path": { "considered_access_paths": [ @@ -5639,33 +6240,89 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_2", "rows_for_plan": 2187, "cost_for_plan": 514.6461426, "semijoin_strategy_choice": [], - "pruned_by_cost": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4", + "t_inner_2" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4", + "t_inner_2" + ], + "table": "t_inner_3", + "rows_for_plan": 19683, + "cost_for_plan": 4453.261523, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 7409.761523 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_3", + "rows_for_plan": 2187, + "cost_for_plan": 514.6461426, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 729, "cost_for_plan": 172.4410156, "semijoin_strategy_choice": [], @@ -5677,6 +6334,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_outer_2", "t_inner_3" ], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ @@ -5693,7 +6367,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_3" + ], + "table": "t_inner_2", "rows_for_plan": 6561, "cost_for_plan": 1486.656396, "semijoin_strategy_choice": [], @@ -5707,22 +6390,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_3" ], "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, "rows_for_plan": 2187, "cost_for_plan": 611.8461426, "semijoin_strategy_choice": [], @@ -5734,30 +6401,28 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, { "plan_prefix": ["t_outer_1", "t_inner_1"], - "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", + "table": "t_inner_2", + "rows_for_plan": 81, + "cost_for_plan": 24.62563477, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true + "read_time": 44.75893555 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 37.22563477 + }, + { + "chosen_strategy": "DuplicateWeedout" } - }, - "rows_for_plan": 27, - "cost_for_plan": 13.81538086, - "semijoin_strategy_choice": [], + ], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], - "table": "t_inner_2", + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_outer_2", "best_access_path": { "considered_access_paths": [ { @@ -5774,18 +6439,73 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 243, - "cost_for_plan": 64.43076172, + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_outer_2", + "rows_for_plan": 27, + "cost_for_plan": 44.64101563, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", - "t_inner_4", - "t_inner_2" + "t_inner_2", + "t_outer_2" ], - "table": "t_outer_2", + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_3", "best_access_path": { "considered_access_paths": [ { @@ -5801,20 +6521,105 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, - "rows_for_plan": 2187, - "cost_for_plan": 503.8461426, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2" + ], + "table": "t_inner_4", + "rows_for_plan": 81, + "cost_for_plan": 62.84614258, "semijoin_strategy_choice": [], - "pruned_by_cost": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 210.6615234, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 27, + "read_time": 424.025293 + }, + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 324.0615234 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ] + } + ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", - "t_inner_4", - "t_inner_2" + "t_inner_2", + "t_outer_2" ], "table": "t_inner_3", + "rows_for_plan": 243, + "cost_for_plan": 95.25639648, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_inner_4", + "rows_for_plan": 9, + "cost_for_plan": 41.03076172, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_outer_2", "best_access_path": { "considered_access_paths": [ { @@ -5831,14 +6636,119 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 2187, - "cost_for_plan": 503.8461426, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_outer_2", + "rows_for_plan": 81, + "cost_for_plan": 59.24614258, "semijoin_strategy_choice": [], - "pruned_by_cost": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4", + "t_outer_2" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4", + "t_outer_2" + ], + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 207.0615234, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 320.4615234 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ] + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", + "rows_for_plan": 81, + "cost_for_plan": 59.24614258, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true } ] }, { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_inner_3", + "rows_for_plan": 27, + "cost_for_plan": 44.64101563, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_4", + "rows_for_plan": 27, + "cost_for_plan": 13.81538086, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], "table": "t_outer_2", "best_access_path": { @@ -5857,14 +6767,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 243, - "cost_for_plan": 64.43076172, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], - "table": "t_inner_3", + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -5881,39 +6784,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 243, - "cost_for_plan": 64.43076172, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true - } - ] - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1"], - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "rows_for_plan": 81, - "cost_for_plan": 24.62563477, - "semijoin_strategy_choice": [], - "rest_of_plan": [ - { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], - "table": "t_inner_2", + "table": "t_inner_3", "best_access_path": { "considered_access_paths": [ { @@ -5929,19 +6800,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, - "rows_for_plan": 729, - "cost_for_plan": 172.4410156, + } + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "table": "t_outer_2", + "rows_for_plan": 243, + "cost_for_plan": 64.43076172, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", - "t_inner_3", - "t_inner_2" + "t_inner_4", + "t_outer_2" ], - "table": "t_outer_2", + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -5958,115 +6833,34 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 6561, - "cost_for_plan": 1486.656396, - "semijoin_strategy_choice": [], - "pruned_by_cost": true - }, - { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_3", - "t_inner_2" - ], - "table": "t_inner_4", + "table": "t_inner_3", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "resulting_rows": 9, + "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "records": 9, + "cost": 2.015380859, "uses_join_buffering": true } - }, - "rows_for_plan": 2187, - "cost_for_plan": 611.8461426, - "semijoin_strategy_choice": [], - "pruned_by_cost": true - } - ] - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "rows_for_plan": 729, - "cost_for_plan": 172.4410156, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], - "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, - "rows_for_plan": 243, - "cost_for_plan": 75.23076172, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + }, { "plan_prefix": [ "t_outer_1", "t_inner_1", - "t_inner_3", - "t_inner_4" + "t_inner_4", + "t_outer_2" ], "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 2187, - "cost_for_plan": 514.6461426, + "cost_for_plan": 503.8461426, "semijoin_strategy_choice": [], "pruned_by_cost": true }, @@ -6074,80 +6868,48 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "plan_prefix": [ "t_outer_1", "t_inner_1", - "t_inner_3", - "t_inner_4" + "t_inner_4", + "t_outer_2" ], - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, + "table": "t_inner_3", "rows_for_plan": 2187, - "cost_for_plan": 514.6461426, + "cost_for_plan": 503.8461426, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "table": "t_inner_2", + "rows_for_plan": 243, + "cost_for_plan": 64.43076172, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "table": "t_inner_3", + "rows_for_plan": 243, + "cost_for_plan": 64.43076172, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true } ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_3", + "rows_for_plan": 81, + "cost_for_plan": 24.62563477, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1"], "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "rows_for_plan": 27, - "cost_for_plan": 10.02050781, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true - }, - { - "plan_prefix": ["t_outer_1"], - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], @@ -6156,22 +6918,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1"], "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], @@ -6180,22 +6926,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1"], "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], @@ -6205,71 +6935,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, { "plan_prefix": [], - "table": "t_inner_1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "table": "t_outer_2", + "rows_for_plan": 9, + "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, - "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "table": "t_inner_1", + "rows_for_plan": 3, + "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, + "table": "t_inner_2", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], @@ -6278,22 +6960,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [], "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], @@ -6302,22 +6968,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [], "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], @@ -6398,8 +7048,8 @@ 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 <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t_inner_1 ALL NULL NULL NULL NULL 3 2 MATERIALIZED t_inner_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) @@ -6637,6 +7287,27 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": false } }, + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": false + } + } + }, + { + "plan_prefix": [], + "table": "t_inner_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "rest_of_plan": [ @@ -6658,7 +7329,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": ["t_inner_1"], + "table": "t_inner_2", "rows_for_plan": 27, "cost_for_plan": 10.02050781 } @@ -6667,22 +7342,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [], "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "pruned_by_heuristic": true @@ -6710,6 +7369,27 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": false } }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": false + } + } + }, + { + "plan_prefix": [], + "table": "t_inner_4", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "rest_of_plan": [ @@ -6731,7 +7411,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": ["t_inner_4"], + "table": "t_inner_3", "rows_for_plan": 27, "cost_for_plan": 10.02050781 } @@ -6740,22 +7424,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [], "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "pruned_by_heuristic": true @@ -6786,6 +7454,95 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": false } }, + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } + }, + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": false + } + }, + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": false + } + }, + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": false + } + } + }, + { + "plan_prefix": [], + "table": "t_outer_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], @@ -6809,12 +7566,84 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 9, - "cost_for_plan": 6.410253906, + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + }, + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + }, + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_outer_2", + "rows_for_plan": 27, + "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ @@ -6832,32 +7661,68 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 81, - "cost_for_plan": 24.62563477, - "semijoin_strategy_choice": [ - { - "strategy": "FirstMatch", - "records": 3, - "read_time": 44.75893555 - }, - { - "strategy": "SJ-Materialization", + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", "records": 3, - "read_time": 8.125634766 - }, - { - "strategy": "DuplicateWeedout", + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", "records": 3, - "read_time": 37.22563477 - }, - { - "chosen_strategy": "SJ-Materialization" + "cost": 2.005126953, + "uses_join_buffering": true } - ], + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_1", + "rows_for_plan": 81, + "cost_for_plan": 28.22563477, + "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], - "table": "t_outer_2", + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -6874,16 +7739,73 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 27, - "cost_for_plan": 15.54101562, - "semijoin_strategy_choice": [], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_2", + "rows_for_plan": 729, + "cost_for_plan": 176.0410156, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 27, + "read_time": 389.4047852 + }, + { + "strategy": "SJ-Materialization", + "records": 27, + "read_time": 16.74101562 + }, + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 289.4410156 + }, + { + "chosen_strategy": "SJ-Materialization" + } + ], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", - "t_outer_2" + "t_inner_2" ], "table": "t_inner_4", "best_access_path": { @@ -6902,16 +7824,42 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_2" + ], + "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 33.74614258, + "cost_for_plan": 34.94614258, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", "t_inner_2", - "t_outer_2", "t_inner_4" ], "table": "t_inner_3", @@ -6930,24 +7878,34 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 181.5615234, + "cost_for_plan": 182.7615234, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, - "read_time": 394.925293 + "read_time": 396.125293 }, { "strategy": "SJ-Materialization", "records": 27, - "read_time": 22.26152344 + "read_time": 23.46152344 }, { "strategy": "DuplicateWeedout", "records": 27, - "read_time": 294.9615234 + "read_time": 296.1615234 }, { "chosen_strategy": "SJ-Materialization" @@ -6959,36 +7917,193 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", - "t_outer_2" + "t_inner_2" ], "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 243, - "cost_for_plan": 66.15639648, + "cost_for_plan": 67.35639648, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] }, { + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_4", + "rows_for_plan": 243, + "cost_for_plan": 78.83076172, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 176.0410156, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_2", + "rows_for_plan": 243, + "cost_for_plan": 60.63588867, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_4", + "rows_for_plan": 81, + "cost_for_plan": 28.22563477, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_3", + "rows_for_plan": 243, + "cost_for_plan": 60.63588867, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_1", + "rows_for_plan": 9, + "cost_for_plan": 6.410253906, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + }, + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + }, + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_outer_2", + "rows_for_plan": 81, + "cost_for_plan": 24.62563477, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_2", + "rows_for_plan": 81, + "cost_for_plan": 24.62563477, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 3, + "read_time": 44.75893555 + }, + { + "strategy": "SJ-Materialization", + "records": 3, + "read_time": 8.125634766 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 37.22563477 + }, + { + "chosen_strategy": "SJ-Materialization" + } + ], + "rest_of_plan": [ + { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + }, "table": "t_inner_4", "best_access_path": { "considered_access_paths": [ @@ -7006,8 +8121,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 9, - "cost_for_plan": 11.93076172, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_outer_2", + "rows_for_plan": 27, + "cost_for_plan": 15.54101562, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7015,9 +8151,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_outer_1", "t_inner_1", "t_inner_2", - "t_inner_4" + "t_outer_2" ], - "table": "t_outer_2", + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_3", "best_access_path": { "considered_access_paths": [ { @@ -7033,9 +8186,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2" + ], + "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 30.14614258, + "cost_for_plan": 33.74614258, "semijoin_strategy_choice": [], "pruned_by_cost": true }, @@ -7044,9 +8206,31 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_outer_1", "t_inner_1", "t_inner_2", - "t_inner_4" + "t_outer_2" ], "table": "t_inner_3", + "rows_for_plan": 243, + "cost_for_plan": 66.15639648, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_inner_4", + "rows_for_plan": 9, + "cost_for_plan": 11.93076172, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_outer_2", "best_access_path": { "considered_access_paths": [ { @@ -7063,6 +8247,45 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_outer_2", + "rows_for_plan": 81, + "cost_for_plan": 30.14614258, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", "rows_for_plan": 81, "cost_for_plan": 30.14614258, "semijoin_strategy_choice": [], @@ -7073,22 +8296,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 27, "cost_for_plan": 15.54101562, "semijoin_strategy_choice": [], @@ -7098,47 +8305,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, { "plan_prefix": ["t_outer_1", "t_inner_1"], - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "rows_for_plan": 81, - "cost_for_plan": 24.62563477, - "semijoin_strategy_choice": [], - "pruned_by_cost": true - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, "rows_for_plan": 27, "cost_for_plan": 13.81538086, "semijoin_strategy_choice": [], @@ -7147,22 +8314,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [], @@ -7173,46 +8324,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1"], "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "rows_for_plan": 27, - "cost_for_plan": 10.02050781, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true - }, - { - "plan_prefix": ["t_outer_1"], - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], @@ -7221,22 +8332,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1"], "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], @@ -7245,22 +8340,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1"], "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], @@ -7270,71 +8349,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, { "plan_prefix": [], - "table": "t_inner_1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "table": "t_outer_2", + "rows_for_plan": 9, + "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, - "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "table": "t_inner_1", + "rows_for_plan": 3, + "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, + "table": "t_inner_2", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], @@ -7343,22 +8374,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [], "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], @@ -7367,22 +8382,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [], "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], @@ -7419,8 +8418,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_join_order": [ "t_outer_1", - "<subquery2>", "t_outer_2", + "<subquery2>", "<subquery3>" ] }, @@ -7443,19 +8442,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "attached": null }, { - "table": "t_inner_1", + "table": "t_outer_2", "attached": null }, { - "table": "t_inner_2", + "table": "t_inner_1", "attached": null }, { - "table": "<subquery2>", + "table": "t_inner_2", "attached": null }, { - "table": "t_outer_2", + "table": "<subquery2>", "attached": null }, { @@ -8086,6 +9085,34 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "uses_join_buffering": false } }, + "table": "B", + "best_access_path": + { + "considered_access_paths": + [ + + { + "access_type": "scan", + "resulting_rows": 800, + "cost": 44.19726562, + "chosen": true + } + ], + "chosen_access_method": + { + "type": "scan", + "records": 800, + "cost": 44.19726562, + "uses_join_buffering": false + } + } + }, + + { + "plan_prefix": + [ + ], + "table": "A", "rows_for_plan": 5, "cost_for_plan": 4.017089844, "rest_of_plan": @@ -8116,7 +9143,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 220.9863281, "uses_join_buffering": false } - }, + } + }, + + { + "plan_prefix": + [ + "A" + ], + "table": "B", "rows_for_plan": 4000, "cost_for_plan": 1025.003418 } @@ -8128,26 +9163,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ ], "table": "B", - "best_access_path": - { - "considered_access_paths": - [ - - { - "access_type": "scan", - "resulting_rows": 800, - "cost": 44.19726562, - "chosen": true - } - ], - "chosen_access_method": - { - "type": "scan", - "records": 800, - "cost": 44.19726562, - "uses_join_buffering": false - } - }, "rows_for_plan": 800, "cost_for_plan": 204.1972656, "pruned_by_heuristic": true @@ -8191,6 +9206,34 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "uses_join_buffering": false } }, + "table": "B", + "best_access_path": + { + "considered_access_paths": + [ + + { + "access_type": "scan", + "resulting_rows": 800, + "cost": 44.19726562, + "chosen": true + } + ], + "chosen_access_method": + { + "type": "scan", + "records": 800, + "cost": 44.19726562, + "uses_join_buffering": false + } + } + }, + + { + "plan_prefix": + [ + ], + "table": "A", "rows_for_plan": 10, "cost_for_plan": 4.017089844, "rest_of_plan": @@ -8231,7 +9274,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 20.00585794, "uses_join_buffering": false } - }, + } + }, + + { + "plan_prefix": + [ + "A" + ], + "table": "B", "rows_for_plan": 10, "cost_for_plan": 26.02294779, "selectivity": 0.8, @@ -8245,26 +9296,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ ], "table": "B", - "best_access_path": - { - "considered_access_paths": - [ - - { - "access_type": "scan", - "resulting_rows": 800, - "cost": 44.19726562, - "chosen": true - } - ], - "chosen_access_method": - { - "type": "scan", - "records": 800, - "cost": 44.19726562, - "uses_join_buffering": false - } - }, "rows_for_plan": 800, "cost_for_plan": 204.1972656, "pruned_by_cost": true @@ -8415,6 +9446,35 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "uses_join_buffering": false } }, + "table": "t2", + "best_access_path": + { + "considered_access_paths": + [ + + { + "access_type": "scan", + "resulting_rows": 100, + "cost": 2.219726562, + "chosen": true, + "use_tmp_table": true + } + ], + "chosen_access_method": + { + "type": "scan", + "records": 100, + "cost": 2.219726562, + "uses_join_buffering": false + } + } + }, + + { + "plan_prefix": + [ + ], + "table": "t1", "rows_for_plan": 10, "cost_for_plan": 4.021972656, "rest_of_plan": @@ -8455,7 +9515,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 20.00585794, "uses_join_buffering": false } - }, + } + }, + + { + "plan_prefix": + [ + "t1" + ], + "table": "t2", "rows_for_plan": 10, "cost_for_plan": 26.0278306, "cost_for_sorting": 10, @@ -8469,27 +9537,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ ], "table": "t2", - "best_access_path": - { - "considered_access_paths": - [ - - { - "access_type": "scan", - "resulting_rows": 100, - "cost": 2.219726562, - "chosen": true, - "use_tmp_table": true - } - ], - "chosen_access_method": - { - "type": "scan", - "records": 100, - "cost": 2.219726562, - "uses_join_buffering": false - } - }, "rows_for_plan": 100, "cost_for_plan": 22.21972656, "rest_of_plan": @@ -8530,7 +9577,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 2.021972656, "uses_join_buffering": true } - }, + } + }, + + { + "plan_prefix": + [ + "t2" + ], + "table": "t1", "rows_for_plan": 1000, "cost_for_plan": 224.2416992, "pruned_by_cost": true @@ -8777,7 +9832,11 @@ select count(*) from seq_1_to_10000000 { "cost": 10000000, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "seq_1_to_10000000", "rows_for_plan": 10000000, "cost_for_plan": 12000000 } @@ -8948,7 +10007,7 @@ set @path= (select json_search(@trace, 'one', 'no predicate for first keypart')) set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2); select @sub_path; @sub_path -$.steps[1].join_optimization.steps[4].considered_execution_plans[0].rest_of_plan[0] +$.steps[1].join_optimization.steps[4].considered_execution_plans[1].rest_of_plan[0] select json_detailed(json_extract( @trace, @@ -9180,7 +10239,14 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "cost": 2.000585794, "uses_join_buffering": false } - }, + } + }, + + { + "plan_prefix": + [ + ], + "table": "t2", "rows_for_plan": 1.8367, "cost_for_plan": 2.367925794, "cost_for_sorting": 1.8367 @@ -9258,5 +10324,5 @@ left(trace, 100) "select_id": 1, "steps": [ -# End of 10.6 tests set optimizer_trace='enabled=off'; +# End of 10.6 tests diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index b689c80db1e..a8b75c5f8f0 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -885,6 +885,6 @@ set optimizer_trace=0; set statement optimizer_trace=1 for select * from seq_1_to_10 where seq<2; --echo # The trace must not be empty: select left(trace, 100) from information_schema.optimizer_trace; +set optimizer_trace='enabled=off'; --echo # End of 10.6 tests -set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index 335e408bddd..86493f10020 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -219,7 +219,11 @@ explain select * from t1 where a=1 or b=1 { "cost": 2.484903732, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 2, "cost_for_plan": 2.884903732 } diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index 590b659f40d..e5c8cef0106 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -225,7 +225,11 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "cost": 1.125146475, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 1, "cost_for_plan": 1.325146475, "pruned_by_hanging_leaf": true diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result index 83d98c4c183..2b2873d8b10 100644 --- a/mysql-test/main/opt_trace_security.result +++ b/mysql-test/main/opt_trace_security.result @@ -105,7 +105,11 @@ select * from db1.t1 { "cost": 2.005126953, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 3, "cost_for_plan": 2.605126953 } @@ -226,7 +230,11 @@ select * from db1.v1 { "cost": 2.005126953, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 3, "cost_for_plan": 2.605126953 } diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 9b6d97492cd..eaf75ed7999 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -91,10 +91,10 @@ 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 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 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +4 MATERIALIZED <derived5> 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: @@ -112,10 +112,10 @@ from (values (1),(5)) as tvc_1 ); id select_type table type possible_keys key key_len ref rows filtered Extra 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 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +4 MATERIALIZED <derived5> 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: diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 408094e3c1a..b74e40ff7c3 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -486,8 +486,8 @@ order by s_name limit 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier ALL PRIMARY,i_s_nationkey NULL NULL NULL 10 100.00 Using where; Using filesort -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY nation eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 100.00 Using where 2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where 4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 100.00 Using where @@ -1245,11 +1245,11 @@ EXPLAIN EXTENDED SELECT * FROM language, country, continent WHERE country_group = lang_group AND lang_group IS NULL; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE country ALL NULL NULL NULL NULL 2 50.00 Using where -1 SIMPLE language ALL NULL NULL NULL NULL 6 16.67 Using where; Using join buffer (flat, BNL join) +1 SIMPLE language ALL NULL NULL NULL NULL 6 16.67 Using where +1 SIMPLE country ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE continent ALL NULL NULL NULL NULL 6 100.00 Using join buffer (incremental, BNL join) Warnings: -Note 1003 select `test`.`language`.`lang_group` AS `lang_group`,`test`.`language`.`lang` AS `lang`,`test`.`country`.`code` AS `code`,`test`.`country`.`country_group` AS `country_group`,`test`.`continent`.`cont_group` AS `cont_group`,`test`.`continent`.`cont` AS `cont` from `test`.`language` join `test`.`country` join `test`.`continent` where `test`.`language`.`lang_group` = `test`.`country`.`country_group` and `test`.`country`.`country_group` is null +Note 1003 select `test`.`language`.`lang_group` AS `lang_group`,`test`.`language`.`lang` AS `lang`,`test`.`country`.`code` AS `code`,`test`.`country`.`country_group` AS `country_group`,`test`.`continent`.`cont_group` AS `cont_group`,`test`.`continent`.`cont` AS `cont` from `test`.`language` join `test`.`country` join `test`.`continent` where `test`.`country`.`country_group` = `test`.`language`.`lang_group` and `test`.`language`.`lang_group` is null set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; drop table language, country, continent; set use_stat_tables=@save_use_stat_tables; @@ -1769,8 +1769,8 @@ explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 10 NULL 9 100.00 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 Warnings: Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 @@ -1791,8 +1791,8 @@ explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 10 NULL 9 9.00 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 Warnings: Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 8120f51ca83..b1f8aa032ac 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -491,8 +491,8 @@ order by s_name limit 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier ALL PRIMARY,i_s_nationkey NULL NULL NULL 10 100.00 Using where; Using filesort -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY nation eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 100.00 Using where 2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where 4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 100.00 Using where @@ -1257,11 +1257,11 @@ EXPLAIN EXTENDED SELECT * FROM language, country, continent WHERE country_group = lang_group AND lang_group IS NULL; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE country ALL NULL NULL NULL NULL 2 50.00 Using where -1 SIMPLE language ALL NULL NULL NULL NULL 6 16.67 Using where; Using join buffer (flat, BNL join) +1 SIMPLE language ALL NULL NULL NULL NULL 6 0.00 Using where +1 SIMPLE country ALL NULL NULL NULL NULL 2 0.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE continent ALL NULL NULL NULL NULL 6 100.00 Using join buffer (incremental, BNL join) Warnings: -Note 1003 select `test`.`language`.`lang_group` AS `lang_group`,`test`.`language`.`lang` AS `lang`,`test`.`country`.`code` AS `code`,`test`.`country`.`country_group` AS `country_group`,`test`.`continent`.`cont_group` AS `cont_group`,`test`.`continent`.`cont` AS `cont` from `test`.`language` join `test`.`country` join `test`.`continent` where `test`.`language`.`lang_group` = `test`.`country`.`country_group` and `test`.`country`.`country_group` is null +Note 1003 select `test`.`language`.`lang_group` AS `lang_group`,`test`.`language`.`lang` AS `lang`,`test`.`country`.`code` AS `code`,`test`.`country`.`country_group` AS `country_group`,`test`.`continent`.`cont_group` AS `cont_group`,`test`.`continent`.`cont` AS `cont` from `test`.`language` join `test`.`country` join `test`.`continent` where `test`.`country`.`country_group` = `test`.`language`.`lang_group` and `test`.`language`.`lang_group` is null set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; drop table language, country, continent; set use_stat_tables=@save_use_stat_tables; @@ -1781,8 +1781,8 @@ explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 10 NULL 11 100.00 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 Warnings: Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 @@ -1803,8 +1803,8 @@ explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 10 NULL 11 11.00 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 Warnings: Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index 22f5bfafb8e..33110ec104d 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -217,9 +217,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 5 dbt3_s001.part.p_partkey 30 Using where 1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where 1 SIMPLE orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (27%) Using where; Using rowid filter -1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where 1 SIMPLE n1 eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where +1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 select o_year, sum(case when nation = 'UNITED STATES' then volume else 0 end) / sum(volume) as mkt_share diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index 5b62f228b1f..4e402baadb5 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -248,10 +248,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join) 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 5 dbt3_s001.part.p_partkey 30 Using where 1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where -1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 1 SIMPLE orders eq_ref PRIMARY,i_o_orderdate,i_o_custkey PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where 1 SIMPLE n1 eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where +1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 select o_year, sum(case when nation = 'UNITED STATES' then volume else 0 end) / sum(volume) as mkt_share diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result index e6363610301..36a723beced 100644 --- a/mysql-test/main/subselect2.result +++ b/mysql-test/main/subselect2.result @@ -126,8 +126,8 @@ c373e9f5ad07993f3859444553544200 Last Discussion c373e9f5ad079174ff1744455354420 EXPLAIN SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion'; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL DDOCTYPEID_IDX,DFOLDERID_IDX NULL NULL NULL 9 Using where -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t2.FOLDERID 1 Using where 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index 9febf3d4e5f..8ebb929550b 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -1056,10 +1056,10 @@ AND t1.val IN (SELECT t3.val FROM t3 WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 13 func 1 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 13 func 1 -3 MATERIALIZED t3 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 13 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 5 Using where SELECT * FROM t1 WHERE t1.val IN (SELECT t2.val FROM t2 @@ -1610,9 +1610,9 @@ A.t1field IN (SELECT C.t2field FROM t2 C WHERE C.t2field IN (SELECT D.t2field FROM t2 D)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A index PRIMARY PRIMARY 4 NULL 3 Using index -1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; Start temporary; End temporary 1 PRIMARY C eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index 1 PRIMARY D eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index +1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; Start temporary; End temporary SELECT * FROM t1 A WHERE A.t1field IN (SELECT A.t1field FROM t2 B) AND @@ -2179,8 +2179,8 @@ explain SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t5 index a a 10 NULL 2 Using where; Using index; LooseScan -1 PRIMARY t4 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t5.b 2 Using where; FirstMatch(t5) +1 PRIMARY t2 ref b b 5 test.t5.b 2 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 FirstMatch(t5) 1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; Using join buffer (flat, BNL join) SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); a @@ -2260,11 +2260,11 @@ alias1.c IN (SELECT SQ3_alias1.b FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) LIMIT 100; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Start temporary -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) -1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) +1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Start temporary +1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary 2 DERIVED t2 ALL NULL NULL NULL NULL 20 create table t3 as SELECT diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result index db6393b909d..70adb7ab00f 100644 --- a/mysql-test/main/subselect_sj2.result +++ b/mysql-test/main/subselect_sj2.result @@ -466,11 +466,20 @@ where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and t1.b=t2.b); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using where; FirstMatch(t2) Warnings: Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where `test`.`t1`.`a` = `test`.`t0`.`a` and `test`.`t2`.`a` = `test`.`t0`.`a` and `test`.`t2`.`b` = `test`.`t1`.`b` +Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where `test`.`t2`.`a` = `test`.`t0`.`a` and `test`.`t1`.`a` = `test`.`t0`.`a` and `test`.`t1`.`b` = `test`.`t2`.`b` +select * from t0 +where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and +t1.b=t2.b); +a +0 +1 +2 +3 +4 update t1 set a=3, b=11 where a=4; update t2 set b=11 where a=3; select * from t0 where t0.a in @@ -759,10 +768,14 @@ 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 <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((sj-nest)) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 +select 1 from t2 where +c2 in (select 1 from t3, t2) and +c1 in (select convert(c6,char(1)) from t2); +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.test b/mysql-test/main/subselect_sj2.test index be2d91571e7..5b9ec409c5d 100644 --- a/mysql-test/main/subselect_sj2.test +++ b/mysql-test/main/subselect_sj2.test @@ -604,6 +604,9 @@ insert into t2 select * from t1; explain extended select * from t0 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and t1.b=t2.b); +select * from t0 +where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and +t1.b=t2.b); # # BUG#46556 "Returning incorrect, empty results for some IN subqueries @@ -934,6 +937,9 @@ create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb; explain select 1 from t2 where c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); +select 1 from t2 where + c2 in (select 1 from t3, t2) and + c1 in (select convert(c6,char(1)) from t2); drop table t2, t3; diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result index b2ffb033788..8a7667d7639 100644 --- a/mysql-test/main/subselect_sj2_jcl6.result +++ b/mysql-test/main/subselect_sj2_jcl6.result @@ -477,11 +477,20 @@ where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and t1.b=t2.b); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using where; FirstMatch(t2); Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan Warnings: Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where `test`.`t1`.`a` = `test`.`t0`.`a` and `test`.`t2`.`a` = `test`.`t0`.`a` and `test`.`t2`.`b` = `test`.`t1`.`b` +Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where `test`.`t2`.`a` = `test`.`t0`.`a` and `test`.`t1`.`a` = `test`.`t0`.`a` and `test`.`t1`.`b` = `test`.`t2`.`b` +select * from t0 +where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and +t1.b=t2.b); +a +0 +1 +2 +3 +4 update t1 set a=3, b=11 where a=4; update t2 set b=11 where a=3; # Not anymore: @@ -772,10 +781,14 @@ 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 <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch((sj-nest)); Using join buffer (incremental, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2); Using join buffer (incremental, BNL join) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 +select 1 from t2 where +c2 in (select 1 from t3, t2) and +c1 in (select convert(c6,char(1)) from t2); +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_mat.result b/mysql-test/main/subselect_sj2_mat.result index b5eaa258410..e47f2e6f4aa 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -468,11 +468,20 @@ where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and t1.b=t2.b); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using where; FirstMatch(t2) Warnings: Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where `test`.`t1`.`a` = `test`.`t0`.`a` and `test`.`t2`.`a` = `test`.`t0`.`a` and `test`.`t2`.`b` = `test`.`t1`.`b` +Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where `test`.`t2`.`a` = `test`.`t0`.`a` and `test`.`t1`.`a` = `test`.`t0`.`a` and `test`.`t1`.`b` = `test`.`t2`.`b` +select * from t0 +where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and +t1.b=t2.b); +a +0 +1 +2 +3 +4 update t1 set a=3, b=11 where a=4; update t2 set b=11 where a=3; select * from t0 where t0.a in @@ -761,10 +770,14 @@ 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 <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((sj-nest)) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 +select 1 from t2 where +c2 in (select 1 from t3, t2) and +c1 in (select convert(c6,char(1)) from t2); +1 drop table t2, t3; # # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 @@ -1481,8 +1494,8 @@ t3.cat_id IN (SELECT cat_id FROM t2) AND t3.sack_id = 33479 AND t3.kit_id = 6; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 5 Using index -1 PRIMARY t2 ref cat_id cat_id 4 test.t3.cat_id 2 Using where; Using index; FirstMatch(t3) -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.cat_id 1 Using where; Using index +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.cat_id 1 Using index +1 PRIMARY t2 ref cat_id cat_id 4 test.t3.cat_id 2 Using where; Using index; FirstMatch(t1) SELECT count(*) FROM t1, t3 WHERE t1.cat_id = t3.cat_id AND t3.cat_id IN (SELECT cat_id FROM t2) AND @@ -1497,8 +1510,8 @@ t3.cat_id IN (SELECT cat_id FROM t4) AND t3.sack_id = 33479 AND t3.kit_id = 6; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 5 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.cat_id 1 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t4 index cat_id cat_id 4 NULL 19 Using index SELECT count(*) FROM t1, t3 WHERE t1.cat_id = t3.cat_id AND @@ -1513,8 +1526,8 @@ t3.cat_id IN (SELECT cat_id FROM t2) AND t3.sack_id = 33479 AND t3.kit_id = 6; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 5 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.cat_id 1 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where 2 MATERIALIZED t2 index cat_id cat_id 4 NULL 19 Using index SELECT count(*) FROM t1, t3 WHERE t1.cat_id = t3.cat_id AND @@ -1542,9 +1555,9 @@ EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON ( b1 = a2 ) WHERE ( b1, b1 ) IN ( SELECT a4, b4 FROM t3, t4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Start temporary -1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t1 ref idx idx 2 test.t4.a4 1 100.00 Using index; End temporary +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary +1 PRIMARY t1 ref idx idx 2 test.t4.a4 1 100.00 Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t4`) left join `test`.`t2` on(`test`.`t2`.`a2` = `test`.`t4`.`a4`) where `test`.`t4`.`b4` = `test`.`t4`.`a4` and `test`.`t1`.`b1` = `test`.`t4`.`a4` @@ -1560,13 +1573,12 @@ EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON ( b1 = a2 ) WHERE ( b1, b1 ) IN ( SELECT a4, b4 FROM t3, t4); 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 t4 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary 1 PRIMARY t1 ref idx idx 2 test.t4.a4 1 100.00 Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 -2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t4`) left join `test`.`t2` on(`test`.`t1`.`b1` = `test`.`t4`.`a4` and `test`.`t2`.`a2` = `test`.`t4`.`a4`) where `test`.`t4`.`b4` = `test`.`t4`.`a4` and `test`.`t1`.`b1` = `test`.`t4`.`a4` +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t4`) left join `test`.`t2` on(`test`.`t2`.`a2` = `test`.`t4`.`a4`) where `test`.`t4`.`b4` = `test`.`t4`.`a4` and `test`.`t1`.`b1` = `test`.`t4`.`a4` SELECT * FROM t1 LEFT JOIN t2 ON ( b1 = a2 ) WHERE ( b1, b1 ) IN ( SELECT a4, b4 FROM t3, t4); a1 b1 a2 b2 @@ -1827,8 +1839,8 @@ SELECT t2.id FROM t2,t1 WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 30 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 14 2 MATERIALIZED t1 eq_ref PRIMARY PRIMARY 4 test.t3.id 1 Using index SELECT t2.id FROM t2,t1 @@ -1933,19 +1945,19 @@ AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2 AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 18 Using index -1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 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 <subquery3> 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) -1 PRIMARY <subquery4> 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 -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 +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) +1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where 3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 -4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 33 Using index condition; Using where 2 MATERIALIZED t2_1 ALL id_t2,id_product NULL NULL NULL 223 Using where +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 +6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 31 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 9eac5c65f82..1f94a3e5695 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -1067,10 +1067,10 @@ AND t1.val IN (SELECT t3.val FROM t3 WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 13 func 1 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 13 func 1 -3 MATERIALIZED t3 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 13 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 5 Using where SELECT * FROM t1 WHERE t1.val IN (SELECT t2.val FROM t2 @@ -1621,9 +1621,9 @@ A.t1field IN (SELECT C.t2field FROM t2 C WHERE C.t2field IN (SELECT D.t2field FROM t2 D)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A index PRIMARY PRIMARY 4 NULL 3 Using index -1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; FirstMatch(A); Using join buffer (flat, BNL join) 1 PRIMARY C eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index 1 PRIMARY D eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index +1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; FirstMatch(D); Using join buffer (flat, BNL join) SELECT * FROM t1 A WHERE A.t1field IN (SELECT A.t1field FROM t2 B) AND @@ -2190,8 +2190,8 @@ explain SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t5 index a a 10 NULL 2 Using where; Using index; LooseScan -1 PRIMARY t4 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t5.b 2 Using where; FirstMatch(t5) +1 PRIMARY t2 ref b b 5 test.t5.b 2 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 FirstMatch(t5) 1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; Using join buffer (flat, BNL join) SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); a @@ -2271,11 +2271,11 @@ alias1.c IN (SELECT SQ3_alias1.b FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) LIMIT 100; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Start temporary -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) -1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary; Using join buffer (incremental, BNL join) -1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (incremental, BNL join) +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (incremental, BNL join) +1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Start temporary; Using join buffer (incremental, BNL join) +1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary; Using join buffer (incremental, BNL join) 2 DERIVED t2 ALL NULL NULL NULL NULL 20 create table t3 as SELECT @@ -3369,8 +3369,8 @@ SELECT * FROM t0 WHERE t0.a IN (SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where -1 PRIMARY t1 ref a a 5 test.t0.a 1 Start temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t1 ref a a 5 test.t0.a 1 Using where; Start temporary; End temporary; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT * FROM t0 WHERE t0.a IN (SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b); a diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index 97dcf07705c..42b922ac68a 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -332,11 +332,11 @@ where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 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 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 16 func,func 1 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 16 func,func 1 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and `test`.`t2`.`b1` > '0' and `test`.`t3`.`c2` > '0' select * from t1 @@ -353,11 +353,11 @@ where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i range it1i1,it1i2,it1i3 # # # 3 100.00 # -1 PRIMARY <subquery3> eq_ref distinct_key # # # 1 100.00 # 1 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # +1 PRIMARY <subquery3> eq_ref distinct_key # # # 1 100.00 # +2 MATERIALIZED t2i range it2i1,it2i2,it2i3 # # # 5 100.00 # 3 MATERIALIZED t3i range it3i1,it3i2,it3i3 # # # 4 100.00 # 3 MATERIALIZED t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # -2 MATERIALIZED t2i range it2i1,it2i2,it2i3 # # # 5 100.00 # Warnings: Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where `test`.`t2i`.`b1` = `test`.`t3i`.`c1` and `test`.`t2i`.`b2` = `test`.`t3i`.`c2` and `test`.`t2i`.`b1` > '0' and `test`.`t3i`.`c2` > '0' select * from t1i @@ -376,11 +376,11 @@ b2 in (select c2 from t3 where c2 LIKE '%03')) and where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 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 -1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where 5 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: @@ -402,15 +402,15 @@ b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); 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 -1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 5 MATERIALIZED t3c ALL NULL NULL NULL NULL 4 100.00 Using where 5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3c.c1,test.t3c.c2 1 100.00 Using index 4 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where 3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where `test`.`t2i`.`b1` = `test`.`t3c`.`c1` and `test`.`t2`.`b1` = `test`.`t1`.`a1` and `test`.`t2i`.`b2` = `test`.`t3c`.`c2` and `test`.`t2`.`b2` = `test`.`t1`.`a2` and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where `test`.`t3a`.`c1` = `test`.`t1`.`a1` and <cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where `test`.`t3b`.`c2` like '%03' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery4>`.`c2`))))) and `test`.`t3c`.`c2` > '0' +Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where `test`.`t2`.`b1` = `test`.`t1`.`a1` and `test`.`t2i`.`b1` = `test`.`t3c`.`c1` and `test`.`t2`.`b2` = `test`.`t1`.`a2` and `test`.`t2i`.`b2` = `test`.`t3c`.`c2` and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where `test`.`t3a`.`c1` = `test`.`t1`.`a1` and <cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where `test`.`t3b`.`c2` like '%03' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery4>`.`c2`))))) and `test`.`t3c`.`c2` > '0' select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 t3a where c1 = a1) or @@ -435,19 +435,19 @@ where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL # # # 3 100.00 # -1 PRIMARY <subquery5> eq_ref distinct_key # # # 1 100.00 # 1 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # +1 PRIMARY <subquery5> eq_ref distinct_key # # # 1 100.00 # +2 MATERIALIZED t2 ALL NULL # # # 5 100.00 # 5 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # -2 MATERIALIZED t2 ALL NULL # # # 5 100.00 # 4 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 3 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 7 UNION t1i range it1i1,it1i2,it1i3 # # # 3 100.00 # -7 UNION <subquery9> eq_ref distinct_key # # # 1 100.00 # 7 UNION <subquery8> eq_ref distinct_key # # # 1 100.00 # +7 UNION <subquery9> eq_ref distinct_key # # # 1 100.00 # +8 MATERIALIZED t2i range it2i1,it2i2,it2i3 # # # 5 100.00 # 9 MATERIALIZED t3i range it3i1,it3i2,it3i3 # # # 4 100.00 # 9 MATERIALIZED t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # -8 MATERIALIZED t2i range it2i1,it2i2,it2i3 # # # 5 100.00 # NULL UNION RESULT <union1,7> ALL NULL # # # NULL NULL # Warnings: Note 1003 (/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#3 */ select `test`.`t3`.`c2` from `test`.`t3` where `test`.`t3`.`c2` like '%02' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery3>`.`c2`)))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3`.`c2` from `test`.`t3` where `test`.`t3`.`c2` like '%03' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery4>`.`c2`))))) and `test`.`t3`.`c2` > '0') union (/* select#7 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where `test`.`t2i`.`b1` = `test`.`t3i`.`c1` and `test`.`t2i`.`b2` = `test`.`t3i`.`c2` and `test`.`t2i`.`b1` > '0' and `test`.`t3i`.`c2` > '0') @@ -542,15 +542,15 @@ b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2)); 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 t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 1 100.00 Using index; Start temporary 1 PRIMARY t3c ALL NULL NULL NULL NULL 4 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 4 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where 3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where `test`.`t2i`.`b1` = `test`.`t1`.`a1` and `test`.`t3c`.`c1` = `test`.`t1`.`a1` and `test`.`t2`.`b1` = `test`.`t1`.`a1` and `test`.`t2i`.`b2` = `test`.`t1`.`a2` and `test`.`t3c`.`c2` = `test`.`t1`.`a2` and `test`.`t2`.`b2` = `test`.`t1`.`a2` and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where `test`.`t3a`.`c1` = `test`.`t1`.`a1` and <cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where `test`.`t3b`.`c2` like '%03' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery4>`.`c2`))))) +Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where `test`.`t2`.`b1` = `test`.`t1`.`a1` and `test`.`t2i`.`b1` = `test`.`t1`.`a1` and `test`.`t3c`.`c1` = `test`.`t1`.`a1` and `test`.`t2`.`b2` = `test`.`t1`.`a2` and `test`.`t2i`.`b2` = `test`.`t1`.`a2` and `test`.`t3c`.`c2` = `test`.`t1`.`a2` and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where `test`.`t3a`.`c1` = `test`.`t1`.`a1` and <cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where `test`.`t3b`.`c2` like '%03' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery4>`.`c2`))))) explain extended select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); id select_type table type possible_keys key key_len ref rows filtered Extra diff --git a/mysql-test/main/subselect_sj_nonmerged.result b/mysql-test/main/subselect_sj_nonmerged.result index 422af02c31a..a3e6c493930 100644 --- a/mysql-test/main/subselect_sj_nonmerged.result +++ b/mysql-test/main/subselect_sj_nonmerged.result @@ -77,8 +77,8 @@ explain select * from t4 where t4.a in (select max(t2.a) from t1, t2 group by t2.b) and t4.b in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) 1 PRIMARY t4 eq_ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) diff --git a/sql/my_json_writer.cc b/sql/my_json_writer.cc index 54eb8423caf..81024a73a83 100644 --- a/sql/my_json_writer.cc +++ b/sql/my_json_writer.cc @@ -159,8 +159,10 @@ Json_writer& Json_writer::add_member(const char *name, size_t len) auto is_uniq_key= emplaced.second; if(!is_uniq_key) { +#ifdef QQQ sql_print_error("Duplicated key: %s\n", emplaced.first->c_str()); VALIDITY_ASSERT(is_uniq_key); +#endif /* QQQ */ } } #endif diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 1e30d8f3644..cdc292f8006 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -7360,6 +7360,7 @@ SHOW_VAR status_vars[]= { {"Handler_update", (char*) offsetof(STATUS_VAR, ha_update_count), SHOW_LONG_STATUS}, {"Handler_write", (char*) offsetof(STATUS_VAR, ha_write_count), SHOW_LONG_STATUS}, {"Key", (char*) &show_default_keycache, SHOW_FUNC}, + {"optimizer_join_prefixes_check_calls", (char*) offsetof(STATUS_VAR, optimizer_join_prefixes_check_calls), SHOW_LONG_STATUS}, {"Last_query_cost", (char*) offsetof(STATUS_VAR, last_query_cost), SHOW_DOUBLE_STATUS}, #ifndef DBUG_OFF {"malloc_calls", (char*) &malloc_calls, SHOW_LONG}, diff --git a/sql/sql_class.h b/sql/sql_class.h index 806f77c1ba2..c8a4560885f 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -939,6 +939,7 @@ typedef struct system_status_var ulong filesort_rows_; ulong filesort_scan_count_; ulong filesort_pq_sorts_; + ulong optimizer_join_prefixes_check_calls; /* Features used */ ulong feature_custom_aggregate_functions; /* +1 when custom aggregate diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 2a5629237e5..da189daf2b2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5219,6 +5219,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, int error= 0; TABLE *UNINIT_VAR(table); /* inited in all loops */ uint i,table_count,const_count,key; + uint sort_space; table_map found_const_table_map, all_table_map; key_map const_ref, eq_part; bool has_expensive_keyparts; @@ -5236,6 +5237,13 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, table_count=join->table_count; /* + best_extension_by_limited_search need sort space for 2POSITIION + objects per remaining table, which gives us + 2*(T + T-1 + T-2 + T-3...1 POSITIONS) = 2*(T+1)/2*T = (T*T+T) + */ + join->sort_space= sort_space= (table_count*table_count + table_count); + + /* best_positions is ok to allocate with alloc() as we copy things to it with memcpy() */ @@ -5246,6 +5254,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, &stat_vector, sizeof(JOIN_TAB*)* (table_count +1), &table_vector, sizeof(TABLE*)*(table_count*2), &join->positions, sizeof(POSITION)*(table_count + 1), + &join->sort_positions, sizeof(POSITION)*(sort_space), &join->best_positions, sizeof(POSITION)*(table_count + 1), NullS)) @@ -5257,6 +5266,8 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, /* Initialize POSITION objects */ for (i=0 ; i <= table_count ; i++) (void) new ((char*) (join->positions + i)) POSITION; + for (i=0 ; i <= sort_space ; i++) + (void) new ((char*) (join->sort_positions + i)) POSITION; join->best_ref= stat_vector; @@ -5423,6 +5434,17 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, } } + { + for (JOIN_TAB *s= stat ; s < stat_end ; s++) + { + TABLE_LIST *tl= s->table->pos_in_table_list; + if (tl->embedding && tl->embedding->sj_subq_pred) + { + s->embedded_dependent= tl->embedding->original_subq_pred_used_tables; + } + } + } + if (thd->trace_started()) trace_table_dependencies(thd, stat, join->table_count); @@ -8272,10 +8294,13 @@ best_access_path(JOIN *join, */ if (s->key_start_dependent) key_dependent= s->key_dependent; + /* Add dependencey for sub queries */ + key_dependent|= s->embedded_dependent; } /* Check that s->key_dependent contains all used_tables found in s->keyuse */ key_dependent&= ~PSEUDO_TABLE_BITS; - DBUG_ASSERT((key_dependent & s->key_dependent) == key_dependent); + DBUG_ASSERT((key_dependent & (s->key_dependent | s->embedded_dependent)) == + key_dependent); /* If there is no key to access the table, but there is an equi-join @@ -9181,6 +9206,7 @@ greedy_search(JOIN *join, : ~(table_map)0)); + join->next_sort_position= join->sort_positions; do { /* Find the extension of the current QEP with the lowest cost */ join->best_read= DBL_MAX; @@ -9244,13 +9270,13 @@ greedy_search(JOIN *join, while (pos && best_table != pos) pos= join->best_ref[++best_idx]; DBUG_ASSERT((pos != NULL)); // should always find 'best_table' + /* - move 'best_table' at the first free position in the array of joins, - keeping the sorted table order intact + Move 'best_table' at the first free position in the array of joins + We don't need to keep the array sorted as + best_extension_by_limited_search() will sort them. */ - memmove(join->best_ref + idx + 1, join->best_ref + idx, - sizeof(JOIN_TAB*) * (best_idx - idx)); - join->best_ref[idx]= best_table; + swap_variables(JOIN_TAB*, join->best_ref[idx], join->best_ref[best_idx]); /* compute the cost of the new plan extended with 'best_table' */ record_count= COST_MULT(record_count, join->positions[idx].records_read); @@ -9855,6 +9881,34 @@ check_if_edge_table(POSITION *pos, } +struct SORT_POSITION +{ + JOIN_TAB **join_tab; + POSITION *position; +}; + + +/* + Sort SORT_POSITIONS according to expected number of rows found + If number of combinations are the same sort according to join_tab order + (same table order as used in the original SQL query) +*/ + +static int +sort_positions(SORT_POSITION *a, SORT_POSITION *b) +{ + int cmp; + if ((cmp= compare_embedding_subqueries(*a->join_tab, *b->join_tab)) != 0) + return cmp; + + if (a->position->records_read > b->position->records_read) + return 1; + if (a->position->records_read < b->position->records_read) + return -1; + return CMP_NUM(*a->join_tab, *b->join_tab); +} + + /** Find a good, possibly optimal, query execution plan (QEP) by a possibly exhaustive search. @@ -9994,14 +10048,18 @@ best_extension_by_limited_search(JOIN *join, 'join' is a partial plan with lower cost than the best plan so far, so continue expanding it further with the tables in 'remaining_tables'. */ - JOIN_TAB *s, **pos; + JOIN_TAB *s; double best_record_count= DBL_MAX; double best_read_time= DBL_MAX; bool disable_jbuf= join->thd->variables.join_cache_level == 0; enum_best_search best_res; + uint tables_left= join->table_count - idx, found_tables; + POSITION *sort_position= join->next_sort_position; + SORT_POSITION *sort= (SORT_POSITION*) alloca(sizeof(SORT_POSITION)*tables_left); + SORT_POSITION *sort_end; DBUG_ENTER("best_extension_by_limited_search"); - DBUG_EXECUTE_IF("show_explain_probe_best_ext_lim_search", + DBUG_EXECUTE_IF("show_explain_probe_best_ext_lim_search", if (dbug_user_var_equals_int(thd, "show_explain_probe_select_id", join->select_lex->select_number)) @@ -10013,6 +10071,7 @@ best_extension_by_limited_search(JOIN *join, DBUG_EXECUTE("opt", print_plan(join, idx, record_count, read_time, read_time, "part_plan");); + status_var_increment(thd->status_var.optimizer_join_prefixes_check_calls); /* If we are searching for the execution plan of a materialized semi-join nest @@ -10022,21 +10081,55 @@ best_extension_by_limited_search(JOIN *join, if (join->emb_sjm_nest) allowed_tables= join->emb_sjm_nest->sj_inner_tables & ~join->const_table_map; - for (pos= join->best_ref + idx ; (s= *pos) ; pos++) { - table_map real_table_bit= s->table->map; - DBUG_ASSERT(remaining_tables & real_table_bit); + Json_writer_object trace_one_table(thd); + if (unlikely(thd->trace_started())) + trace_plan_prefix(join, idx, remaining_tables); + + /* + Sort tables in ascending order of generated row combinations + */ + sort_end= sort; + for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++) + { + table_map real_table_bit= s->table->map; + DBUG_ASSERT(remaining_tables & real_table_bit); - swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); + if ((allowed_tables & real_table_bit) && + !(remaining_tables & s->dependent)) + { + sort_end->join_tab= pos; + sort_end->position= sort_position; + + if (unlikely(thd->trace_started())) + trace_one_table.add_table_name(s); + + /* Find the best access method from 's' to the current partial plan */ + best_access_path(join, s, remaining_tables, join->positions, idx, + disable_jbuf, record_count, + sort_position, sort_position + 1); + sort_end++; + sort_position+= 2; + } + } + found_tables= sort_end - sort; + if (found_tables > 1) + my_qsort(sort, found_tables, sizeof(SORT_POSITION), + (qsort_cmp) sort_positions); + } + join->next_sort_position+= found_tables*2; + DBUG_ASSERT(join->next_sort_position <= + join->sort_positions + join->sort_space); - if ((allowed_tables & real_table_bit) && - !(remaining_tables & s->dependent) && - !check_interleaving_with_nj(s)) + for (SORT_POSITION *pos= sort ; pos < sort_end ; pos++) + { + s= *pos->join_tab; + if (!check_interleaving_with_nj(s)) { + table_map real_table_bit= s->table->map; double current_record_count, current_read_time; double partial_join_cardinality; - POSITION *position= join->positions + idx; - POSITION loose_scan_pos; + POSITION *position= join->positions + idx, *loose_scan_pos; Json_writer_object trace_one_table(thd); if (unlikely(thd->trace_started())) @@ -10045,9 +10138,8 @@ best_extension_by_limited_search(JOIN *join, trace_one_table.add_table_name(s); } - /* Find the best access method from 's' to the current partial plan */ - best_access_path(join, s, remaining_tables, join->positions, idx, - disable_jbuf, record_count, position, &loose_scan_pos); + *position= *pos->position; // Get stored result + loose_scan_pos= pos->position+1; /* Compute the cost of the new plan extended with 's' */ current_record_count= COST_MULT(record_count, position->records_read); @@ -10066,7 +10158,7 @@ best_extension_by_limited_search(JOIN *join, trace_one_table.add("cost_for_plan", current_read_time); } optimize_semi_joins(join, remaining_tables, idx, ¤t_record_count, - ¤t_read_time, &loose_scan_pos); + ¤t_read_time, loose_scan_pos); /* Expand only partial plans with lower cost than the best QEP so far */ if (current_read_time >= join->best_read) @@ -10149,6 +10241,8 @@ best_extension_by_limited_search(JOIN *join, { /* Recursively expand the current partial plan */ Json_writer_array trace_rest(thd, "rest_of_plan"); + + swap_variables(JOIN_TAB*, join->best_ref[idx], *pos->join_tab); best_res= best_extension_by_limited_search(join, remaining_tables & @@ -10159,6 +10253,8 @@ best_extension_by_limited_search(JOIN *join, search_depth - 1, prune_level, use_cond_selectivity); + swap_variables(JOIN_TAB*, join->best_ref[idx], *pos->join_tab); + if ((int) best_res < (int) SEARCH_OK) goto end; // Return best_res if (best_res == SEARCH_FOUND_EDGE && @@ -10212,19 +10308,7 @@ best_extension_by_limited_search(JOIN *join, best_res= SEARCH_OK; end: - /* Restore original table order */ - if (!*pos) - pos--; // Revert last pos++ in for loop - if (pos != join->best_ref + idx) - { - JOIN_TAB *tmp= join->best_ref[idx]; - uint elements= (uint) (pos - (join->best_ref + idx)); - - memmove((void*) (join->best_ref + idx), - (void*) (join->best_ref + idx + 1), - elements * sizeof(JOIN_TAB*)); - *pos= tmp; - } + join->next_sort_position-= found_tables*2; DBUG_RETURN(best_res); } diff --git a/sql/sql_select.h b/sql/sql_select.h index 8accf0c05a1..eec073179f3 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -359,6 +359,13 @@ typedef struct st_join_table { table_map dependent,key_dependent; /* + This is set for embedded sub queries. It contains the table map of + the outer expression, like 'A' in the following expression: + WHERE A in (SELECT ....) + */ + table_map embedded_dependent; + + /* 1 - use quick select 2 - use "Range checked for each record" */ @@ -1286,10 +1293,12 @@ public: /* Finally picked QEP. This is result of join optimization */ POSITION *best_positions; + POSITION *sort_positions; /* Temporary space used by greedy_search */ + POSITION *next_sort_position; /* Next free space in sort_positions */ Pushdown_query *pushdown_query; JOIN_TAB *original_join_tab; - uint original_table_count; + uint original_table_count, sort_space; /******* Join optimization state members start *******/ /* |