Bug #58791 Incorrect result as Cluster may fail to reject an unpushable condition
Submitted: 7 Dec 2010 17:04 Modified: 25 Jan 2011 11:04
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:5.1.51-ndb-7.1.9 OS:Any
Assigned to: Ole John Aske CPU Architecture:Any

[7 Dec 2010 17:04] Ole John Aske
Description:
When there are multiple references / instances of the same physical tables, ha_ndbcluster::cond_push() fails to recognize these multiple instances as different tables. 

This may lead to a condition which refers fields from these other instances to be pushed to Cluster datanodes.

These conditions will then base its evaluation on incorrect field values, and return an incorrect result.

Has not verified it, but assume this bug to be present in all versions <= telco-7.1.9

How to repeat:
create table t1 (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb;
insert into t1 values (1,0), (2,0), (3,0), (4,0);

# Correct result wo/ condition pushdown
set engine_condition_pushdown=0;

SELECT table1.pk1, table2.pk1, table1.pk2, table2.pk2
 FROM t1 AS table1 CROSS JOIN t1 AS table2
 WHERE table2.pk1 IN (0,3) AND
   (table1.pk1 = 7 OR table2.pk1 = 3);

+-----+-----+-----+-----+
| pk1 | pk1 | pk2 | pk2 |
+-----+-----+-----+-----+
|   2 |   3 |   0 |   0 |
|   3 |   3 |   0 |   0 |
|   4 |   3 |   0 |   0 |
|   1 |   3 |   0 |   0 |
+-----+-----+-----+-----+

#turn on condition_pushdown, and the result becomes incorrect
set engine_condition_pushdown=1;

SELECT table1.pk1, table2.pk1, table1.pk2, table2.pk2
 FROM t1 AS table1 CROSS JOIN t1 AS table2
 WHERE table2.pk1 IN (0,3) AND
   (table1.pk1 = 7 OR table2.pk1 = 3);

+-----+-----+-----+-----+
| pk1 | pk1 | pk2 | pk2 |
+-----+-----+-----+-----+
|   3 |   3 |   0 |   0 |
+-----+-----+-----+-----+

Suggested fix:
ha_ndbcluster::cond_push() should reject any condition which refer fields from other tables, or other instances of this table.
[7 Dec 2010 17:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126246

4015 Ole John Aske	2010-12-07
      Fix for bug#58791, for review.
      
      ha_ndbcluster::cond_push() should reject to push any condition which refer fields
      outside the scope of 'this' table.
[7 Dec 2010 17:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126247

3392 Ole John Aske	2010-12-07
      SPJ-scan-scan: Cherry picked fix for bug#58791: Incorrect result as Cluster may fail to reject an unpushable condition.
[7 Dec 2010 17:29] Ole John Aske
Reset status to 'Open' as bug has to be verified by Cluster team.
[8 Dec 2010 8:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126291

4015 Ole John Aske	2010-12-08
      Updated fix for bug#58791 - Added missing '--sorted_result' to test
      
      ha_ndbcluster::cond_push() should reject to push any condition which refer fields
      outside the scope of 'this' table.
[17 Jan 2011 13:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128964

4120 Ole John Aske	2011-01-17
      Updated fix for Bug#58791 'Incorrect result as Cluster may fail to reject an unpushable condition'
      
      ha_ndbcluster::cond_push() should reject to push any condition 
      which refer fields outside the scope of 'this' table.
      
      The rejection of pushing should be signaled by returning the 
      remaining unpushed condition - or entire 'cond' if nothing was
      pushed.
      
      Also fixes two existing bugs in ha_ndbcluster::cond_push where
      we should return 'cond' instead of 'NULL' to signal that 
      the condition was unpushable (Due to memory alloc failure.)
     @ sql/ha_ndbcluster_cond.cc
        Fixed a bug in rejecting pushing of 'cond'. When a condition
        is rejected, the unpushable part of the condition should be returned.
[17 Jan 2011 13:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128973

4124 Ole John Aske	2011-01-17
      Updated fix for Bug#58791 'Incorrect result as Cluster may fail to reject an unpushable condition'
      
      ha_ndbcluster::cond_push() should reject to push any condition 
      which refer fields outside the scope of 'this' table.
      
      The rejection of pushing should be signaled by returning the 
      remaining unpushed condition - or entire 'cond' if nothing was
      pushed.
      
      Also fixes two existing bugs in ha_ndbcluster::cond_push where
      we should return 'cond' instead of 'NULL' to signal that 
      the condition was unpushable (Due to memory alloc failure.)
     @ sql/ha_ndbcluster_cond.cc
        Fixed a bug in rejecting pushing of 'cond'. When a condition
        is rejected, the unpushable part of the condition should be returned.
[17 Jan 2011 13:30] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.22 (revid:ole.john.aske@oracle.com-20110117132952-q6poszeqy1pbflwf) (version source revid:ole.john.aske@oracle.com-20110117132952-q6poszeqy1pbflwf) (merge vers: 5.1.51-ndb-7.0.22) (pib:24)
[17 Jan 2011 13:33] Ole John Aske
Pushed into mysql-5.1-telco-7.0 ++
[25 Jan 2011 8:23] Jonas Oreland
pushed to 7.0.22 and 7.1.11
[25 Jan 2011 11:04] Jon Stephens
Documented in the NDB 7.0.22 and 7.1.11 changelogs as follows:

        When a query used multiple references to or instances of the
        same physical tables, NDB failed to recognize these multiple
        instances as different tables; in such a case, NDB could
        incorrectly use condition pushdown on a condition referring to
        these other instances to be pushed to to the data nodes, even
        though the condition should have been rejected as unpushable,
        leading to invalid results.

Closed.