bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (jan.wedvik:3419)

[Jan Wedvik]

#At file:///net/atum17/export/home/tmp/jw159207/mysql/repo/push-scan-scan/ based on revid:jan.wedvik@stripped

 3419 Jan Wedvik	2011-02-08
      This commits adds an extra MTR test case, testing an outer join with two
      scans. (This query would give wrong results if it was pushed.)
      
      It also adds some comments which explain more in detail why such queries 
      cannot be pushed.
      
      Finaly, the commit fixes a bug that causes a crash in signal logging when 
      running code compiled with -DVM_TRACE. 

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.test
      sql/ha_ndbcluster_push.cc
      storage/ndb/src/ndbapi/TransporterFacade.cpp
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2011-01-21 13:56:43 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2011-02-08 12:17:17 +0000
@@ -1875,6 +1875,20 @@ where t2.a = 4 and t2.b=4
 group by t2.c order by t2.c;
 c	count(distinct t2.a)
 4	1
+create table tx like t1;
+insert into tx 
+select x1.a+x2.a*16, x1.b+x2.b*16, x1.c+x2.c*16, x1.d+x2.d*16 
+from t1 as x1 cross join t1 as x2;
+explain select count(*) from tx as x1 
+left join tx as x2 on x1.c=x2.a and x1.d=x2.d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	x1	ALL	NULL	NULL	NULL	NULL	256	
+1	SIMPLE	x2	ref	PRIMARY	PRIMARY	4	test.x1.c	2	
+select count(*) from tx as x1 
+left join tx as x2 on x1.c=x2.a and x1.d=x2.d;
+count(*)
+304
+drop table tx;
 drop table t1;
 create table t1 (a int, b int, primary key(a) using hash) engine = ndb;
 insert into t1 values (1, 2);
@@ -4808,7 +4822,7 @@ LOCAL+REMOTE READS_SENT
 drop table spj_counts_at_startup;
 drop table spj_counts_at_end;
 scan_count
-2414
+2676
 pruned_scan_count
 8
 sorted_scan_count

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2011-01-04 14:50:54 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2011-02-08 12:17:17 +0000
@@ -886,6 +886,21 @@ join t1 as t2 on t1.a = t2.c and t1.b = 
 where t2.a = 4 and t2.b=4
 group by t2.c order by t2.c;
 
+create table tx like t1;
+
+insert into tx 
+  select x1.a+x2.a*16, x1.b+x2.b*16, x1.c+x2.c*16, x1.d+x2.d*16 
+    from t1 as x1 cross join t1 as x2;
+
+# Test of outer join with scan child.
+# This query should not be pushed. Doing so would produce lots of extra
+# [<x1 row>.NULL] rows, since the x1.d=x2.d predicate cannot be pushed.
+explain select count(*) from tx as x1 
+  left join tx as x2 on x1.c=x2.a and x1.d=x2.d;
+select count(*) from tx as x1 
+  left join tx as x2 on x1.c=x2.a and x1.d=x2.d;
+
+drop table tx;
 drop table t1;
 
 

=== modified file 'sql/ha_ndbcluster_push.cc'
--- a/sql/ha_ndbcluster_push.cc	2011-01-18 08:17:07 +0000
+++ b/sql/ha_ndbcluster_push.cc	2011-02-08 12:17:17 +0000
@@ -703,6 +703,32 @@ ndb_pushed_builder_ctx::is_pushable_as_c
       DBUG_PRINT("info", ("  Force artificial grandparent dependency through scan-child %s",
                          scan_descendant->get_table()->alias));
 
+      /**
+       * Outer joining scan-scan is not supported, due to the following problem:
+       * Consider the query:
+       *
+       * select * from t1 left join t2 
+       *   where t1.attr=t2.ordered_index and predicate(t1.row, t2. row);
+       *
+       * Where 'predicate' cannot be pushed to the ndb. The ndb api may then
+       * return:
+       * +---------+---------+
+       * | t1.row1 | t2.row1 | 
+       * | t1.row2 | t2.row1 | 
+       * | t1.row1 | t2.row2 |
+       * +---------+---------+
+       * Now assume that all rows but [t1.row1, t2.row1] satisfies 'predicate'.
+       * mysqld would be confused since the rows are not grouped on t1 values.
+       * It would therefor generate a NULL row such that it returns:
+       * +---------+---------+
+       * | t1.row1 | NULL    | -> Error! 
+       * | t1.row2 | t2.row1 | 
+       * | t1.row1 | t2.row2 |
+       * +---------+---------+
+       * 
+       * (Outer joining with scan may be indirect through lookup operations 
+       * inbetween)
+       */
       if (scan_descendant && 
          table->get_join_type(scan_descendant) == AQP::JT_OUTER_JOIN)
       {
@@ -745,9 +771,7 @@ ndb_pushed_builder_ctx::is_pushable_as_c
         ancestor_no= m_tables[ancestor_no].m_parent;
       } // while
 
-      // Outer joining scan-scan is not supported due to possible parent-NULL-row duplicates
-      // being created in the NdbResultStream when incomplete child batches are received.
-      // (Outer joining with scan may be indirect through lookup operations inbetween)
+      // Outer joining scan-scan is not supported, see comments above.
       if (scan_ancestor && 
          table->get_join_type(scan_ancestor) == AQP::JT_OUTER_JOIN)
       {

=== modified file 'storage/ndb/src/ndbapi/TransporterFacade.cpp'
--- a/storage/ndb/src/ndbapi/TransporterFacade.cpp	2011-02-04 11:45:24 +0000
+++ b/storage/ndb/src/ndbapi/TransporterFacade.cpp	2011-02-08 12:17:17 +0000
@@ -1340,7 +1340,6 @@ TransporterFacade::sendSignal(const NdbA
   if(setSignalLog() && TRACE_GSN(aSignal->theVerId_signalNumber)){
     SignalHeader tmp = * aSignal;
     tmp.theSendersBlockRef = numberToRef(aSignal->theSendersBlockRef, theOwnId);
-    LinearSectionPtr ptr[3];
     signalLogger.sendSignal(tmp,
                             1,
                             aSignal->getConstDataPtrSend(),


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