Bug #47054 Cluster only deletes first matched row in delete with left join

Description:
On a query to remove orphan child records in a parent/child relation
the following query removes all orphans with id<100 with MyISAM or 
InnoDB but only the first one with cluster tables:

DELETE c.* 
  FROM child AS c 
  LEFT JOIN parent AS p 
    ON c.parent_id = p.id 
 WHERE p.id IS NULL 
   AND c.id < 100;

Without the "c.id < 100" condition all rows are deleted as expected
(independent of engine_condition_pushdown settings)

How to repeat:
DROP TABLE IF EXISTS child;
DROP TABLE IF EXISTS parent;

CREATE TABLE child
( id INT AUTO_INCREMENT PRIMARY KEY
, parent_id INT
) ENGINE=ndb;

CREATE TABLE parent 
( id INT AUTO_INCREMENT PRIMARY KEY
) ENGINE=ndb;

INSERT INTO parent VALUES (NULL),(NULL),(NULL);

INSERT INTO child VALUES (NULL, 1),(NULL,2),(NULL,3),(NULL,4),(NULL,5);

SELECT * 
FROM child AS c 
  LEFT JOIN parent AS p ON c.parent_id = p.id 
WHERE p.id IS NULL AND c.id < 100;

DELETE c.* 
FROM child AS c 
  LEFT JOIN parent AS p ON c.parent_id = p.id 
WHERE p.id IS NULL AND c.id < 100;

-- result set of following query should be empty but isn't for cluster
SELECT *
FROM child AS c 
  LEFT JOIN parent AS p ON c.parent_id = p.id 
WHERE p.id IS NULL AND c.id < 100;

may be related to bug 9719 somehow?

mysqltest test case

Attachment: bug47054.tgz (application/x-gtar, text), 1.03 KiB.

Workaround: use optimizer hints like this:

  DELETE c.* 
    FROM child AS c IGNORE INDEX (PRIMARY)
  ...

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/93868

3049 Martin Skold	2009-12-14
      Bug #47054 Cluster only deletes first matched row in delete with left join: Resetting status
      modified:
        mysql-test/suite/ndb/r/ndb_basic.result
        mysql-test/suite/ndb/t/ndb_basic.test
        sql/ha_ndbcluster.cc

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/94148

3199 Martin Skold	2009-12-15 [merge]
      Merge (Bug#47054 Cluster only deletes first matched row in delete with left join: Resetting status, Bug#49459 Incorrect handling of too long string in condition pushdown)
      modified:
        mysql-test/suite/ndb/r/ndb_basic.result
        mysql-test/suite/ndb/r/ndb_condition_pushdown.result
        mysql-test/suite/ndb/t/ndb_basic.test
        mysql-test/suite/ndb/t/ndb_condition_pushdown.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster_cond.cc
        sql/ha_ndbcluster_cond.h

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/94152

3285 Martin Skold	2009-12-15 [merge]
      Merge (Bug#47054 Cluster only deletes first matched row in delete with left join: Resetting status, Bug#49459 Incorrect handling of too long string in condition pushdown)
      modified:
        mysql-test/suite/ndb/r/ndb_basic.result
        mysql-test/suite/ndb/r/ndb_condition_pushdown.result
        mysql-test/suite/ndb/t/ndb_basic.test
        mysql-test/suite/ndb/t/ndb_condition_pushdown.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster_cond.cc
        sql/ha_ndbcluster_cond.h

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/94296

3195 Martin Skold	2009-12-15 [merge]
      Merge (Bug#47054 Cluster only deletes first matched row in delete with left join: Resetting status, Bug#49459 Incorrect handling of too long string in condition pushdown)
      modified:
        mysql-test/suite/ndb/r/ndb_basic.result
        mysql-test/suite/ndb/r/ndb_condition_pushdown.result
        mysql-test/suite/ndb/t/ndb_basic.test
        mysql-test/suite/ndb/t/ndb_condition_pushdown.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster_cond.cc
        sql/ha_ndbcluster_cond.h

Pushed into 5.1.39-ndb-7.1.0 (revid:martin.skold@mysql.com-20091215222649-iqgd7vyvenqq6gzc) (version source revid:martin.skold@mysql.com-20091215214635-gzqxh7i2biqx4c6y) (merge vers: 5.1.39-ndb-7.1.0) (pib:14)

Pushed into 5.1.39-ndb-7.0.11 (revid:martin.skold@mysql.com-20091215222838-1pupv0vybeu4nyx5) (version source revid:martin.skold@mysql.com-20091215144450-atl23gwk7xaarxz4) (merge vers: 5.1.39-ndb-7.0.11) (pib:14)

Pushed into 5.1.39-ndb-6.3.31 (revid:martin.skold@mysql.com-20091215223321-36ia6mqwhjkoqhye) (version source revid:martin.skold@mysql.com-20091215142101-o3a15a2rl0xgj9k0) (merge vers: 5.1.39-ndb-6.3.31) (pib:14)

Documented in the NDB-6.3.31 and 7.0.11 changelogs as follows:

      When performing a DELETE that included a left join from an NDB 
      table, only the first matching row was deleted.

Closed.