List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:October 1 2011 7:27pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3448 to 3449) Bug#12714094
View as plain text  
 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#12714094Roy Lyseng2 Oct