List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:January 16 2012 2:01pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3518 to 3519) Bug#13591383
View as plain text  
 3519 Roy Lyseng	2012-01-16
      Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive()) ...
      
      Regression after bugfix for bug#13552968.
      This bugfix added an assertion that made sure that we did not have an
      "expensive" condition on a const table with one row that was outer
      joined. However, it also fired in the case of an empty outer joined table.
      Note that this problem occurs with materialized (expensive) subqueries
      but not with subqueries transformed into semi-joins and not with
      "regular" subqueries.
      
      mysql-test/include/subquery_mat.inc
        Added test case for bug#13591383.
      
      mysql-test/r/subquery_mat.result
      mysql-test/r/subquery_mat_all.result
      mysql-test/r/subquery_mat_none.result
        Added test case results for bug#13591383.
      
      sql/sql_executor.cc
        Modified debug assert to ensure that it does not fire when the
        outer-joined table is empty.
      
      sql/sql_executor.cc
        Added a grep tag.

    modified:
      mysql-test/include/subquery_mat.inc
      mysql-test/r/subquery_mat.result
      mysql-test/r/subquery_mat_all.result
      mysql-test/r/subquery_mat_none.result
      sql/sql_executor.cc
      sql/sql_optimizer.cc
 3518 Guilhem Bichot	2012-01-16
      Fix for Bug #13589848 "MISSING ROW ON SELECT WITH NESTED IN CLAUSES WHEN LOOSESCAN=ON"
      Forgot to reset some state variable.
     @ mysql-test/r/subquery_sj_all.result
        without the code fix, SELECT would return no row.
     @ sql/sql_executor.cc
        The bugfix. Scenario of the bug follow.
        Execution plan is:
        GP2 (=t3, system table) - GP1 (=t1) - subquery.
        Subquery is handled with IN->EXISTS. Inside this subquery there is
        a semijoin, with t1 as outer table:
        t2 (loosescan) - t3 (outer-joined-to-t2, firstmatch-to-t2) - t1
        We read first row of GP2(=t1): it is 'm' (we note rows by
        their col_varchar_key value).
        We execute the subquery:
        - we read first (and single) index value of t2: it is 'b'.
        - we read t3, the outer join condition doesn't match ('b'<>'k'),
        we NULL-complement columns of t3
        - all conditions on this partial row are met: t3->found_match is set
        to true.
        - we finally read t1, which has this attached equality:
        ( (t1.col_varchar_nokey = t2.col_varchar_key) and
          (<cache>(GP1.col_varchar_nokey) = t1.col_varchar_nokey) and
          (<cache>(GP1.col_varchar_nokey) = t1.col_varchar_nokey) ),
        the two <cache> equalities are injected by IN->EXISTS and they are
        false because the row of GP1 is 'm' and the row of t2 is 'b'.
        So, no row in the query's result set so far.
        Back to the beginning of the nested loop, we now read the second row of
        GP1: 'b'. This should match... But things go like this:
        We execute the subquery (this is the second time):
        - we read first (and single) index value of t2: it is 'b'.
        - we enter this code (still at join_tab==t2):
            else if (join_tab->loosescan_match_tab && 
                     join_tab->loosescan_match_tab->found_match),
        join_tab->loosescan_match_tab is t3, and t3->found_match is true
        (from the previous subquery execution!), so we
        compare keys (new 'b', versus old 'b' of previous subquery execution!),
        find they are identical so decide to skip this value (set 'found' to false).
        As there is no other key in t2, the loop stops there, and this is wrong;
        no row will be emitted.
        In short, this "key comparison logic" is valid as long as we stay at
        the loosescan table or below. When we finished executing the subquery
        the first time, we "went out" of t2, so should have reset the logic.
        Sub_select(join_tab) does join_tab->found_match=false, but this
        didn't work: in our case, the second subquery execution goes through:
        sub_select(t2) -> evaluate_join_record(t2) -> testing old 't3->found_match'
        without going through sub_select(t3), as t3 is deeper down.
        The fix is: in sub_select() for the first loosescan table, reset
        'found_match' of the last table of the loosescan range.
        This is like we do for outer joins: in outer joins, sub_select() for
        the outer table cares for resetting some properties of inner tables.
     @ sql/sql_optimizer.cc
        renamed macro to clearer name
     @ sql/sql_planner.cc
        comment. renamed macro to clearer name
     @ sql/sql_select.h
        comment. renamed macro to clearer name

    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_executor.cc
      sql/sql_optimizer.cc
      sql/sql_planner.cc
      sql/sql_select.h
=== modified file 'mysql-test/include/subquery_mat.inc'
--- a/mysql-test/include/subquery_mat.inc	2012-01-09 14:19:49 +0000
+++ b/mysql-test/include/subquery_mat.inc	2012-01-16 14:00:28 +0000
@@ -961,3 +961,23 @@ eval $query;
 DROP TABLE t1, t2, t3;
 
 --echo # End of test for bug#13552968
+
+--echo #
+--echo # Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive())
+--echo # in join_read_const_table()
+--echo #
+
+CREATE TABLE t1 (v INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES(1);
+
+CREATE TABLE t2 (v INTEGER) ENGINE=MyISAM;
+
+SELECT *
+FROM t1 LEFT JOIN t2
+     ON t2.v IN(SELECT v FROM t1);
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#13591383.
+
+--echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_mat.result'
--- a/mysql-test/r/subquery_mat.result	2012-01-09 14:19:49 +0000
+++ b/mysql-test/r/subquery_mat.result	2012-01-16 14:00:28 +0000
@@ -1232,4 +1232,19 @@ col_varchar_nokey	col_varchar_nokey
 b	NULL
 DROP TABLE t1, t2, t3;
 # End of test for bug#13552968
+#
+# Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive())
+# in join_read_const_table()
+#
+CREATE TABLE t1 (v INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES(1);
+CREATE TABLE t2 (v INTEGER) ENGINE=MyISAM;
+SELECT *
+FROM t1 LEFT JOIN t2
+ON t2.v IN(SELECT v FROM t1);
+v	v
+1	NULL
+DROP TABLE t1, t2;
+# End of test for bug#13591383.
+# End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result	2012-01-09 14:19:49 +0000
+++ b/mysql-test/r/subquery_mat_all.result	2012-01-16 14:00:28 +0000
@@ -1233,4 +1233,19 @@ col_varchar_nokey	col_varchar_nokey
 b	NULL
 DROP TABLE t1, t2, t3;
 # End of test for bug#13552968
+#
+# Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive())
+# in join_read_const_table()
+#
+CREATE TABLE t1 (v INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES(1);
+CREATE TABLE t2 (v INTEGER) ENGINE=MyISAM;
+SELECT *
+FROM t1 LEFT JOIN t2
+ON t2.v IN(SELECT v FROM t1);
+v	v
+1	NULL
+DROP TABLE t1, t2;
+# End of test for bug#13591383.
+# End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_mat_none.result'
--- a/mysql-test/r/subquery_mat_none.result	2012-01-09 14:19:49 +0000
+++ b/mysql-test/r/subquery_mat_none.result	2012-01-16 14:00:28 +0000
@@ -1230,4 +1230,19 @@ col_varchar_nokey	col_varchar_nokey
 b	NULL
 DROP TABLE t1, t2, t3;
 # End of test for bug#13552968
+#
+# Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive())
+# in join_read_const_table()
+#
+CREATE TABLE t1 (v INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES(1);
+CREATE TABLE t2 (v INTEGER) ENGINE=MyISAM;
+SELECT *
+FROM t1 LEFT JOIN t2
+ON t2.v IN(SELECT v FROM t1);
+v	v
+1	NULL
+DROP TABLE t1, t2;
+# End of test for bug#13591383.
+# End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc	2012-01-16 12:51:06 +0000
+++ b/sql/sql_executor.cc	2012-01-16 14:00:28 +0000
@@ -2652,10 +2652,10 @@ join_read_const_table(JOIN_TAB *tab, POS
     }
   }
 
-  // We cannot handle outer-joined tables with expensive join conditions here:
-  DBUG_ASSERT(!(*tab->on_expr_ref && (*tab->on_expr_ref)->is_expensive()));
   if (*tab->on_expr_ref && !table->null_row)
   {
+    // We cannot handle outer-joined tables with expensive join conditions here:
+    DBUG_ASSERT(!(*tab->on_expr_ref)->is_expensive());
     if ((table->null_row= test((*tab->on_expr_ref)->val_int() == 0)))
       mark_as_null_row(table);  
   }

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-01-16 12:51:06 +0000
+++ b/sql/sql_optimizer.cc	2012-01-16 14:00:28 +0000
@@ -3021,6 +3021,7 @@ const_table_extraction_done:
               has a real row or a null-extended row in the optimizer phase.
               We have no possibility to evaluate its join condition at
               execution time, when it is marked as a system table.
+              DontEvaluateMaterializedSubqueryTooEarly
         */
 	if (table->file->stats.records <= 1L &&                            // 1
             (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) && // 1

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3518 to 3519) Bug#13591383Roy Lyseng16 Jan