MySQL Bugs: #17257: update fails for inner joins if tables do not have Primary Key

Bug #17257	update fails for inner joins if tables do not have Primary Key
Submitted:	9 Feb 2006 1:28	Modified:	14 Feb 2006 10:11
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, 5.0bk	OS:	Linux (Linux 32 Bit OS)
Assigned to:	Tomas Ulin	CPU Architecture:	Any

Description:
This behaves a lot like http://bugs.mysql.com/bug.php?id=17249, they may be related. If you add just one PK to the tables, the update works.

Errors are (from /home/ndbdev/jmiller/clones/mysql-5.1-new/mysql-test/var/log/mysqltest-time) :
mysqltest: In included file "./extra/rpl_tests/rpl_multi_update3.test": At line 140: query 'UPDATE      t1 AS a
INNER JOIN  t2 AS b
ON a.i = b.i
INNER JOIN  t3 AS c
ON a.j = c.j  AND  b.k = c.k
SET         a.x = b.x,
a.y = b.y,
a.z = (
SELECT  sum(z)
FROM    t3
WHERE   y = 34
)
WHERE       b.x = 23' failed: 1032: Can't find record in 't1'
(the last lines may be the most important ones)

How to repeat:
CREATE TABLE t1 (
  i   INT,
  j   INT,
  x   INT,
  y   INT,
  z   INT
);

CREATE TABLE t2 (
  i   INT,
  k   INT,
  x   INT,
  y   INT,
  z   INT
);

CREATE TABLE t3 (
  j   INT,
  k   INT,
  x   INT,
  y   INT,
  z   INT
);

INSERT INTO t1 VALUES ( 1, 2,13,14,15);
INSERT INTO t2 VALUES ( 1, 3,23,24,25);
INSERT INTO t3 VALUES ( 2, 3, 1,34,35), ( 2, 3, 1,34,36);

UPDATE      t1 AS a
INNER JOIN  t2 AS b
              ON a.i = b.i
INNER JOIN  t3 AS c
              ON a.j = c.j  AND  b.k = c.k
SET         a.x = b.x,
            a.y = b.y,
            a.z = (
              SELECT  sum(z)
              FROM    t3
              WHERE   y = 34
            )
WHERE       b.x = 23;

5.0 reports this instad: ERROR 1032 (HY000): Can't find record in 'a'

standalone queries that show the problem (not relying on ndb being the default table type):

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  i   INT,
  j   INT,
  x   INT,
  y   INT,
  z   INT
-- , a   int primary key auto_increment -- uncomment this to make it work
) engine=ndb;

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
  i   INT,
  k   INT,
  x   INT,
  y   INT,
  z   INT
) engine=ndb;

DROP TABLE IF EXISTS t3;
CREATE TABLE t3 (
  j   INT,
  k   INT,
  x   INT,
  y   INT,
  z   INT
) engine=ndb;

INSERT INTO t1 VALUES ( 1, 2,13,14,15);
INSERT INTO t2 VALUES ( 1, 3,23,24,25);
INSERT INTO t3 VALUES ( 2, 3, 1,34,35), ( 2, 3, 1,34,36);

UPDATE      t1 AS a
INNER JOIN  t2 AS b
              ON a.i = b.i
INNER JOIN  t3 AS c
              ON a.j = c.j  AND  b.k = c.k
SET         a.x = b.x,
            a.y = b.y,
            a.z = (
              SELECT  sum(z)
              FROM    t3
              WHERE   y = 34
            )
WHERE       b.x = 23;

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

pushed to 4.1.19, 5.019, 5.1.7

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 in 4.1.19, 5.0.19, and 5.1.7 changelogs. Bug closed.