[Commits] Rev 2871: Backport of (see below) + temporary measures to make SJ-Materialization work with join buffering. in file:///home/psergey/dev2/5.3-fix-subq/

Sergey Petrunya psergey at askmonty.org
Thu Jan 13 18:25:33 EET 2011


At file:///home/psergey/dev2/5.3-fix-subq/

------------------------------------------------------------
revno: 2871
revision-id: psergey at askmonty.org-20110113162531-yx0gmu3u3jblt7qr
parent: psergey at askmonty.org-20101225132316-3d9lf0j0izjjfvq6
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-fix-subq
timestamp: Thu 2011-01-13 19:25:31 +0300
message:
  Backport of (see below) + temporary measures to make SJ-Materialization work with join buffering.
  
  Date: Mon, 01 Nov 2010 15:15:25 -0000
  3272 Roy Lyseng        2010-11-01
  Bug#52068: Optimizer generates invalid semijoin materialization plan
  
  When MaterializeScan semijoin strategy was used and there were one
  or more outer dependent tables before the semijoin tables, the scan
  over the materialized table was not properly reset for each row of
  the prefix outer tables.
  
  Example: suppose we have a join order:
  
    ot1 SJ-Mat-Scan(it2 it3)  ot4
  
  Notice that this is called a MaterializeScan, even though there is an
  outer table ahead of the materialized tables. Usually a MaterializeScan
  has the outer tables after the materialized table, but this is
  a special (but legal) case with outer dependent tables both before and
  after the materialized table.
  
  For each qualifying row from ot1, a new scan over the materialized
  table must be set up. The code failed to do that, so all scans after
  the first one returned zero rows from the materialized table.
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2010-12-11 07:23:34 +0000
+++ b/mysql-test/r/subselect.result	2011-01-13 16:25:31 +0000
@@ -4975,3 +4975,39 @@
 3
 drop table t1,t2,t3;
 drop view v2;
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+drop table if exists ot1, ot2, it1, it2;
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	24	
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+2	SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2010-12-11 07:23:34 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2011-01-13 16:25:31 +0000
@@ -4978,6 +4978,41 @@
 3
 drop table t1,t2,t3;
 drop view v2;
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+drop table if exists ot1, ot2, it1, it2;
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (flat, BNL join)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';
 @@optimizer_switch like '%materialization=on%'

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2010-12-11 07:23:34 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2011-01-13 16:25:31 +0000
@@ -4975,4 +4975,39 @@
 3
 drop table t1,t2,t3;
 drop view v2;
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+drop table if exists ot1, ot2, it1, it2;
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2010-12-11 07:23:34 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2011-01-13 16:25:31 +0000
@@ -4975,4 +4975,39 @@
 3
 drop table t1,t2,t3;
 drop view v2;
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+drop table if exists ot1, ot2, it1, it2;
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+2	SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 set optimizer_switch=default;

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2010-12-06 08:25:44 +0000
+++ b/mysql-test/t/subselect.test	2011-01-13 16:25:31 +0000
@@ -4251,3 +4251,30 @@
 
 drop table t1,t2,t3;
 drop view v2;
+
+--echo #
+--echo # Bug#52068: Optimizer generates invalid semijoin materialization plan
+--echo #
+--disable_warnings
+drop table if exists ot1, ot2, it1, it2;
+--enable_warnings
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+
+let $query=
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+                        FROM it2,it3);
+
+eval $query;
+eval explain $query;
+
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+
+

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2010-12-13 17:01:32 +0000
+++ b/sql/sql_join_cache.cc	2011-01-13 16:25:31 +0000
@@ -169,9 +169,17 @@
   if (join_tab->first_sjm_sibling)
     return tab;
   uint i= tab-join->join_tab;
+  /*
+  Temporary measure before MWL#90 refactorings are there: if 'tab' is at upper
+  level (i.e. it's not inside an SJM nest), still include into the join buffer
+  the tables from within SJM nest.  We might need the subquery's select list
+  columns, because SJ-Materialization-Scan upacks data to those. 
+
   while (sj_is_materialize_strategy(join->best_positions[i].sj_strategy) &&
          i < join->tables)
     i+= join->best_positions[i].n_sj_tables;
+
+  */
   return join->join_tab+i < join_tab ? join->join_tab+i : NULL; 
 }
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-12-21 11:40:23 +0000
+++ b/sql/sql_select.cc	2011-01-13 16:25:31 +0000
@@ -13168,6 +13168,15 @@
       last_tab->read_record.read_record= rr_sequential_and_unpack;
     }
   }
+  else
+  {
+    if (sjm->is_sj_scan)
+    {
+      /* Reset the cursor for a new scan over the table */
+      if (sjm->table->file->ha_rnd_init(TRUE))
+        DBUG_RETURN(NESTED_LOOP_ERROR);
+    }
+  }
 
   if (sjm->is_sj_scan)
   {



More information about the commits mailing list