Details
Major Description
Discovered this while reviewing the patch
Author: Michael Widenius <monty@mariadb.org>
|
Date: Sun May 15 15:46:29 2022 +0300
|
|
|
greedy_search() and best_extension_by_limited_search() scrambled table order
|
|
best_extension_by_limited_search() assumes that tables should be sorted
|
according to size to be able to quickly disregard bad plans. However the
|
current usage of swap_variables() will change the table order to a not
|
The patch changes which join orders are considered, and potentially the order the join orders are enumerated.
One of the .result changes in the patch:
@@ -596,8 +596,8 @@ limit 10;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort
|
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00
|
-1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 7.03 Using where
|
-1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 100.00 Using where; FirstMatch(supplier)
|
+1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 7.03 Using where; Start temporary; Using join buffer (flat, BNL join)
|
+1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 100.00 Using where; End temporary
|
That is
-1 PRIMARY part ... Using where
|
+1 PRIMARY part ... Using where; Start temporary; Using join buffer (flat, BNL join)
|
|
|
-1 PRIMARY partsupp ... Using where; FirstMatch(supplier)
|
+1 PRIMARY partsupp ... Using where; End temporary
|
The join order is NOT changed while the semi-join strategy IS changed. This should not happen.