bzr push into mysql-5.1-telco-7.0-spj branch (jan.wedvik:3186 to 3187)

[Jan Wedvik]

 3187 Jan Wedvik	2010-06-25
      This commit fixes an error that caused scan filters not to be pushed for the root operation (of a linked operation). 
      As a consequence, the ndb would send unneeded tuples to the mysql server. Also, descendant operations (i.e. linked 
      lookup operations) would be executed more times than necessary.
      
      This commit also adds a regression test which should check that filters are pushed and executed for both root 
      and non-root operations (by examining suitable ndbinfo counters).

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.test
      sql/ha_ndbcluster.cc
 3186 Ole John Aske	2010-06-24
      Fixed bugs in script used to run SPJ RQG tests:
      
      - Results from all repeated query executions in check_query()
        should be appended to resultfile instead of overwriting
        previous result.
      
      - Results from 'ndb pushed' should be written to its own file 
        instead of overwriting results from non-pushed ndb.
      
      - Also compare NDB results with MyIsam results

    modified:
      storage/ndb/test/rqg/runall.sh
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-06-23 12:19:40 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-06-25 07:49:01 +0000
@@ -2958,6 +2958,38 @@ READS_SENT	7
 drop table old_count;
 drop table new_count;
 drop table T1;
+create table T1 (
+a int primary key, 
+b int,
+c int) engine = ndb;
+insert into T1 values (1, 2, 3);
+insert into T1 values (2, 3, 4);
+insert into T1 values (3, 4, 5);
+select @spj_lookups:=sum(val) from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT');
+@spj_lookups:=sum(val)
+7620
+explain select * from T1 x, T1 y where x.b=y.a and x.c=4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	x	ALL	NULL	NULL	NULL	NULL	3	Parent of 2 pushed join@1; Using where with pushed condition
+1	SIMPLE	y	eq_ref	PRIMARY	PRIMARY	4	test.x.b	1	Child of pushed join@1
+select * from T1 x, T1 y where x.b=y.a and x.c=4;
+a	b	c	a	b	c
+2	3	4	3	4	5
+select sum(val) - @spj_lookups from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT');
+sum(val) - @spj_lookups
+1.000000000000000000000000000000
+explain select * from T1 x, T1 y, T1 z where x.b=y.a and y.c=4 and y.b=z.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	x	ALL	NULL	NULL	NULL	NULL	3	Parent of 3 pushed join@1
+1	SIMPLE	y	eq_ref	PRIMARY	PRIMARY	4	test.x.b	1	Child of pushed join@1; Using where with pushed condition
+1	SIMPLE	z	eq_ref	PRIMARY	PRIMARY	4	test.y.b	1	Child of pushed join@1
+select * from T1 x, T1 y, T1 z where x.b=y.a and y.c=4 and y.b=z.a;
+a	b	c	a	b	c	a	b	c
[phone]
+select sum(val) - @spj_lookups from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT');
+sum(val) - @spj_lookups
+5.000000000000000000000000000000
+drop table T1;
 create table T1(
 a int not null,
 b int not null,

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-06-23 12:19:40 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-06-25 07:49:01 +0000
@@ -1919,6 +1919,38 @@ drop table old_count;
 drop table new_count;
 drop table T1;
 
+### Test that scan filters are used for pushed operations.
+
+create table T1 (
+       a int primary key, 
+       b int,
+       c int) engine = ndb;
+
+insert into T1 values (1, 2, 3);
+insert into T1 values (2, 3, 4);
+insert into T1 values (3, 4, 5);
+
+# Find the total number of lookups issued by the SPJ blocks.
+select @spj_lookups:=sum(val) from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT');
+
+# Root scan should give only one tuple if scan filter is pushed. 
+# Therefore only one lookup on 'y'.
+explain select * from T1 x, T1 y where x.b=y.a and x.c=4;
+
+select * from T1 x, T1 y where x.b=y.a and x.c=4;
+
+select sum(val) - @spj_lookups from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT');
+
+# Lookup on y should only give one result tuple if filter is pushed.
+# This should give 3 lookups on 'y' and 1 on 'z', 4 in all.
+explain select * from T1 x, T1 y, T1 z where x.b=y.a and y.c=4 and y.b=z.a;
+
+select * from T1 x, T1 y, T1 z where x.b=y.a and y.c=4 and y.b=z.a;
+
+select sum(val) - @spj_lookups from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT');
+
+drop table T1;
+
 if (`select @@ndbinfo_version < ((7<<16) | (1 << 8))`)
 {
   --disable_query_log

=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc	2010-06-23 12:19:40 +0000
+++ b/sql/ha_ndbcluster.cc	2010-06-25 07:49:01 +0000
@@ -1562,7 +1562,7 @@ ha_ndbcluster::create_pushed_join(NdbQue
     ERR_RETURN(m_thd_ndb->trans->getNdbError());
 
   // Append filters for for pushed conditions where available
-  for (uint i= 1; i < m_pushed_join->get_operation_count(); i++)
+  for (uint i= 0; i < m_pushed_join->get_operation_count(); i++)
   {
     const TABLE* const tab= m_pushed_join->get_table(i);
     ha_ndbcluster* handler= static_cast<ha_ndbcluster*>(tab->file);


Attachment: [text/bzr-bundle] bzr/[e-mail]