3449 Roy Lyseng 2011-10-01
Bug#12714094: Assert in optimize_semijoin_nests()
Here we have a subquery with two outer-joined tables, where the outer
table of the outer join has a functional dependency to the outer query.
pull_out_semijoin_tables() attempts to pull out this table, however
we do not support a semi-join nest that has an outer-join dependency
to the outer query scope. The consequence was an assert in
optimize_semijoin_nests() because the const table detection code
erroneously detected a semi-joined inner-of-outer-join table as const.
The simple solution to the problem is to not perform pullout of
tables that will cause outer-join dependencies across join nests.
We now collect information about dependent tables inside
pull_out_semijoin_tables() and do not pull out tables that have
dependencies from other tables.
There was also a consequence for straight-joined tables that use the
same dependency information as outer-joined tables. There was special
code in make_join_statistics() that attempted to mark dependent tables
as const. This code is invalid when the table in question is also
a semi-joined table.
mysql-test/include/subquery_sj.inc
New test for bug#12714094.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
New test results for bug#12714094.
sql/sql_select.cc
In pull_out_semijoin_tables(), do not pull out tables that have
dependencies from other tables.
In make_join_statistics(), do not mark as const dependent tables
that are part of a semi-join nest.
In optimize_semijoin_nests(), tighten a DBUG_ASSERT.
After bug#43768 was fixed, we do not allow any const tables inside
semi-join nests.
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_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
sql/sql_select.cc
3448 Roy Lyseng 2011-09-30
Bug#12797534: Segfault in hp_movelink still exists
There was a silly mistake in the fix for bug#12603183: only the
read_first_record() function was saved, but not the read_record()
function. The result was more Valgrind warnings and/or segmentation
faults for slightly different test cases.
The proposed solution is simpler: A new scan is set up (by calling
init_read_record) every time a scan over the materialized table
is needed. Thus, saving the access parameters for the materialization
can be done locally, without involving "saved" fields in join_tab.
The tradeoff is slightly higher setup cost, because it would have
been sufficient to call init_read_record() once for every
materialization and only call ha_rnd_init() for each scan.
This tiny problem may be attacked by refactoring.
mysql-test/include/subquery_sj.inc
Added two test cases for bug#12797534.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
Updated with test results for bug#12797534.
sql/sql_select.cc
sub_select_sjm():
Save access parameters for materialization in a local struct
every time a scan over the materialized table is needed.
Restore the parameters immediately after scan is finished.
Call end_read_record() after the materialized table is scanned.
Some code cleanup suggested by reviewers.
sql/sql_select.h
Removed a comment about usage of field save_read_first_record.
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_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.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_nobnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
sql/sql_select.cc
sql/sql_select.h
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2011-09-30 10:22:38 +0000
+++ b/mysql-test/include/subquery_sj.inc 2011-10-01 19:26:04 +0000
@@ -4084,4 +4084,35 @@ DROP TABLE t1, t2, t3;
--echo # End of test for bug#12797534.
+--echo #
+--echo # Bug#12714094: Assert in optimize_semijoin_nests()
+--echo #
+
+CREATE TABLE it (
+ pk int NOT NULL,
+ col_varchar VARCHAR(10) DEFAULT NULL,
+ PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+
+INSERT INTO it VALUES (1, 'g');
+
+CREATE TABLE ot
+ SELECT alias1.pk AS field1
+ FROM it AS alias1
+ LEFT JOIN it AS alias2
+ ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+ SELECT alias1.pk
+ FROM it AS alias1
+ LEFT JOIN it AS alias2
+ ON alias1.col_varchar = alias2.col_varchar
+);
+
+DROP TABLE it, ot;
+
+--echo # End of test for bug#12714094
+
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2011-10-01 19:26:04 +0000
@@ -6767,5 +6767,32 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2011-10-01 19:26:04 +0000
@@ -6768,6 +6768,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nobnl.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nobnl.result 2011-10-01 19:26:04 +0000
@@ -6768,6 +6768,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2011-10-01 19:26:04 +0000
@@ -6769,6 +6769,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2011-10-01 19:26:04 +0000
@@ -6767,5 +6767,32 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2011-10-01 19:26:04 +0000
@@ -6768,6 +6768,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result 2011-10-01 19:26:04 +0000
@@ -6768,6 +6768,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2011-10-01 19:26:04 +0000
@@ -6769,6 +6769,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2011-10-01 19:26:04 +0000
@@ -6768,6 +6768,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2011-10-01 19:26:04 +0000
@@ -6769,6 +6769,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nobnl.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nobnl.result 2011-10-01 19:26:04 +0000
@@ -6769,6 +6769,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2011-10-01 19:26:04 +0000
@@ -6770,6 +6770,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2011-10-01 19:26:04 +0000
@@ -6768,5 +6768,32 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2011-10-01 19:26:04 +0000
@@ -6769,6 +6769,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nobnl.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nobnl.result 2011-10-01 19:26:04 +0000
@@ -6769,6 +6769,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2011-10-01 19:26:04 +0000
@@ -6770,6 +6770,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2011-10-01 19:26:04 +0000
@@ -6767,5 +6767,32 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2011-10-01 19:26:04 +0000
@@ -6768,6 +6768,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nobnl.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nobnl.result 2011-10-01 19:26:04 +0000
@@ -6768,6 +6768,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2011-10-01 19:26:04 +0000
@@ -6769,6 +6769,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-10-01 19:26:04 +0000
@@ -6844,5 +6844,32 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-10-01 19:26:04 +0000
@@ -6779,5 +6779,32 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2011-10-01 19:26:04 +0000
@@ -6780,6 +6780,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka_nobnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nobnl.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nobnl.result 2011-10-01 19:26:04 +0000
@@ -6780,6 +6780,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result 2011-09-30 10:22:38 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2011-10-01 19:26:04 +0000
@@ -6781,6 +6781,33 @@ AND NOT col_varchar_key IS NULL);
x
DROP TABLE t1, t2, t3;
# End of test for bug#12797534.
+#
+# Bug#12714094: Assert in optimize_semijoin_nests()
+#
+CREATE TABLE it (
+pk int NOT NULL,
+col_varchar VARCHAR(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO it VALUES (1, 'g');
+CREATE TABLE ot
+SELECT alias1.pk AS field1
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+;
+SELECT *
+FROM ot
+WHERE field1 IN (
+SELECT alias1.pk
+FROM it AS alias1
+LEFT JOIN it AS alias2
+ON alias1.col_varchar = alias2.col_varchar
+);
+field1
+1
+DROP TABLE it, ot;
+# End of test for bug#12714094
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-09-30 10:22:38 +0000
+++ b/sql/sql_select.cc 2011-10-01 19:26:04 +0000
@@ -4624,6 +4624,19 @@ static bool pull_out_semijoin_tables(JOI
List_iterator<TABLE_LIST> child_li(sj_nest->nested_join->join_list);
TABLE_LIST *tbl;
/*
+ Calculate set of tables within this semi-join nest that have
+ other dependent tables
+ */
+ table_map dep_tables= 0;
+ while ((tbl= child_li++))
+ {
+ TABLE *const table= tbl->table;
+ if (table &&
+ (table->reginfo.join_tab->dependent &
+ sj_nest->nested_join->used_tables))
+ dep_tables|= table->reginfo.join_tab->dependent;
+ }
+ /*
Find which tables we can pull out based on key dependency data.
Note that pulling one table out can allow us to pull out some
other tables too.
@@ -4635,7 +4648,9 @@ static bool pull_out_semijoin_tables(JOI
child_li.rewind();
while ((tbl= child_li++))
{
- if (tbl->table && !(pulled_tables & tbl->table->map))
+ if (tbl->table &&
+ !(pulled_tables & tbl->table->map) &&
+ !(dep_tables & tbl->table->map))
{
if (find_eq_ref_candidate(tbl->table,
sj_nest->nested_join->used_tables &
@@ -5227,7 +5242,7 @@ const_table_extraction_done:
for (JOIN_TAB **pos=stat_vector+const_count ; (s= *pos) ; pos++)
{
table=s->table;
-
+ TABLE_LIST *const tl= table->pos_in_table_list;
/*
If equi-join condition by a key is null rejecting and after a
substitution of a const table the key value happens to be null
@@ -5268,7 +5283,7 @@ const_table_extraction_done:
continue;
if (table->file->stats.records <= 1L &&
(table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) &&
- !table->pos_in_table_list->in_outer_join_nest())
+ !tl->in_outer_join_nest())
{ // system table
int tmp= 0;
s->type=JT_SYSTEM;
@@ -5313,11 +5328,13 @@ const_table_extraction_done:
Extract const tables with proper key dependencies.
Exclude tables that
1. are full-text searched, or
- 2. are part of nested outer join.
+ 2. are part of nested outer join, or
+ 3. are part of semi-join
*/
if (eq_part.is_prefix(table->key_info[key].key_parts) &&
!table->fulltext_searched && // 1
- !table->pos_in_table_list->in_outer_join_nest()) // 2
+ !tl->in_outer_join_nest() && // 2
+ !(tl->embedding && tl->embedding->sj_on_expr)) // 3
{
if (table->key_info[key].flags & HA_NOSAME)
{
@@ -5584,8 +5601,9 @@ static bool optimize_semijoin_nests(JOIN
if (join->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_SEMIJOIN) &&
join->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_MATERIALIZATION))
{
- /* semi-join nests with only constant tables are not valid */
- DBUG_ASSERT(sj_nest->sj_inner_tables & ~join->const_table_map);
+ /* A semi-join nest should not contain tables marked as const */
+ DBUG_ASSERT(!(sj_nest->sj_inner_tables & join->const_table_map));
+
Opt_trace_object trace_wrapper(trace);
Opt_trace_object
trace_sjmat(trace, "execution_plan_for_potential_materialization");
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3448 to 3449) Bug#12714094 | Roy Lyseng | 2 Oct |
