List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:March 29 2012 10:39am
Subject:bzr push into mysql-trunk-cluster branch (ole.john.aske:3459 to 3460)
View as plain text  
 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).
Thread
bzr push into mysql-trunk-cluster branch (ole.john.aske:3459 to 3460) Ole John Aske29 Mar