Bug #47054 Cluster only deletes first matched row in delete with left join
Submitted: 2 Sep 2009 7:47 Modified: 17 Dec 2009 13:01
Reporter: Hartmut Holzgraefe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:mysql-5.1-telco-7.0 OS:Linux
Assigned to: Martin Skold
Tags: mysql-5.1.34-ndb-7.0.6
Triage: Triaged: D2 (Serious) / R6 (Needs Assessment) / E6 (Needs Assessment)

[2 Sep 2009 7:47] Hartmut Holzgraefe
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;
[2 Sep 2009 7:50] Hartmut Holzgraefe
may be related to bug 9719 somehow?
[2 Sep 2009 7:56] Hartmut Holzgraefe
mysqltest test case

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

[2 Sep 2009 8:29] Hartmut Holzgraefe
Workaround: use optimizer hints like this:

  DELETE c.* 
    FROM child AS c IGNORE INDEX (PRIMARY)
  ...
[14 Dec 2009 10:05] 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/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
[15 Dec 2009 14:21] 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/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
[15 Dec 2009 14:45] 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/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
[15 Dec 2009 21:47] 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/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
[15 Dec 2009 22:28] Bugs System
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)
[15 Dec 2009 22:30] Bugs System
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)
[15 Dec 2009 22:34] Bugs System
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)
[17 Dec 2009 13:01] Jon Stephens
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.