3394 Roy Lyseng 2011-06-27
Bug#11752543: Prepared query with nested subqueries core dump on second exec.
Also known as bug#43768
The problem here is that pullout of semi-join tables is attempted for
each execution, but because those tables are not "pushed back" again
after each execution, the pullout fails on second attempt.
The solution chosen here is to pullout only those semi-join tables that are
functionally dependent upon the outer tables. This pullout operation
need to be performed only once, and, unlike the current procedure, is not
dependent upon the row count of the involved tables.
Pullout of semi-join tables based on rowcount (tables that we know
contain exactly zero or one rows) is abandoned all together.
The practical implication of this change is as follows:
- Only outer tables containing zero or one rows will now be extracted
as "const tables". Thus, such tables from a semijoin nest are no
longer accessed during optimization, and some (rare) optimizations
are no longer possible.
- In the majority of cases, there is no performance impact. Often,
the new strategy chosen is Materialization, meaning that the row
of these table is accessed only once and saved in local memory.
Whether it is accessed during optimization or execution should not
affect performance.
- Const table analysis is now done in two phases:
1) Extract tables based on dependencies. Both regular and semi-joined
tables may apply, and
2) Extract tables based on row count. Semi-joined tables are not
handled in this phase.
In order to implement this, make_join_statistics() has been restructured
slightly. The conditional logic within the function has also been
enhanced for better readability.
The logic of make_join_statistics() is now as follows:
1. Initialize JOIN data structures
(major part of first loop in existing implementation).
2. Update dependencies based on join information
(the Warshall algorithm).
3. Make key descriptions (update_ref_and_keys()).
4. Pull out semi-join tables based on identified dependencies,
called only during the first optimization.
5. Extract tables with zero or one rows as const tables
(Semi-joined tables are not handled).
6. Extract dependent tables as const tables.
(Semi-joined tables are not handled).
7. The remaining parts of the function.
mysql-test/include/subquery_sj.inc
Added test case for bug#11752543.
Extended "join loop" with prepared statement testing.
mysql-test/r/select_found.result
Possible keys changed from "PRIMARY,kid" to "kid".
Happens because analysis order is slightly changed, but harmless as
the table is identified as "const".
mysql-test/r/subquery_mat_all.result
Plan changes because const table pullout is removed for semi-join
tables.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
Added test case for bug#11752543
A number of plan changes because of extensive testing of semi-join
tables with 0, 1 and 2 rows.
sql/sql_select.cc
pull_out_semijoin_tables()
Pullout based on const tables removed.
Comments updated.
make_join_statistics()
Removed const table pullout from first loop.
Simplified testing based on inner/outer/semi-join properties.
Calls pull_out_semijoin_tables() just after dependency analysis.
Then, added loop that performs pullout based on row count
but excludes semi-joined tables.
Notice also that we no longer delete outer-join dependencies
based on row count. This has some implications for EXPLAIN, but
all plans are still the same.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/select_found.result
mysql-test/r/subquery_mat_all.result
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
sql/sql_select.cc
3393 Roy Lyseng 2011-06-27
Bug#12603183: Segfault in hp_movelink
The bug manifests itself sometimes as a segmentation fault,
sometimes as a Valgrind warning. Consistent faulting has been
difficult to achieve.
The problem is with the Materialization scan semi-join strategy.
When materialization is done, the read_first_record function
pointer is replaced with a function to read from the materialized
table instead. This strategy works when the materialization is
performed once per query, even when the materialized table is
read multiple times. However, if the materialization is performed
multiple times, such as when called from within another subquery,
the original function pointer is never restored, and the wrong
function is used to read from the subquery tables when materializing.
The solution for the problem is to save the original function pointer
in the save_read_first_record field of the join_tab and restore
it for every new materialization.
save_read_first_record is now used both from materialize-scan and
from subqueries that toggle between ref access and full table scan.
We could imagine a query that used both strategies at the same time:
SELECT ... FROM ot...
WHERE ocol NOT IN (SELECT ... FROM mt...
WHERE mcol IN (SELECT ... FROM it ...))
Where the inner-most subquery is transformed into a semi-join and
materialize-scan strategy is selected, and the NOT IN alternates
betwen a ref access and a full table scan on its inner table.
However, the ref access would have to be against table "mt", but the
table being materialized in this case is "it".
Hence, I think this scenario is not possible.
Notice that there are still some result differences, which would
not be seen with the original "LIMIT 1" specification.
It seems that Materialization scan is still slightly broken when
used together with an outer join. This problem will be looked at
in the context of WL#5561. Notice also that when semi-join
transformation for outer joins is enabled, both subqueries of this
query will be converted, and the materialization will be performed
only once, avoiding the entire problem. Hence, to reproduce this
problem in context of WL#5561, make sure that the outer subquery
is not transformed.
mysql-test/include/subquery_sj.inc
Added test case for bug#12603183.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
Updated with test results for bug#12603183.
sql/sql_select.cc
sub_select_sjm():
Save read function pointer into save_read_first_record on first
materialization, and restore it on subsequent materializations.
Deleted a DBUG_ASSERT that seemed redundant, and moved setting
of sjm->materialized to a better place.
sql/sql_select.h
Updated comment for join_tab field save_read_first_record.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
sql/sql_select.cc
sql/sql_select.h
Diff too large for email (40002 lines, the limit is 10000).
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3393 to 3394) Bug#11752543 | Roy Lyseng | 27 Jun |
