From: Roy Lyseng
Date: January 9 2012 2:27pm
Subject: bzr push into mysql-trunk branch (roy.lyseng:3509 to 3510) Bug#13552968
List-Archive: http://lists.mysql.com/commits/142344
X-Bug: 13552968
Message-Id: <20120109142739.BBE86207@tyr67.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 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).