3510 Roy Lyseng 2012-01-09
Bug#13552968: Extra row with materialization on join + subquery in where +
The problem here occurs with an outer join between two "system" tables
(tables with exactly one row each), and with an "expensive" join
condition between the two tables. User-defined functions and materialized
subqueries are considered expensive in this context.
The fix for bug#52344 is related to this problem, as it moved subquery
materialization out of the optimizer phase and into the execution phase.
However, when marking the inner table of the outer join as "const",
it is not possible to later evaluate the join condition and subsequently
mark the fields from the inner tables as NULL. This is contrary to the
condition attached to the JOIN object: If there are only const tables
in the JOIN, do_select() will evaluate this condition and correctly
return an empty result if the condition evaluates to FALSE.
The cases for where the bug hit were quite interesting:
- With semijoin and materialization ON, the subquery is first marked
as semi-join candidate, but because the outer query is OUTER JOIN,
it is instead reverted to IN-TO-EXISTS. This is marked non-expensive
and thus does not pose a problem.
- With semijoin OFF, materialization is selected. The condition is then
marked as expensive and the problem occurs.
- With STRAIGHT JOIN applied to the outer query, materialization is
always selected, and the problem occurs.
The chosen solution is to stop marking the inner table of an outer join
that has an expensive join condition as a const table.
Fixing the executor to handle this particular case does not seem viable.
mysql-test/include/subquery_mat.inc
Added test case for bug#13552968.
Modified test case for bug#52344 so that problem is visible here as well.
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#13552968.
sql/sql_executor.cc
Ensure that join_read_const_table() is not called with an outer-joined
table with an expensive condition.
sql/sql_optimizer.cc
For the inner table of an outer join that has an expensive condition,
do not mark a table containing one row as const.
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
3509 Tor Didriksen 2012-01-05 [merge]
NULL merge trunk => opt-backporting
=== modified file 'mysql-test/include/subquery_mat.inc'
--- a/mysql-test/include/subquery_mat.inc 2011-11-25 10:39:07 +0000
+++ b/mysql-test/include/subquery_mat.inc 2012-01-09 14:19:49 +0000
@@ -677,12 +677,12 @@ INSERT INTO t2 VALUES (5);
CREATE TABLE t3 (k INTEGER);
EXPLAIN
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
EXPLAIN
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
DROP TABLE t1, t2, t3;
@@ -929,3 +929,35 @@ SELECT * FROM t3;
# prove that subquery materialization was used:
SHOW STATUS LIKE "CREATED_TMP_TABLES";
DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # Bug#13552968: Extra row with materialization on join + subquery in
+--echo #
+
+CREATE TABLE t1 (
+ col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES ('b');
+
+CREATE TABLE t2 (
+ col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES ('k');
+
+CREATE TABLE t3 (
+ col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+
+let $query=
+SELECT STRAIGHT_JOIN *
+FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey
+ FROM t3);
+
+eval explain $query;
+eval $query;
+
+DROP TABLE t1, t2, t3;
+
+--echo # End of test for bug#13552968
=== modified file 'mysql-test/r/subquery_mat.result'
--- a/mysql-test/r/subquery_mat.result 2011-11-25 10:39:07 +0000
+++ b/mysql-test/r/subquery_mat.result 2012-01-09 14:19:49 +0000
@@ -968,23 +968,23 @@ CREATE TABLE t2 (j INTEGER);
INSERT INTO t2 VALUES (5);
CREATE TABLE t3 (k INTEGER);
EXPLAIN
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-1 PRIMARY t2 system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
-i
-10
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+i j
+10 NULL
EXPLAIN
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-1 PRIMARY t2 system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
-i
-10
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+i j
+10 NULL
DROP TABLE t1, t2, t3;
# End BUG#52344
CREATE TABLE t1 (
@@ -1088,7 +1088,7 @@ explain SELECT a FROM (
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
) table1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
SELECT a FROM (
@@ -1204,4 +1204,32 @@ SHOW STATUS LIKE "CREATED_TMP_TABLES";
Variable_name Value
Created_tmp_tables 2
DROP TABLE t1,t2,t3;
+#
+# Bug#13552968: Extra row with materialization on join + subquery in
+#
+CREATE TABLE t1 (
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('b');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('k');
+CREATE TABLE t3 (
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+explain SELECT STRAIGHT_JOIN *
+FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey
+FROM t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
+2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found
+SELECT STRAIGHT_JOIN *
+FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey col_varchar_nokey
+b NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13552968
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result 2011-11-25 10:39:07 +0000
+++ b/mysql-test/r/subquery_mat_all.result 2012-01-09 14:19:49 +0000
@@ -970,23 +970,23 @@ CREATE TABLE t2 (j INTEGER);
INSERT INTO t2 VALUES (5);
CREATE TABLE t3 (k INTEGER);
EXPLAIN
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
-i
-10
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+i j
+10 NULL
EXPLAIN
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-1 PRIMARY t2 system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
-i
-10
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+i j
+10 NULL
DROP TABLE t1, t2, t3;
# End BUG#52344
CREATE TABLE t1 (
@@ -1090,7 +1090,7 @@ explain SELECT a FROM (
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
) table1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
SELECT a FROM (
@@ -1205,4 +1205,32 @@ SHOW STATUS LIKE "CREATED_TMP_TABLES";
Variable_name Value
Created_tmp_tables 0
DROP TABLE t1,t2,t3;
+#
+# Bug#13552968: Extra row with materialization on join + subquery in
+#
+CREATE TABLE t1 (
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('b');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('k');
+CREATE TABLE t3 (
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+explain SELECT STRAIGHT_JOIN *
+FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey
+FROM t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
+2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found
+SELECT STRAIGHT_JOIN *
+FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey col_varchar_nokey
+b NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13552968
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_mat_none.result'
--- a/mysql-test/r/subquery_mat_none.result 2011-11-25 10:39:07 +0000
+++ b/mysql-test/r/subquery_mat_none.result 2012-01-09 14:19:49 +0000
@@ -967,23 +967,23 @@ CREATE TABLE t2 (j INTEGER);
INSERT INTO t2 VALUES (5);
CREATE TABLE t3 (k INTEGER);
EXPLAIN
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
-i
-10
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+i j
+10 NULL
EXPLAIN
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
-SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
-i
-10
+SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+i j
+10 NULL
DROP TABLE t1, t2, t3;
# End BUG#52344
CREATE TABLE t1 (
@@ -1202,4 +1202,32 @@ SHOW STATUS LIKE "CREATED_TMP_TABLES";
Variable_name Value
Created_tmp_tables 0
DROP TABLE t1,t2,t3;
+#
+# Bug#13552968: Extra row with materialization on join + subquery in
+#
+CREATE TABLE t1 (
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('b');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('k');
+CREATE TABLE t3 (
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=MyISAM;
+explain SELECT STRAIGHT_JOIN *
+FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey
+FROM t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1
+1 PRIMARY t2 system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+SELECT STRAIGHT_JOIN *
+FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey col_varchar_nokey
+b NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13552968
set optimizer_switch=default;
=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc 2012-01-05 14:20:14 +0000
+++ b/sql/sql_executor.cc 2012-01-09 14:19:49 +0000
@@ -2637,15 +2637,10 @@ join_read_const_table(JOIN_TAB *tab, POS
}
}
}
- /* We will evaluate on-expressions here only if it is not considered
- expensive. This also prevents executing materialized subqueries
- in optimization phase. This is necessary since proper setup for
- such execution has not been done at this stage.
- (See comment in internal_remove_eq_conds() tagged
- DontEvaluateMaterializedSubqueryTooEarly).
- */
- if (*tab->on_expr_ref && !table->null_row &&
- !(*tab->on_expr_ref)->is_expensive())
+
+ // 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)
{
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-05 10:17:03 +0000
+++ b/sql/sql_optimizer.cc 2012-01-09 14:19:49 +0000
@@ -3012,9 +3012,20 @@ const_table_extraction_done:
// All dep. must be constants
if (s->dependent & ~(join->const_table_map))
continue;
- if (table->file->stats.records <= 1L &&
- (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) &&
- !tl->in_outer_join_nest())
+ /*
+ Mark a dependent table as constant if
+ 1. it has exactly zero or one rows (it is a system table), and
+ 2. it is not within a nested outer join, and
+ 3. it does not have an expensive join condition.
+ This is because we have to determine whether an outer-joined table
+ 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.
+ */
+ if (table->file->stats.records <= 1L && // 1
+ (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) && // 1
+ !tl->in_outer_join_nest() && // 2
+ !(*s->on_expr_ref && (*s->on_expr_ref)->is_expensive())) // 3
{ // system table
int tmp= 0;
s->type=JT_SYSTEM;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3509 to 3510) Bug#13552968 | Roy Lyseng | 9 Jan |
