• unknown's avatar
    MWL#68 Subquery optimization: Efficient NOT IN execution with NULLs · 5515bcba
    unknown authored
    This patch implements correct NULL semantics for materialized subquery execution.
    The implementation has the following properties and main limitations:
    - It passes all query result tests, but fails a number of EXPLAIN tests because of
      changed plans.
    - The EXPLAIN output for partial matching is not decided yet.
    - It works only when all necessary indexes fit into main memory. Notice that these
      are not the general B-tree/Hash indexes, but instead much more compact ones,
      therefore this limitation may not be a problem in many practical cases.
    - It doesn't contain specialized tests.
    - In several places the implementation uses methods that are modified copies of
      other similar methods. These cases need to be refactored to avoid code duplication.
    - Add a test if the predicate is top-level just before deciding on partial matching.
      If it is top-level, use a more efficient exec method (index lookup).
    - Add sorting of indexes according to their selectivity. The code is almost there.
    - Needs more comments, and to sync existing ones with the implementation.
    
    sql/item_cmpfunc.h:
      Expose the Arg_comparator of a comparison predicate. This makes it possible to
      directly get the comparison result {-1,0,1}, which is not possible through the
      val_XXX() methods which "fold" such results into a boolean.
    sql/item_subselect.cc:
      The core of the implementation of MWL#68.
    sql/item_subselect.h:
      The core of the implementation of MWL#68.
    sql/opt_subselect.cc:
      Removed the limitation for materialized subquery execution that it is applicable only
      for top-level predicates.
    sql/sql_class.cc:
      New class select_materialize_with_stats that collects data statistics about
      the data being inserted into the target table.
    sql/sql_class.h:
      New class select_materialize_with_stats that collects data statistics about
      the data being inserted into the target table.
    sql/sql_select.cc:
      - more complete initialization of the TABLE object of a temp table.
      - call setup_subquery_materialization at one more exit point.
    5515bcba
sql_select.cc 598 KB