#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 Aske | 2 May |
