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#13607423 | Roy Lyseng | 20 Jan |
