From: Ole John Aske Date: March 29 2012 10:39am Subject: bzr push into mysql-trunk-cluster branch (ole.john.aske:3459 to 3460) List-Archive: http://lists.mysql.com/commits/143353 Message-Id: <20120329103900.E0492244@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3460 Ole John Aske 2012-03-29 More WL5940 addaption of ORDER/GROUP BY handling in order to address comments from reviewers: In the case of a 'simple'(*) order by expression which can not be executed by reading from an ordered index, there is a conflict of interests between the optimizer and the pushed join integration: For any 'simple' order by expression the optimizer will establish the correct ordering of the first non-const table before starting the join. If there is no suitable ordered index, the table itself will be filesorted into an intermediate record store. As the table is now stored outside NDB, such a presorted table can not pe part of a pushed join. Before this fix this conflict was resolved in favour of maximizing usage of pushed joins: The Query Execution Plan (QEP) from the optimizer was changes such that the presort was dropped. Instead the entire resultset was calculated (with pushed joins) and then written to a temporary file which was later filesorted. The reviewer commented that changing the QEP (above) as part of the pushed join integration was 'questionable, at best'. This fix changes this by extending our AQP (Abstract Query Plan) with functionality to detect when the optimizer has decided to do 'filesort_before_join'. If that condition is detected, this presorted table is excluded as non-pushable. To sum up: - We don't any longer modify QEP as part of pushed join integration. - This comes at the cost of some order/group by queries being less pushable. (*) A 'simple' ordering expression contain only column references to the first non-const table in the join_tab's. modified: mysql-test/suite/ndb/r/ndb_join_pushdown_default.result mysql-test/suite/ndb/r/ndb_join_pushdown_nobnl.result mysql-test/suite/ndb/r/ndb_join_pushdown_none.result mysql-test/suite/ndb/t/ndb_join_pushdown.inc sql/abstract_query_plan.cc sql/abstract_query_plan.h sql/ha_ndbcluster.cc sql/ha_ndbcluster_push.cc sql/handler.cc sql/handler.h sql/sql_executor.cc sql/sql_executor.h sql/sql_optimizer.cc 3459 Ole John Aske 2012-03-23 SPJ: Addapt 'Abstract query plan' interface and SPJ-optimizer integration to latest review comments. These changes has been made possible by rewrites introduced by WL5558 and adding native sorted-scan-scan support to the SPJ interface: - AQP::Join_plan* argument to several methods can now be made 'const' as AQP will not any longer modify the query plan. - Join_plan::group_by_filesort_is_skippable() & ::order_by_filesort_is_skippable() has become obsolete. - Integrate optimizer code which sets up the pushed join version of *read_first_record accessor functions into pick_table_access_method(). - Change optimizer such that pick_table_access_method() is called at the end of the optimizer phase when the complete query plan is known (Including pushed join execution) modified: mysql-test/suite/ndb/t/ndb_join_pushdown.inc sql/abstract_query_plan.cc sql/abstract_query_plan.h sql/ha_ndbcluster.cc sql/handler.cc sql/handler.h sql/sql_executor.cc sql/sql_executor.h sql/sql_optimizer.cc sql/sql_select.cc === modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown_default.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown_default.result 2012-03-23 12:15:24 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown_default.result 2012-03-29 10:38:31 +0000 @@ -1707,10 +1707,12 @@ join t1 as t3 on t3.a = t2.a and t3.b = order by t1.c,t1.d, t1.a, t1.b; 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; Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL Warnings: +Note 9999 Table 't1' is not pushable, need filesort before joining child tables +Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const' Note 1003 /* select#1 */ select `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` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`c`,`test`.`t1`.`d`,`test`.`t1`.`a`,`test`.`t1`.`b` select * from t1 @@ -1867,10 +1869,12 @@ join t1 as t2 on t2.a = t1.b and t2.b = join t1 as t3 on t3.a = t2.a and t3.b = t2.b order by t1.b,t1.a; 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; Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL Warnings: +Note 9999 Table 't1' is not pushable, need filesort before joining child tables +Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const' Note 1003 /* select#1 */ select `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` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b`,`test`.`t1`.`a` select * from t1 @@ -1910,10 +1914,12 @@ join t1 as t2 on t2.a = t1.b and t2.b = join t1 as t3 on t3.a = t2.a and t3.b = t2.b order by t1.b; 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; Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL Warnings: +Note 9999 Table 't1' is not pushable, need filesort before joining child tables +Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const' Note 1003 /* select#1 */ select `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` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b` explain extended select t1.a, t1.b, count(*) @@ -5513,11 +5519,11 @@ and spj_counts_at_end.counter_name not i 'SCAN_BATCHES_RETURNED'); counter_name spj_counts_at_end.val - spj_counts_at_startup.val CONST_PRUNED_RANGE_SCANS_RECEIVED 8 -LOCAL_TABLE_SCANS_SENT 256 +LOCAL_TABLE_SCANS_SENT 252 PRUNED_RANGE_SCANS_RECEIVED 27 RANGE_SCANS_RECEIVED 728 READS_RECEIVED 54 -TABLE_SCANS_RECEIVED 256 +TABLE_SCANS_RECEIVED 252 drop table spj_counts_at_startup; drop table spj_counts_at_end; select new.variable_name, new.variable_value - old.variable_value @@ -5527,9 +5533,9 @@ where new.variable_name = old.variable_n order by new.variable_name; variable_name new.variable_value - old.variable_value NDB_PRUNED_SCAN_COUNT 8 -NDB_PUSHED_QUERIES_DEFINED 403 +NDB_PUSHED_QUERIES_DEFINED 398 NDB_PUSHED_QUERIES_DROPPED 7 -NDB_PUSHED_QUERIES_EXECUTED 547 +NDB_PUSHED_QUERIES_EXECUTED 545 NDB_SORTED_SCAN_COUNT 11 drop table server_counts_at_startup; set ndb_join_pushdown = @save_ndb_join_pushdown; === modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown_nobnl.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown_nobnl.result 2012-03-23 12:15:24 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown_nobnl.result 2012-03-29 10:38:31 +0000 @@ -1704,10 +1704,12 @@ join t1 as t3 on t3.a = t2.a and t3.b = order by t1.c,t1.d, t1.a, t1.b; 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; Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL Warnings: +Note 9999 Table 't1' is not pushable, need filesort before joining child tables +Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const' Note 1003 /* select#1 */ select `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` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`c`,`test`.`t1`.`d`,`test`.`t1`.`a`,`test`.`t1`.`b` select * from t1 @@ -1864,10 +1866,12 @@ join t1 as t2 on t2.a = t1.b and t2.b = join t1 as t3 on t3.a = t2.a and t3.b = t2.b order by t1.b,t1.a; 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; Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL Warnings: +Note 9999 Table 't1' is not pushable, need filesort before joining child tables +Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const' Note 1003 /* select#1 */ select `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` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b`,`test`.`t1`.`a` select * from t1 @@ -1907,10 +1911,12 @@ join t1 as t2 on t2.a = t1.b and t2.b = join t1 as t3 on t3.a = t2.a and t3.b = t2.b order by t1.b; 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; Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL Warnings: +Note 9999 Table 't1' is not pushable, need filesort before joining child tables +Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const' Note 1003 /* select#1 */ select `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` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b` explain extended select t1.a, t1.b, count(*) @@ -5508,11 +5514,11 @@ and spj_counts_at_end.counter_name not i 'SCAN_BATCHES_RETURNED'); counter_name spj_counts_at_end.val - spj_counts_at_startup.val CONST_PRUNED_RANGE_SCANS_RECEIVED 8 -LOCAL_TABLE_SCANS_SENT 298 +LOCAL_TABLE_SCANS_SENT 294 PRUNED_RANGE_SCANS_RECEIVED 27 RANGE_SCANS_RECEIVED 734 READS_RECEIVED 54 -TABLE_SCANS_RECEIVED 298 +TABLE_SCANS_RECEIVED 294 drop table spj_counts_at_startup; drop table spj_counts_at_end; select new.variable_name, new.variable_value - old.variable_value @@ -5522,9 +5528,9 @@ where new.variable_name = old.variable_n order by new.variable_name; variable_name new.variable_value - old.variable_value NDB_PRUNED_SCAN_COUNT 8 -NDB_PUSHED_QUERIES_DEFINED 411 +NDB_PUSHED_QUERIES_DEFINED 406 NDB_PUSHED_QUERIES_DROPPED 7 -NDB_PUSHED_QUERIES_EXECUTED 571 +NDB_PUSHED_QUERIES_EXECUTED 569 NDB_SORTED_SCAN_COUNT 11 drop table server_counts_at_startup; set ndb_join_pushdown = @save_ndb_join_pushdown; === modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown_none.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown_none.result 2012-03-23 12:15:24 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown_none.result 2012-03-29 10:38:31 +0000 @@ -1707,10 +1707,12 @@ join t1 as t3 on t3.a = t2.a and t3.b = order by t1.c,t1.d, t1.a, t1.b; 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; Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL Warnings: +Note 9999 Table 't1' is not pushable, need filesort before joining child tables +Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const' Note 1003 /* select#1 */ select `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` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`c`,`test`.`t1`.`d`,`test`.`t1`.`a`,`test`.`t1`.`b` select * from t1 @@ -1867,10 +1869,12 @@ join t1 as t2 on t2.a = t1.b and t2.b = join t1 as t3 on t3.a = t2.a and t3.b = t2.b order by t1.b,t1.a; 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; Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL Warnings: +Note 9999 Table 't1' is not pushable, need filesort before joining child tables +Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const' Note 1003 /* select#1 */ select `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` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b`,`test`.`t1`.`a` select * from t1 @@ -1910,10 +1914,12 @@ join t1 as t2 on t2.a = t1.b and t2.b = join t1 as t3 on t3.a = t2.a and t3.b = t2.b order by t1.b; 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; Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL Warnings: +Note 9999 Table 't1' is not pushable, need filesort before joining child tables +Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const' Note 1003 /* select#1 */ select `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` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b` explain extended select t1.a, t1.b, count(*) @@ -5513,11 +5519,11 @@ and spj_counts_at_end.counter_name not i 'SCAN_BATCHES_RETURNED'); counter_name spj_counts_at_end.val - spj_counts_at_startup.val CONST_PRUNED_RANGE_SCANS_RECEIVED 8 -LOCAL_TABLE_SCANS_SENT 254 +LOCAL_TABLE_SCANS_SENT 250 PRUNED_RANGE_SCANS_RECEIVED 27 RANGE_SCANS_RECEIVED 735 READS_RECEIVED 58 -TABLE_SCANS_RECEIVED 254 +TABLE_SCANS_RECEIVED 250 drop table spj_counts_at_startup; drop table spj_counts_at_end; select new.variable_name, new.variable_value - old.variable_value @@ -5527,9 +5533,9 @@ where new.variable_name = old.variable_n order by new.variable_name; variable_name new.variable_value - old.variable_value NDB_PRUNED_SCAN_COUNT 11 -NDB_PUSHED_QUERIES_DEFINED 403 +NDB_PUSHED_QUERIES_DEFINED 398 NDB_PUSHED_QUERIES_DROPPED 11 -NDB_PUSHED_QUERIES_EXECUTED 555 +NDB_PUSHED_QUERIES_EXECUTED 553 NDB_SORTED_SCAN_COUNT 11 drop table server_counts_at_startup; set ndb_join_pushdown = @save_ndb_join_pushdown; === modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.inc' --- a/mysql-test/suite/ndb/t/ndb_join_pushdown.inc 2012-03-23 13:33:58 +0000 +++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.inc 2012-03-29 10:38:31 +0000 @@ -787,16 +787,24 @@ explain extended y.a=x.d and y.b=x.b; # Test ORDER BY expressions -# Filesort on pushed joins are not possible as the +# If it is a 'simple' order, i.e all order by's are plain column refs +# to the first non-const table, the optimizer will (by heuristic) +# make the first table 'ordered' beforing joining in the siblings. +# This may involve presorting of the first table into intermediate storage. +# +# This will make this (parent-) table non-pushable as # read of rows to be filesorted will also prefetch rows from pushed child # operands. These are not cached by the filesort buffer mechanisnm and are # effectively lost. -# With pushed joins we either has to: +# +# Non simple ordering will always writte entire resultset to temp. +# table and filesort that -> No extra push restrictions on these. +# +# Integrating pushed joins we either has to: # 1) find a suitable ordered index which we can create an ordered indexscan on # (-> joinType() -> JT_NEXT, or type: 'index' w/ explain) # or: -# 2) Use a temporary result file for the result, which is then filesort'ed -# (-> 'Using temporary; Using filesort') +# 2) Reject pushing of the this parent table. # # Comment1: As 'order by' correctness is part of what we want to test, # '--sorted_result' is *not* specified. Instead we aim at @@ -806,7 +814,7 @@ explain extended # to get a deterministic sorting order wo/ '--sorted_result' # -## pushed join w/ 'simple order' on non_PK - Need temp table + filesort +## Non-pushed join w/ 'simple order' on non_PK - Presorts parent table. explain extended select * from t1 @@ -882,7 +890,7 @@ join t1 as t2 on t2.a = t1.b and t2.b = join t1 as t3 on t3.a = t2.a and t3.b = t2.b order by t1.a desc,t1.b desc; -## PK column in incorrect order, -> filesort to tempfile +## Simple-PK column in incorrect order, -> Presort parent table, (no-push) explain extended select * from t1 @@ -906,7 +914,7 @@ join t1 as t2 on t2.a = t1.b and t2.b = join t1 as t3 on t3.a = t2.a and t3.b = t2.b order by t1.a; -# PK columns not including first part -> filesort to tempfile +# PK columns not including first part -> Presort parent table, (no-push) explain extended select * from t1 @@ -3498,7 +3506,10 @@ connection ddl; drop table t1; ### -# Test that sorted scan with sub scan is *not* pushed. +# Sorted scan with sub scan used to not being pushable. +# However since v7.2.6(?) we support this by enforcing +# batchRowSize=1 for the parent table in the scan-scan. +# (At the cost of more roundrtrips) ### connection ddl; create table t1 (pk int primary key, a int, b int) engine=ndb; @@ -3517,7 +3528,7 @@ insert into t1 values (8,10,20); insert into t1 values (9,10,10); -# Results would be sorted wrongly if pushed. +# This sorted scan-scan is pushed since V7.2.6 explain extended select x1.pk,x1.a,x1.b from t1 as x1 join t1 as x2 on x1.a=x2.b join t1 as x3 on x2.a=x3.b @@ -3591,6 +3602,8 @@ drop table t; # bug#57601 'Optimizer is overly eager to request ordered access.' # When we turned of 'sorted' for 'descending', we broke QUICK_SELECT_DESC # which required result to be read as an ordered index access +# Note: Before V7.2.6, and as a result of WL5558, such 'turn of sorting' +# is not expected to happen any more. ####### connection ddl; create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb; === modified file 'sql/abstract_query_plan.cc' --- a/sql/abstract_query_plan.cc 2012-03-23 13:33:58 +0000 +++ b/sql/abstract_query_plan.cc 2012-03-29 10:38:31 +0000 @@ -482,5 +482,46 @@ namespace AQP return get_join_tab()->next_select == sub_select_cache; } + /** + Check if this table will be presorted to an intermediate record storage + before it is joined with its siblings. + */ + bool Table_access::filesort_before_join() const + { + if (m_access_type == AT_PRIMARY_KEY || + m_access_type == AT_UNIQUE_KEY) + { + return false; + } + + const JOIN_TAB* const join_tab= get_join_tab(); + JOIN* const join= join_tab->join; + + /** + Table will be presorted before joining with child tables, if: + 1) This is the first non-const table + 2) There are more tables to be joined + 3) It is not already decide to write entire join result to temp. + 4a) The GROUP BY is 'simple' and does not match an orderd index + 4b) The ORDER BY is 'simple' and does not match an orderd index + + A 'simple' order/group by contain only column references to + the first non-const table + */ + if (join_tab == join->join_tab+join->const_tables && // First non-const table + join->const_tables < join->tables) // There are more tables + { + if (join->need_tmp) + return false; + else if (join->group_list && join->simple_group) + return (join->ordered_index_usage!=JOIN::ordered_index_group_by); + else if (join->order && join->simple_order) + return (join->ordered_index_usage!=JOIN::ordered_index_order_by); + else + return false; + } + return false; + } + }; // namespace AQP === modified file 'sql/abstract_query_plan.h' --- a/sql/abstract_query_plan.h 2012-03-23 13:33:58 +0000 +++ b/sql/abstract_query_plan.h 2012-03-29 10:38:31 +0000 @@ -199,6 +199,8 @@ namespace AQP bool uses_join_cache() const; + bool filesort_before_join() const; + private: /** Backref. to the Join_plan which this Table_access is part of */ === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2012-03-23 13:33:58 +0000 +++ b/sql/ha_ndbcluster.cc 2012-03-29 10:38:31 +0000 @@ -813,8 +813,7 @@ SHOW_VAR ndb_status_index_stat_variables #ifndef NDB_WITHOUT_JOIN_PUSHDOWN static int ndbcluster_make_pushed_join(handlerton *, THD*, - const AQP::Join_plan*, - uint*); + const AQP::Join_plan*); #endif /* @@ -14829,12 +14828,10 @@ ha_ndbcluster::read_multi_range_fetch_ne static int ndbcluster_make_pushed_join(handlerton *hton, THD* thd, - const AQP::Join_plan* plan, - uint* pushed) + const AQP::Join_plan* plan) { DBUG_ENTER("ndbcluster_make_pushed_join"); (void)ha_ndb_ext; // prevents compiler warning. - *pushed= 0; if (THDVAR(thd, join_pushdown)) { @@ -14871,7 +14868,6 @@ int ndbcluster_make_pushed_join(handlert handler->print_error(error, MYF(0)); DBUG_RETURN(error); } - *pushed= handler->number_of_pushed_joins(); } } } === modified file 'sql/ha_ndbcluster_push.cc' --- a/sql/ha_ndbcluster_push.cc 2012-03-23 12:15:24 +0000 +++ b/sql/ha_ndbcluster_push.cc 2012-03-29 10:38:31 +0000 @@ -523,6 +523,14 @@ ndb_pushed_builder_ctx::is_pushable_with DBUG_RETURN(false); } + if (root->filesort_before_join()) + { + EXPLAIN_NO_PUSH("Table '%s' is not pushable, " + "need filesort before joining child tables", + root->get_table()->alias); + DBUG_RETURN(false); + } + /** * Past this point we know at least root to be pushable as parent * operation. Search remaining tables appendable if '::is_pushable_as_child()' === modified file 'sql/handler.cc' --- a/sql/handler.cc 2012-03-23 13:33:58 +0000 +++ b/sql/handler.cc 2012-03-29 10:38:31 +0000 @@ -4348,7 +4348,6 @@ int ha_table_exists_in_engine(THD* thd, struct st_make_pushed_join_args { const AQP::Join_plan* plan; // Query plan provided by optimizer - uint pushed; // #operations which was pushed. int err; // Error code to return. }; @@ -4360,26 +4359,23 @@ static my_bool make_pushed_join_handlert if (hton && hton->make_pushed_join) { - uint pushed= 0; - const int error= hton->make_pushed_join(hton, thd, vargs->plan, &pushed); + const int error= hton->make_pushed_join(hton, thd, vargs->plan); if (unlikely(error)) { vargs->err = error; return TRUE; } - vargs->pushed+= pushed; } return FALSE; } -int ha_make_pushed_joins(THD *thd, const AQP::Join_plan* plan, uint* pushed) +int ha_make_pushed_joins(THD *thd, const AQP::Join_plan* plan) { DBUG_ENTER("ha_make_pushed_joins"); - st_make_pushed_join_args args= {plan, 0, 0}; + st_make_pushed_join_args args= {plan, 0}; plugin_foreach(thd, make_pushed_join_handlerton, MYSQL_STORAGE_ENGINE_PLUGIN, &args); - *pushed = args.pushed; - DBUG_PRINT("exit", ("pushed cnt: %d, error: %d", args.pushed, args.err)); + DBUG_PRINT("exit", ("error: %d", args.err)); DBUG_RETURN(args.err); } #endif === modified file 'sql/handler.h' --- a/sql/handler.h 2012-03-23 13:33:58 +0000 +++ b/sql/handler.h 2012-03-29 10:38:31 +0000 @@ -863,8 +863,7 @@ struct handlerton #ifndef MCP_WL4784 int (*make_pushed_join)(handlerton *hton, THD* thd, - const AQP::Join_plan* plan, - uint* pushed); + const AQP::Join_plan* plan); #endif #ifndef MCP_GLOBAL_SCHEMA_LOCK int (*global_schema_func)(THD* thd, bool lock, void* args); @@ -2790,7 +2789,7 @@ int ha_release_savepoint(THD *thd, SAVEP #ifndef MCP_WL4784 /* Build pushed joins in handlers implementing this feature */ -int ha_make_pushed_joins(THD *thd, const AQP::Join_plan* plan, uint* pushed); +int ha_make_pushed_joins(THD *thd, const AQP::Join_plan* plan); #endif /* these are called by storage engines */ === modified file 'sql/sql_executor.cc' --- a/sql/sql_executor.cc 2012-03-23 13:33:58 +0000 +++ b/sql/sql_executor.cc 2012-03-29 10:38:31 +0000 @@ -3386,10 +3386,11 @@ join_read_next_same_or_null(READ_RECORD @param tab Table reference to put access method */ -#ifndef MCP_WL4784 void -pick_table_access_method(JOIN_TAB *tab, int *active_pushed_joins) +pick_table_access_method(JOIN_TAB *tab) { + +#ifndef MCP_WL4784 uint pushed_joins= tab->table->file->number_of_pushed_joins(); /** @@ -3397,17 +3398,14 @@ pick_table_access_method(JOIN_TAB *tab, */ if (pushed_joins > 0) { - active_pushed_joins--; - if (tab->table->file->root_of_pushed_join() == tab->table) + if (tab->table->file->root_of_pushed_join() != tab->table) { - *active_pushed_joins += pushed_joins; - } - else - { - // Is child of a pushed join operation: - // Replace 'read_key' access with its linked counterpart - // ... Which is effectively a NOOP as the row is read - // as part of the linked operation + /* + Is child of a pushed join operation: + Replace access functions with its linked counterpart. + ... Which is effectively a NOOP as the row is already fetched + together with the root of the linked operation. + */ DBUG_ASSERT(tab->type != JT_REF_OR_NULL); tab->read_first_record= join_read_linked_first; tab->read_record.read_record= join_read_linked_next; @@ -3416,15 +3414,15 @@ pick_table_access_method(JOIN_TAB *tab, } } - // Already set to some non-default value in sql_select.cc - // TODO: Move these settings into pick_table_access_method() also + /* + Already set to some non-default value in sql_select.cc + TODO: Move these settings into pick_table_access_method() also + */ else if (tab->read_first_record != NULL) return; -#else -void -pick_table_access_method(JOIN_TAB *tab) -{ + // Fall through to set default access functions: + #endif // MCP_WL4784 switch (tab->type) === modified file 'sql/sql_executor.h' --- a/sql/sql_executor.h 2012-03-23 13:33:58 +0000 +++ b/sql/sql_executor.h 2012-03-29 10:38:31 +0000 @@ -125,12 +125,7 @@ int report_error(TABLE *table, int error int safe_index_read(JOIN_TAB *tab); SORT_FIELD * make_unireg_sortorder(ORDER *order, uint *length, SORT_FIELD *sortorder); - -#ifndef MCP_WL4784 -void pick_table_access_method(JOIN_TAB *tab, int *active_pushed_joins); -#else void pick_table_access_method(JOIN_TAB *tab); -#endif int join_read_const_table(JOIN_TAB *tab, POSITION *pos); void join_read_key_unlock_row(st_join_table *tab); === modified file 'sql/sql_optimizer.cc' --- a/sql/sql_optimizer.cc 2012-03-23 13:33:58 +0000 +++ b/sql/sql_optimizer.cc 2012-03-29 10:38:31 +0000 @@ -107,11 +107,6 @@ only_eq_ref_tables(JOIN *join, ORDER *or table_map *cached_eq_ref_tables, table_map *eq_ref_tables); -#ifndef MCP_WL4784 -static int make_pushed_join(THD *thd, JOIN *join); -#endif - - /** global select optimisation. @@ -948,18 +943,29 @@ JOIN::optimize() } #ifndef MCP_WL4784 - if (make_pushed_join(thd, this)) - DBUG_RETURN(1); + /** + * Push joins to handler(s) whenever possible. + * The handlers will inspect the QEP through the + * AQP (Abstract Query Plan), and extract from it + * whatewer it might implement of pushed execution. + * It is the responsibility if the handler to store any + * information it need for later execution of pushed queries. + * + * Currently this is only supported by NDB. + */ + { + const AQP::Join_plan plan(this); + if (ha_make_pushed_joins(thd, &plan)) + DBUG_RETURN(1); + } + /** + * Set up access functions for the tables as + * required by the selected access type. + */ + for (uint i= const_tables; i < tables; i++) { - int active_pushed_joins= 0; - for (uint i= this->const_tables; i < this->tables; i++) - { - JOIN_TAB *const tab= this->join_tab+i; - pick_table_access_method (tab, &active_pushed_joins); - } - /* Ensure all (if any) pushed joins were processed. */ - DBUG_ASSERT(!active_pushed_joins); + pick_table_access_method (&join_tab[i]); } #endif @@ -994,56 +1000,6 @@ setup_subq_exit: DBUG_RETURN(0); } -#ifndef MCP_WL4784 -/** - Push join to handler, if possible. Currently this is only supported by - NDB. - - @note If it happens so that handler can't do ordering this function - will force tmp table creation and resolving ORDER/GROUP BY by filesort. - - @returns - 0 ok, join isn't pushed or pushed without errors. - error handler's error otherwise -*/ -static int -make_pushed_join(THD *thd, JOIN *join) -{ - // Let handler extract whatever it might implement of pushed joins - AQP::Join_plan plan(join); - uint pushed; - - const int error= ha_make_pushed_joins(thd, &plan, &pushed); - if (unlikely(error)) - return error; - if (pushed==0) - return 0; // Didn't push anything - - /* If we just pushed a join containing an ORDER BY and/or a GROUP BY clause, - * we have to ensure that we either can skip the sort by scanning an ordered index, - * or write to a temp. table later being filesorted. - */ - if (join->const_tables < join->tables && - join->join_tab[join->const_tables].table->file->number_of_pushed_joins() > 0) - { - if (join->group_list && join->simple_group && - join->ordered_index_usage!=JOIN::ordered_index_group_by) - { - join->need_tmp= 1; - join->simple_order= join->simple_group= 0; - } - else if (join->order && join->simple_order && - join->ordered_index_usage!=JOIN::ordered_index_order_by) - { - join->need_tmp= 1; - join->simple_order= join->simple_group= 0; - } - } - - return 0; -} -#endif // MCP_WL4784 - /** Set NESTED_JOIN::counter=0 in all nested joins in passed list. No bundle (reason: useless for push emails).