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

[9 Feb 2006 1:28] Jonathan Miller
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;
[9 Feb 2006 9:56] Hartmut Holzgraefe
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;
[10 Feb 2006 15: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/2449
[10 Feb 2006 16:05] Tomas Ulin
pushed to 4.1.19, 5.019, 5.1.7
[14 Feb 2006 10:11] 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 in 4.1.19, 5.0.19, and 5.1.7 changelogs. Bug closed.