List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:May 2 2011 1:44pm
Subject:bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
(ole.john.aske:3487)
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:jonas@stripped

 3487 Ole John Aske	2011-05-02
      SPJ: Fixed a regression introduced with recent refactoring of ha_ndbcluster_push.cc:
      
      As we now allow dependencies between (grand-)parents to be added such that all
      the dependent parents become (artificial) dependent on each other, we may also
      implicit introduce dependencies on outer joined tables.
      
      This is incorrect as it is only inner joined tables which can 
      sequentially linked wo/ changing the sematics in the SQL statement.
     @ sql/abstract_query_plan.cc
        Removed a double ';;' terminating a line.

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.test
      sql/abstract_query_plan.cc
      sql/ha_ndbcluster_push.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2011-04-27 07:42:18 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2011-05-02 13:44:39 +0000
@@ -1434,6 +1434,92 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t3x.c,test.t2x.c	1	100.00	Child of 't3x' in pushed join@1
 Warnings:
 Note	1003	select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t2x`.`a` AS `a`,`test`.`t2x`.`b` AS `b`,`test`.`t2x`.`c` AS `c`,`test`.`t2x`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t3x`.`a` AS `a`,`test`.`t3x`.`b` AS `b`,`test`.`t3x`.`c` AS `c`,`test`.`t3x`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t2x` join `test`.`t1` `t3` join `test`.`t1` `t3x` join `test`.`t1` `t4` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t2x`.`b` = `test`.`t2`.`d`) and (`test`.`t2x`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t3`.`b` = `test`.`t1`.`b`) and (`test`.`t3x`.`b` = `test`.`t1`.`d`) and (`test`.`t3`.`a` = 
 `test`.`t1`.`c`) and (`test`.`t3x`.`a` = `test`.`t1`.`c`) and (`test`.`t4`.`b` = `test`.`t2x`.`c`) and (`test`.`t4`.`a` = `test`.`t3x`.`c`))
+explain extended
+select straight_join *
+from t1
+left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+join t1 as t3 on t3.a = t1.c and t3.b = t1.d
+left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	16	100.00	Parent of 3 pushed join@1
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.a,test.t1.b	1	100.00	Child of 't1' in pushed join@1
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t1.c,test.t1.d	1	100.00	Child of 't1' in pushed join@1
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t3.c,test.t2.c	1	100.00	
+Warnings:
+Note	1644	Can't push table 't4' as child of 't1', dependencies on outer joined grandparents not implemented
+Note	1003	select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` left join `test`.`t1` `t2` on(((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))) join `test`.`t1` `t3` left join `test`.`t1` `t4` on(((`test`.`t4`.`b` = `test`.`t2`.`c`) and (`test`.`t4`.`a` = `test`.`t3`.`c`))) where ((`test`.`t3`.`b` = `test`.`t1`.`d`) and (`test`.`t3`.`a` = `test`.`t1`.`c`))
+explain extended
+select straight_join *
+from t1
+join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+left join t1 as t3 on t3.a = t1.c and t3.b = t1.d
+left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16	100.00	Parent of 4 pushed join@1
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.a,test.t1.b	1	100.00	Child of 't1' in pushed join@1
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t1.c,test.t1.d	1	100.00	Child of 't2' in pushed join@1
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t3.c,test.t2.c	1	100.00	Child of 't3' in pushed join@1
+Warnings:
+Note	1003	select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` left join `test`.`t1` `t3` on(((`test`.`t3`.`b` = `test`.`t1`.`d`) and (`test`.`t3`.`a` = `test`.`t1`.`c`))) left join `test`.`t1` `t4` on(((`test`.`t4`.`b` = `test`.`t2`.`c`) and (`test`.`t4`.`a` = `test`.`t3`.`c`))) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))
+explain extended
+select straight_join *
+from t1
+left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+join t1 as t3 on t3.a = t1.a
+left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16	100.00	Parent of 3 pushed join@1
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.a,test.t1.b	1	100.00	Child of 't1' in pushed join@1
+1	SIMPLE	t3	ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	Child of 't1' in pushed join@1
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t3.c,test.t2.c	1	100.00	
+Warnings:
+Note	1644	Can't push table 't4' as child of 't1', dependencies on outer joined grandparents not implemented
+Note	1003	select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` left join `test`.`t1` `t2` on(((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))) join `test`.`t1` `t3` left join `test`.`t1` `t4` on(((`test`.`t4`.`b` = `test`.`t2`.`c`) and (`test`.`t4`.`a` = `test`.`t3`.`c`))) where (`test`.`t3`.`a` = `test`.`t1`.`a`)
+explain extended
+select straight_join *
+from t1
+join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+left join t1 as t3 on t3.a = t1.a
+left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16	100.00	Parent of 2 pushed join@1
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.a,test.t1.b	1	100.00	Child of 't1' in pushed join@1
+1	SIMPLE	t3	ref	PRIMARY	PRIMARY	4	test.t2.a	1	100.00	Parent of 2 pushed join@2
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t3.c,test.t2.c	1	100.00	Child of 't3' in pushed join@2
+Warnings:
+Note	1644	Can't push table 't3' as child of 't1', outer join with scan-ancestor 't1' not implemented
+Note	1644	Can't push table 't4' as child of 't1', column 't3.c' is outside scope of pushable join
+Note	1003	select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` left join `test`.`t1` `t3` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))) left join `test`.`t1` `t4` on(((`test`.`t4`.`b` = `test`.`t2`.`c`) and (`test`.`t4`.`a` = `test`.`t3`.`c`))) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))
+explain extended
+select straight_join *
+from t1
+left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+join t1 as t3 on t3.a = t1.a
+left join t1 as t4 on t4.a = t3.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16	100.00	Parent of 3 pushed join@1
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.a,test.t1.b	1	100.00	Child of 't1' in pushed join@1
+1	SIMPLE	t3	ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	Child of 't1' in pushed join@1
+1	SIMPLE	t4	ref	PRIMARY	PRIMARY	4	test.t3.c	1	100.00	
+Warnings:
+Note	1644	Can't push table 't4' as child of 't1', outer join with scan-ancestor 't3' not implemented
+Note	1003	select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` left join `test`.`t1` `t2` on(((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))) join `test`.`t1` `t3` left join `test`.`t1` `t4` on((`test`.`t4`.`a` = `test`.`t3`.`c`)) where (`test`.`t3`.`a` = `test`.`t1`.`a`)
+explain extended
+select straight_join *
+from t1
+join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+left join t1 as t3 on t3.a = t1.a
+left join t1 as t4 on t4.a = t3.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16	100.00	Parent of 2 pushed join@1
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.a,test.t1.b	1	100.00	Child of 't1' in pushed join@1
+1	SIMPLE	t3	ref	PRIMARY	PRIMARY	4	test.t2.a	1	100.00	
+1	SIMPLE	t4	ref	PRIMARY	PRIMARY	4	test.t3.c	1	100.00	
+Warnings:
+Note	1644	Can't push table 't3' as child of 't1', outer join with scan-ancestor 't1' not implemented
+Note	1644	Can't push table 't4' as child of 't1', column 't3.c' is outside scope of pushable join
+Note	1644	Can't push table 't4' as child of 't3', outer join with scan-ancestor 't3' not implemented
+Note	1003	select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` left join `test`.`t1` `t3` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))) left join `test`.`t1` `t4` on((`test`.`t4`.`a` = `test`.`t3`.`c`)) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))
 set ndb_join_pushdown=true;
 explain extended 
 select * from t1 x, t1 y, t1 z, t1 where 
@@ -4998,13 +5084,13 @@ LOCAL+REMOTE READS_SENT
 drop table spj_counts_at_startup;
 drop table spj_counts_at_end;
 scan_count
-2530
+2536
 pruned_scan_count
 8
 sorted_scan_count
 9
 pushed_queries_defined
-366
+373
 pushed_queries_dropped
 11
 pushed_queries_executed

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2011-04-27 07:42:18 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2011-05-02 13:44:39 +0000
@@ -659,6 +659,50 @@ from t1
  join t1 as t3x on t3x.a = t1.c and t3x.b = t1.d
  join t1 as t4  on t4.a = t3x.c and t4.b = t2x.c;
 
+## It should not be possible to force grandparent dependencies
+## via a previously outer joined table:
+explain extended
+select straight_join *
+from t1
+ left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+ join t1 as t3 on t3.a = t1.c and t3.b = t1.d
+ left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
+
+explain extended
+select straight_join *
+from t1
+ join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+ left join t1 as t3 on t3.a = t1.c and t3.b = t1.d
+ left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
+
+explain extended
+select straight_join *
+from t1
+ left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+ join t1 as t3 on t3.a = t1.a
+ left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
+
+explain extended
+select straight_join *
+from t1
+ join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+ left join t1 as t3 on t3.a = t1.a
+ left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
+
+explain extended
+select straight_join *
+from t1
+ left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+ join t1 as t3 on t3.a = t1.a
+ left join t1 as t4 on t4.a = t3.c;
+
+explain extended
+select straight_join *
+from t1
+ join t1 as t2 on t2.a = t1.a and t2.b = t1.b
+ left join t1 as t3 on t3.a = t1.a
+ left join t1 as t4 on t4.a = t3.c;
+
 
 # Test a combination of pushed table scan (x, y)
 #  & pushed EQ-bound (indexScan) (z, t1)

=== modified file 'sql/abstract_query_plan.cc'
--- a/sql/abstract_query_plan.cc	2011-01-04 14:50:54 +0000
+++ b/sql/abstract_query_plan.cc	2011-05-02 13:44:39 +0000
@@ -167,7 +167,7 @@ namespace AQP
     }
 
     const TABLE_LIST* const child_embedding= 
-      get_join_tab()->table->pos_in_table_list->embedding;;
+      get_join_tab()->table->pos_in_table_list->embedding;
 
     if (child_embedding == NULL)
     {

=== modified file 'sql/ha_ndbcluster_push.cc'
--- a/sql/ha_ndbcluster_push.cc	2011-04-27 09:06:08 +0000
+++ b/sql/ha_ndbcluster_push.cc	2011-05-02 13:44:39 +0000
@@ -644,6 +644,36 @@ ndb_pushed_builder_ctx::is_pushable_as_c
     }
   } // scan operation
 
+  /**
+   * In order to allow multiple parents refs to be made grandparent referrences,
+   * none of the grandparents should be outer joins.
+   */
+  ndb_table_access_map grandparents(depend_parents);
+  grandparents.clear_bit(root_no);
+  uint ancestor_no= root_no+1;
+  while (!grandparents.is_clear_all())
+  {
+    if (grandparents.contain(ancestor_no))
+    {
+      grandparents.clear_bit(ancestor_no);
+      if (grandparents.is_clear_all())
+        break;  // done
+
+      const AQP::Table_access* const ancestor= 
+        m_plan.get_table_access(ancestor_no);
+
+      if (ancestor->get_join_type(m_join_root) == AQP::JT_OUTER_JOIN)
+      {
+        EXPLAIN_NO_PUSH("Can't push table '%s' as child of '%s', "
+                        "dependencies on outer joined grandparents not implemented", 
+                         table->get_table()->alias,
+                         m_join_root->get_table()->alias);
+        DBUG_RETURN(false);
+      }
+    }
+    ancestor_no++;
+  }
+
   DBUG_ASSERT(m_join_scope.contain(common_parents));
   DBUG_ASSERT(m_join_scope.contain(extend_parents));
   DBUG_ASSERT(extend_parents.is_clear_all() ||
@@ -865,8 +895,7 @@ ndb_pushed_builder_ctx::optimize_query_p
      */
     if (!table.m_depend_parents.is_clear_all())
     {
-      ndb_table_access_map const &dependency
-        = table.m_depend_parents;
+      ndb_table_access_map const &dependency= table.m_depend_parents;
       DBUG_ASSERT(!dependency.contain(tab_no)); // Circular dependency!
 
       uint depends_on_parent= dependency.last_table(tab_no-1);
@@ -876,6 +905,8 @@ ndb_pushed_builder_ctx::optimize_query_p
       if (table.m_extend_parents.is_overlapping(dependency_mask))
       {
         table.m_extend_parents.subtract(dependency_mask);
+        DBUG_ASSERT(table.m_extend_parents.contain(depends_on_parent) ||
+                    m_plan.get_table_access(depends_on_parent)->get_join_type(m_join_root) == AQP::JT_INNER_JOIN);
         table.m_extend_parents.add(depends_on_parent);
       }
       if (table.m_common_parents.is_overlapping(dependency_mask))
@@ -886,7 +917,7 @@ ndb_pushed_builder_ctx::optimize_query_p
 
     /**
      * Select set to choose parent from, prefer a 'common'
-     * parent of available.
+     * parent if available.
      */
     uint parent_no;
     ndb_table_access_map const &parents=
@@ -1204,7 +1235,7 @@ ndb_pushed_builder_ctx::build_query()
       if (table->get_join_type(parent) == AQP::JT_INNER_JOIN)
       {
         options.setMatchType(NdbQueryOptions::MatchNonNull);
-      } 
+      }
     }
 
     const NdbQueryOperationDef* query_op= NULL;


Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20110502134439-7u9xtk23w6i5flfa.bundle
Thread
bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3487) Ole John Aske2 May