From: Roy Lyseng Date: January 16 2012 2:01pm Subject: bzr push into mysql-trunk branch (roy.lyseng:3518 to 3519) Bug#13591383 List-Archive: http://lists.mysql.com/commits/142414 X-Bug: 13591383 Message-Id: <20120116140102.7EF5E207@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ((GP1.col_varchar_nokey) = t1.col_varchar_nokey) and ((GP1.col_varchar_nokey) = t1.col_varchar_nokey) ), the two 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).