From: Roy Lyseng Date: May 11 2012 12:42pm Subject: bzr push into mysql-trunk branch (roy.lyseng:3906 to 3907) Bug#13971022 List-Archive: http://lists.mysql.com/commits/144120 X-Bug: 13971022 Message-Id: <201205111242.q4BCgR5q021029@khepri07.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3907 Roy Lyseng 2012-05-11 Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key... Followup of review comments. sql/sql_planner.cc Removed a redundant code line. modified: sql/sql_planner.cc 3906 Roy Lyseng 2012-05-04 Bug#13980954: Missing data on left join + null value + where..in Patch 2 of 2. This problem occurs with a MaterializeLookup semi-join strategy and an outer join in the outer query. Here is a query that suffers from the problem (it is added as test case): SELECT t2.vc, t2.ik AS t2_ik, t1.ik AS t1_ik FROM t2 LEFT JOIN t1 ON t2.vc=t1.vc WHERE t2.vc IN (SELECT vc FROM t2 AS t3); The cost optimizer decides this table order as optimal: t2 - t3 - t1, with materialization for table t3. make_join_select is then called with where condition: t3.vc = t2.vc join condition: t3.vc = t2.vc and t1.vc = t2.vc The where condition is easily attached to table t3, but the join condition cannot be handled before we reach table t1, because it must be triggered by a "found" condition on table t1. Thus, for table t1 we produce a condition that requires that fields from tables t2, t3 and t1 to be available. However, t3 was materialized with a lookup strategy, which makes it NOT available when processing a row from table t1. We can thus fix the problem in two ways: 1) make sure that fields from table t1 are available, or 2) make sure that predicates involving table t3 are not evaluated at this point. Choice 2) is an optimizer-only solution, which sounds more attractive than extending the execution engine. Why can part of the condition be eliminated? We see that the predicate which accesses non-existing data is t3.vc = t2.vc. But this predicate is generated by including a multiple equality from the where condition, so it is clearly redundant. We might have a predicate between tables t3 and t1 in the join condition, but that would violate the premises for MaterializeLookup strategy. Hence, we need not generate the predicate as part of the join condition. Patch 1 of this bugfix was supposed to eliminate such redundant predicates from a join condition, but due to some code in eliminate_item_equal() that adds back those predicates when a materialized semi-join nest is present, another technique is needed for this case. The problem lies in pushdown_on_conditions(). It recalculates used_tables information for the condition, but "forgets" that tables from the materialization semi-join nest are not available for table t1. Notice that this is contrary to the MaterializeScan strategy, which copies back fields from the materialized table into the source tables' buffers! The best solution seems to be to isolate calculation of used_tables information per join_tab into a separate function. The calculation becomes increasingly complex, and used_tables information has to be used in four different places in the code (make_join_select(), pushdown_on_conditions, JOIN::set_access_methods() and test_if_skip_sort_order()). We therefore pre-calculate the set of available tables (prefix_tables_map) and the tables added for one join_tab (added_tables_map) and place those data items into the JOIN_TAB class. mysql-test/include/subquery_sj.inc Test case for bug#13980954. mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_bka.result mysql-test/r/subquery_sj_all_bka_nixbnl.result mysql-test/r/subquery_sj_all_bkaunique.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_bka.result mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result mysql-test/r/subquery_sj_dupsweed_bkaunique.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_bka.result mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result mysql-test/r/subquery_sj_firstmatch_bkaunique.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bka_nixbnl.result mysql-test/r/subquery_sj_mat_bkaunique.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_bka.result mysql-test/r/subquery_sj_none_bka_nixbnl.result mysql-test/r/subquery_sj_none_bkaunique.result Test case results for bug#13980954. sql/sql_optimizer.cc Added function JOIN::set_prefix_tables() that calculates available tables for the current table order, after join optimization is performed. Updated pushdown_on_conditions() to use prefix_tables_map and added_tables_map. Updated make_join_select() to use prefix_tables_map and added_tables_map. sql/sql_optimizer.h Added new class JOIN member function: set_prefix_tables(). sql/sql_select.cc Updated JOIN::set_access_methods() and test_if_skip_sort_order() to use prefix_tables() and added_tables(). sql/sql_select.h Added two new fields in class JOIN_TAB: prefix_tables_map: Tables available in the join prefix. added_tables_map: Tables added in this join_tab, relative to previous. Also added interface functions for the new fields. modified: mysql-test/include/subquery_sj.inc mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_bka.result mysql-test/r/subquery_sj_all_bka_nixbnl.result mysql-test/r/subquery_sj_all_bkaunique.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_bka.result mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result mysql-test/r/subquery_sj_dupsweed_bkaunique.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_bka.result mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result mysql-test/r/subquery_sj_firstmatch_bkaunique.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bka_nixbnl.result mysql-test/r/subquery_sj_mat_bkaunique.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_bka.result mysql-test/r/subquery_sj_none_bka_nixbnl.result mysql-test/r/subquery_sj_none_bkaunique.result sql/sql_optimizer.cc sql/sql_optimizer.h sql/sql_select.cc sql/sql_select.h === modified file 'sql/sql_planner.cc' --- a/sql/sql_planner.cc 2012-05-03 14:02:25 +0000 +++ b/sql/sql_planner.cc 2012-05-11 12:40:38 +0000 @@ -2556,8 +2556,6 @@ bool Optimize_table_order::fix_semijoin_ handled_tables|= join->best_positions[i].table->table->map; } - if (tableno != first) - pos->sj_strategy= SJ_OPT_NONE; remaining_tables |= pos->table->table->map; } No bundle (reason: useless for push emails).