Bug #17249 delete statement with join where clause fails when table do not have pk
Submitted: 8 Feb 2006 23:33 Modified: 14 Feb 2006 10:05
Reporter: Jonathan Miller Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:5.1.7 OS:Linux (Linux 32 Bit OS)
Assigned to: Tomas Ulin CPU Architecture:Any

[8 Feb 2006 23:33] Jonathan Miller
Description:
create table t1 (a int);
create table t2 (a int);
insert into t1 values (1);
insert into t2 values (1);
delete t1.* from t1, t2 where t1.a = t2.a;

Errors are (from /home/ndbdev/jmiller/clones/mysql-5.1-new/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 8: query 'delete t1.* from t1, t2 where t1.a = t2.a' failed: 1032: Can't find record in 't1'
(the last lines may be the most important ones)

These all work:
1:
create table t1 (a int primary key);
create table t2 (a int);
insert into t1 values (1);
insert into t2 values (1);
delete t1.* from t1, t2 where t1.a = t2.a;
2:
create table t1 (a int);
create table t2 (a int  primary key);
insert into t1 values (1);
insert into t2 values (1);
delete t1.* from t1, t2 where t1.a = t2.a;
3:
create table t1 (a int);
create table t2 (a int);
insert into t1 values (1);
insert into t2 values (1);
select t1.* from t1, t2 where t1.a = t2.a;

 

How to repeat:
see above
[10 Feb 2006 16:06] Tomas Ulin
pushed to 4.1.19, 5.019, 5.1.7
[14 Feb 2006 10:05] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix in 4.1.19, 5.0.19, and 5.1.17 changelogs. Closed bug.