From: | martin(dot)nzioki(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #9006: Incorrect update when using where with non-existent column in subselect |
Date: | 2014-01-28 00:08:40 |
Message-ID: | 20140128000840.26702.60938@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 9006
Logged by: Martin Nzioki
Email address: martin(dot)nzioki(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2014-01-28 00:12:21 | Re: BUG #9003: Hard-coding to localhost in postmaster |
Previous Message | Paul Morie | 2014-01-28 00:06:21 | Re: BUG #9003: Hard-coding to localhost in postmaster |