Sergey Tikhonenko (tserge@dms.omskcity.com) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Wrong UPDATE if exist INNER JOIN and alias for table
Long Description
UPDATE test1 SET value = 10 FROM test1 t1 INNER JOIN test2 ON t1.extid=test2.extid WHERE t1.id=1;
This expression update all record i table "test1". Must update only 2 records. See example.
Sample Code
psql 7.3.1
----------begin--------------------
dis=# CREATE TABLE test1(id int, extid int, value int);
CREATE TABLE
dis=# INSERT INTO test1 values(1,1,5);
INSERT 259479 1
dis=# INSERT INTO test1 values(1,2,6);
INSERT 259480 1
dis=# INSERT INTO test1 values(1,3,7);
INSERT 259481 1
dis=# INSERT INTO test1 values(2,1,8);
INSERT 259482 1
dis=# INSERT INTO test1 values(2,2,9);
INSERT 259483 1
dis=# INSERT INTO test1 values(2,3,10);
INSERT 259484 1
dis=# CREATE TABLE test2(extid int);
CREATE TABLE
dis=# INSERT INTO test2 values(1);
INSERT 259487 1
dis=# INSERT INTO test2 values(2);
INSERT 259488 1
dis=# SELECT test1.* FROM test1 INNER JOIN test2 ON test1.extid=test2.extid WHERE test1.id=1;
id | extid | value
----+-------+-------
1 | 1 | 5
1 | 2 | 6
(records: 2)
dis=# UPDATE test1 SET value = 10 FROM test1 t1 INNER JOIN test2 ON t1.extid=test2.extid WHERE t1.id=1;
UPDATE 6
----------begin my comment--------------------
!!! WRONG !!!
----------end my comment--------------------
dis=# UPDATE test1 SET value = 10 FROM test2 WHERE test1.extid=test2.extid AND test1.id=1;
UPDATE 2
----------begin my comment--------------------
!!! TRUE !!!
----------end my comment--------------------
dis=# UPDATE test1 SET value = 10 FROM test1 INNER JOIN test2 ON test1.extid=test2.extid WHERE test1.id=1;
ERROR: Table name "test1" specified more than once
----------end--------------------
No file was uploaded with this report