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#13591383 | Roy Lyseng | 16 Jan |
