List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:January 20 2012 10:43am
Subject:bzr push into mysql-trunk branch (roy.lyseng:3527 to 3528) Bug#13607423
View as plain text  
 3528 Roy Lyseng	2012-01-20
      Bug#13607423: Assertion !(*tab->on_expr_ref->is_expensive()) ...
      
      This problem is similar to bug#13552968, except that this time
      the attempted const table determination was due to key dependencies.
      Apart from that, the same description applies.
      
      Without the assert added in bug#13552968, this bug would have led
      to a crash in subquery materialization, because the proper data
      structures had not been set up yet.
      
      mysql-test/include/subquery_mat.inc
        Added test case for bug#13607423.
      
      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#13607423.
      
      sql/sql_optimizer.cc
        Prevent outer joined table from being marked as const when it has
        an expensive join condition.

    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_optimizer.cc
 3527 Tor Didriksen	2012-01-18
      Bug#13580775 review comments

    modified:
      sql/filesort.cc
      sql/filesort_utils.h
      sql/table.h
      unittest/gunit/filesort_buffer-t.cc
=== modified file 'mysql-test/include/subquery_mat.inc'
--- a/mysql-test/include/subquery_mat.inc	2012-01-16 14:00:28 +0000
+++ b/mysql-test/include/subquery_mat.inc	2012-01-20 09:07:08 +0000
@@ -980,4 +980,46 @@ DROP TABLE t1, t2;
 
 --echo # End of test for bug#13591383.
 
+--echo #
+--echo # Bug#13607423: Assertion !(*tab->on_expr_ref->is_expensive())
+--echo # in join_read_const_table()
+--echo #
+
+CREATE TABLE t1 (
+  pk int NOT NULL,
+  col_int_nokey int DEFAULT NULL,
+  col_int_key int DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES (1,2,4), (2,150,62);
+
+CREATE TABLE t2 (
+  pk int NOT NULL,
+  col_int_key int DEFAULT NULL,
+  PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES (1,7);
+
+let $query=
+SELECT table1.pk, table2.pk
+FROM t2 AS table1 LEFT JOIN t2 AS table2
+     ON table2.pk = table1.pk AND
+        table2.col_int_key IN
+         (SELECT col_int_key
+          FROM t1 AS innr
+          WHERE innr.col_int_nokey > innr.col_int_nokey
+          GROUP BY col_int_key
+          HAVING COUNT(*) > 0
+         );
+
+eval explain $query;
+eval $query;
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#13607423.
+
 --echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_mat.result'
--- a/mysql-test/r/subquery_mat.result	2012-01-16 14:00:28 +0000
+++ b/mysql-test/r/subquery_mat.result	2012-01-20 09:07:08 +0000
@@ -1246,5 +1246,51 @@ v	v
 1	NULL
 DROP TABLE t1, t2;
 # End of test for bug#13591383.
+#
+# Bug#13607423: Assertion !(*tab->on_expr_ref->is_expensive())
+# in join_read_const_table()
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,2,4), (2,150,62);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,7);
+explain SELECT table1.pk, table2.pk
+FROM t2 AS table1 LEFT JOIN t2 AS table2
+ON table2.pk = table1.pk AND
+table2.col_int_key IN
+(SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.col_int_nokey > innr.col_int_nokey
+GROUP BY col_int_key
+HAVING COUNT(*) > 0
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	table2	eq_ref	PRIMARY	PRIMARY	4	const	1	Using where
+2	SUBQUERY	innr	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary; Using filesort
+SELECT table1.pk, table2.pk
+FROM t2 AS table1 LEFT JOIN t2 AS table2
+ON table2.pk = table1.pk AND
+table2.col_int_key IN
+(SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.col_int_nokey > innr.col_int_nokey
+GROUP BY col_int_key
+HAVING COUNT(*) > 0
+);
+pk	pk
+1	NULL
+DROP TABLE t1, t2;
+# End of test for bug#13607423.
 # 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-16 14:00:28 +0000
+++ b/mysql-test/r/subquery_mat_all.result	2012-01-20 09:07:08 +0000
@@ -1247,5 +1247,51 @@ v	v
 1	NULL
 DROP TABLE t1, t2;
 # End of test for bug#13591383.
+#
+# Bug#13607423: Assertion !(*tab->on_expr_ref->is_expensive())
+# in join_read_const_table()
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,2,4), (2,150,62);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,7);
+explain SELECT table1.pk, table2.pk
+FROM t2 AS table1 LEFT JOIN t2 AS table2
+ON table2.pk = table1.pk AND
+table2.col_int_key IN
+(SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.col_int_nokey > innr.col_int_nokey
+GROUP BY col_int_key
+HAVING COUNT(*) > 0
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	table2	eq_ref	PRIMARY	PRIMARY	4	const	1	Using where
+2	SUBQUERY	innr	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary; Using filesort
+SELECT table1.pk, table2.pk
+FROM t2 AS table1 LEFT JOIN t2 AS table2
+ON table2.pk = table1.pk AND
+table2.col_int_key IN
+(SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.col_int_nokey > innr.col_int_nokey
+GROUP BY col_int_key
+HAVING COUNT(*) > 0
+);
+pk	pk
+1	NULL
+DROP TABLE t1, t2;
+# End of test for bug#13607423.
 # 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-16 14:00:28 +0000
+++ b/mysql-test/r/subquery_mat_none.result	2012-01-20 09:07:08 +0000
@@ -1244,5 +1244,51 @@ v	v
 1	NULL
 DROP TABLE t1, t2;
 # End of test for bug#13591383.
+#
+# Bug#13607423: Assertion !(*tab->on_expr_ref->is_expensive())
+# in join_read_const_table()
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,2,4), (2,150,62);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,7);
+explain SELECT table1.pk, table2.pk
+FROM t2 AS table1 LEFT JOIN t2 AS table2
+ON table2.pk = table1.pk AND
+table2.col_int_key IN
+(SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.col_int_nokey > innr.col_int_nokey
+GROUP BY col_int_key
+HAVING COUNT(*) > 0
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	table2	system	PRIMARY	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	innr	index	NULL	col_int_key	5	NULL	2	Using where; Using filesort
+SELECT table1.pk, table2.pk
+FROM t2 AS table1 LEFT JOIN t2 AS table2
+ON table2.pk = table1.pk AND
+table2.col_int_key IN
+(SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.col_int_nokey > innr.col_int_nokey
+GROUP BY col_int_key
+HAVING COUNT(*) > 0
+);
+pk	pk
+1	NULL
+DROP TABLE t1, t2;
+# End of test for bug#13607423.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-01-18 13:53:00 +0000
+++ b/sql/sql_optimizer.cc	2012-01-20 09:07:08 +0000
@@ -3028,7 +3028,7 @@ const_table_extraction_done:
           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.
+           3. it does not have an expensive outer 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
@@ -3084,12 +3084,15 @@ const_table_extraction_done:
             Exclude tables that
              1. are full-text searched, or
              2. are part of nested outer join, or
-             3. are part of semi-join
+             3. are part of semi-join, or
+             4. have an expensive outer join condition.
+                DontEvaluateMaterializedSubqueryTooEarly
           */
 	  if (eq_part.is_prefix(table->key_info[key].key_parts) &&
               !table->fulltext_searched &&                           // 1
               !tl->in_outer_join_nest() &&                           // 2
-              !(tl->embedding && tl->embedding->sj_on_expr))         // 3
+              !(tl->embedding && tl->embedding->sj_on_expr) &&       // 3
+              !(*s->on_expr_ref && (*s->on_expr_ref)->is_expensive())) // 4
 	  {
             if (table->key_info[key].flags & HA_NOSAME)
             {

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3527 to 3528) Bug#13607423Roy Lyseng20 Jan