Thread: BUG #9006: Incorrect update when using where with non-existent column in subselect

The following bug has been logged on the website:

Bug reference:      9006
Logged by:          Martin Nzioki
Email address:      martin.nzioki@gmail.com
PostgreSQL version: 9.3.2
Operating system:   Windows 7 64-bit Professional SP1
Description:

drop table if exists _test1;
drop table if exists _tmp_test2;

create table _test1 (id int not null primary key, c2 int);
insert into _test1 (id) select * from generate_series(1, 5);

create table _tmp_test2 (id_fk int not null);
insert into _tmp_test2 (id_fk) select id from _test1 limit 2;

update _test1 set c2 = 7 where id in (select id from _tmp_test2);
select * from _test1 order by id;

Expected results:
id,c2
--,--
1,7
2,7
3,
4,
5,

Actual results:
id,c2
--,--
1,7
2,7
3,7
4,7
5,7

Changing the update to

update _test1 set c2 = 7 where id in (select id_fk from _tmp_test2);

returns the expected results.

Expectation is that the non-existent id_fk would have been caught during
parsing.

This still happens even when each statement is executed in its own
transaction.

Using pgScript on pgAdmin:

[QUERY    ] drop table if exists _test1
            NOTICE:  table "_test1" does not exist, skipping
[QUERY    ] drop table if exists _tmp_test2
            NOTICE:  table "_tmp_test2" does not exist, skipping
[QUERY    ] create table _test1 (id int not null primary key, c2 int)
[QUERY    ] insert into _test1 (id) select * from generate_series(1, 5)
[QUERY    ] create table _tmp_test2 (id_fk int not null)
[QUERY    ] insert into _tmp_test2 (id_fk) select id from _test1 limit 2
[QUERY    ] update _test1 set c2 = 7 where id in (select id from
_tmp_test2)
[QUERY    ] select * from _test1 order by id

martin.nzioki@gmail.com writes:
> create table _test1 (id int not null primary key, c2 int);
> insert into _test1 (id) select * from generate_series(1, 5);

> create table _tmp_test2 (id_fk int not null);
> insert into _tmp_test2 (id_fk) select id from _test1 limit 2;

> update _test1 set c2 = 7 where id in (select id from _tmp_test2);

This is a FAQ ... that statement is perfectly valid per SQL standard,
it just doesn't do what you expect, because the sub-select's "id" is
taken as an outer reference to _test1's id column.

            regards, tom lane

That is clear. Thanks.

On Tue, 28 Jan 2014 05:13:43 +0300, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> martin.nzioki@gmail.com writes:
>> create table _test1 (id int not null primary key, c2 int);
>> insert into _test1 (id) select * from generate_series(1, 5);
>
>> create table _tmp_test2 (id_fk int not null);
>> insert into _tmp_test2 (id_fk) select id from _test1 limit 2;
>
>> update _test1 set c2 = 7 where id in (select id from _tmp_test2);
>
> This is a FAQ ... that statement is perfectly valid per SQL standard,
> it just doesn't do what you expect, because the sub-select's "id" is
> taken as an outer reference to _test1's id column.
>
>             regards, tom lane