bzr push into mysql-trunk branch (roy.lyseng:3906 to 3907) Bug#13971022

[Roy Lyseng]

 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).