| 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: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | mysql-5.1-telco-7.0 | OS: | Linux |
| Assigned to: | Martin Skold | CPU Architecture: | Any |
| Tags: | mysql-5.1.34-ndb-7.0.6 | ||
[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.
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;