List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:September 7 2010 12:29pm
Subject:bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
(ole.john.aske:3277)
View as plain text  
#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0-spj-scan-scan/ based on revid:ole.john.aske@stripped

 3277 Ole John Aske	2010-09-07
      Corrected a bug where we failed to detect that pushing another scan operation may 
      lead to a 'bushy scan' in the NdbQuery .

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.test
      sql/ha_ndbcluster.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-09-06 08:28:27 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-09-07 12:28:58 +0000
@@ -3480,6 +3480,27 @@ join t1 as x2 on x2.b = x1.a
 join t1 as x3 on x3.b = x1.b;
 count(*)
 2028
+explain
+select straight_join count(*) from t1 as x1
+join t1 as x2 on x2.b = x1.a
+join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	x1	ALL	NULL	NULL	NULL	NULL	13	Parent of 3 pushed join@1
+1	SIMPLE	x2	ref	ix1	ix1	5	test.x1.a	1	Child of pushed join@1; Using where
+1	SIMPLE	x3	eq_ref	PRIMARY	PRIMARY	4	test.x1.a	1	Child of pushed join@1
+1	SIMPLE	x4	ref	ix1	ix1	5	test.x3.a	1	Using where
+set ndb_join_pushdown=off;
+select straight_join count(*) from t1 as x1
+join t1 as x2 on x2.b = x1.a
+join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
+count(*)
+2028
+set ndb_join_pushdown=on;
+select straight_join count(*) from t1 as x1
+join t1 as x2 on x2.b = x1.a
+join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
+count(*)
+2028
 explain select straight_join count(*) from t1 as x1 
 left join t1 as x3 on x3.b = x1.a
 join t1 as x2 on x2.pk = x1.a;

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-09-06 08:28:27 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-09-07 12:28:58 +0000
@@ -2461,7 +2461,10 @@ left join t1 as x4 on x3.u=x4.pk
 left join t1 as x5 on x4.a=x5.b;
 set ndb_join_pushdown=on;
 
+#############
 # Test bushy-scans:
+# These should be serialized through an artificial dependency between the bushy scan childs.
+#
 explain select straight_join count(*) from t1 as x1 
   join t1 as x2 on x2.b = x1.a
   join t1 as x3 on x3.b = x1.b;
@@ -2474,6 +2477,31 @@ select straight_join count(*) from t1 as
   join t1 as x2 on x2.b = x1.a
   join t1 as x3 on x3.b = x1.b;
 
+##############
+# If we have a bushy lookup, with scandescendants depending on these lookups,
+# we dont serialize the scan by insertion of artificial dependencies.
+#
+# (Actually we could have serialized these also be forcing an artificial dependency
+#  between the referred lookup parent, and one of the other scan child. However, we
+#  don't believe this to be an efficient way of executing the pushed query, and would
+#  instead break these queries up into several smaller queries) 
+#
+explain
+select straight_join count(*) from t1 as x1
+  join t1 as x2 on x2.b = x1.a
+  join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
+
+set ndb_join_pushdown=off;
+select straight_join count(*) from t1 as x1
+  join t1 as x2 on x2.b = x1.a
+  join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
+set ndb_join_pushdown=on;
+select straight_join count(*) from t1 as x1
+  join t1 as x2 on x2.b = x1.a
+  join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
+
+
+#############
 # Test bushy lookups + 1scan, 
 # (Regression test for previous commit: http://lists.mysql.com/commits/117571)
 # Repeatable child rangescan with same parent should be allowed to be in 

=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc	2010-09-03 13:08:00 +0000
+++ b/sql/ha_ndbcluster.cc	2010-09-07 12:28:58 +0000
@@ -1005,12 +1005,33 @@ ndb_pushed_builder_ctx::field_ref_is_joi
      *        created in the SPJ block - Which adds extra (huge) communication overhead.
      *        As a longer term solution bushy scans should be nativily supported by SPJ.
      */
-     if (m_tables[parent_no].m_last_scan_descendant < MAX_TABLES)
-     {
-       parent_no= m_tables[parent_no].m_last_scan_descendant;
-       parent= m_plan.get_table_access(parent_no);
-       DBUG_PRINT("info", ("  Force artificial grandparent dependency through scan-child %s", parent->get_table()->alias));
-     }
+
+    if (m_tables[parent_no].m_last_scan_descendant < MAX_TABLES)
+    {
+      parent_no= m_tables[parent_no].m_last_scan_descendant;
+      parent= m_plan.get_table_access(parent_no);
+      DBUG_PRINT("info", ("  Force artificial grandparent dependency through scan-child %s", parent->get_table()->alias));
+    }
+    else
+    {
+      // Verify that there are no (bushy-) ancestors with scan descendants
+      uint ancestor_no= parent_no;
+      while (ancestor_no != MAX_TABLES)
+      {
+        if (m_tables[ancestor_no].m_last_scan_descendant < MAX_TABLES)
+        {
+          DBUG_PRINT("info", ("  Ancestor tab %d has scan descendants -> can't append", ancestor_no));
+          DBUG_RETURN(false);
+        }
+
+        const AQP::Table_access* const ancestor = m_plan.get_table_access(ancestor_no);
+        if (!is_lookup_operation(ancestor->get_access_type()))
+        {
+          break; // As adding this scanop was allowed, above ancestor can't be scan bushy
+        }
+        ancestor_no = m_tables[ancestor_no].m_parent;
+      }
+    }
   }
      // get_referred_table_access ??
   parent= m_plan.get_table_access(parent_no);


Attachment: [text/bzr-bundle] bzr/ole.john.aske@sun.com-20100907122858-1ir3off84vuy18y8.bundle
Thread
bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3277) Ole John Aske7 Sep