BUG #6209: Invalid subquery is accepted within a IN() clause

The following bug has been logged online:

Bug reference: 6209
Logged by: Marc Mamin
Email address: marc@intershop.de
PostgreSQL version: 9.1beta3
Operating system: Linux
Description: Invalid subquery is accepted within a IN() clause
Details:

Hello,

This is somehow similar to BUG #6154 but I don't have yet a 9.1. Version to
test and I'm not sure that 9.1 already contains the Fix.

This issue can also be reproduced in 8.3.13

HTH,

Marc Mamin

steps to repeat:

CREATE TABLE test_f_files_steps
(
id bigserial NOT NULL,
file_id integer NOT NULL,
class_id integer NOT NULL,
step_id integer NOT NULL,
"timestamp" bigint NOT NULL,
infotext character varying,
efm_uid integer,
CONSTRAINT test_f_files_steps_pk PRIMARY KEY (id)
);

CREATE TABLE test_f_files_status
(
id serial NOT NULL,
class_id integer NOT NULL,
file_name character varying NOT NULL,
last_step_id integer NOT NULL,
runs smallint,
size bigint,
"timestamp" bigint,
plainday integer,
success boolean,
linecount integer,
rejected integer,
efm_uid integer NOT NULL,
CONSTRAINT test_f_files_status_pk PRIMARY KEY (id, class_id)
);

This is not valid, but is accepted.

EXPLAIN analyze
select * from test_f_files_steps where id in
(select id from
(
select file_id,class_id from test_f_files_steps
EXCEPT
select id,class_id from test_f_files_status
)foo
)

Seq Scan on test_f_files_steps (cost=0.00..26895.75 rows=430 width=64)
(actual time=0.001..0.001 rows=0 loops=1)
Filter: (SubPlan 1)
SubPlan 1
-> Subquery Scan on foo (cost=0.00..62.00 rows=200 width=0) (never
executed)
-> HashSetOp Except (cost=0.00..60.00 rows=200 width=8) (never
executed)
-> Append (cost=0.00..52.00 rows=1600 width=8) (never
executed)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..27.20
rows=860 width=8) (never executed)
-> Seq Scan on test_f_files_steps
(cost=0.00..18.60 rows=860 width=8) (never executed)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..24.80
rows=740 width=8) (never executed)
-> Seq Scan on test_f_files_status
(cost=0.00..17.40 rows=740 width=8) (never executed)
Total runtime: 0.087 ms

calling the IN subquery is correctly rejected:

select id from
(
select file_id,class_id from test_f_files_steps
EXCEPT
select id,class_id from test_f_files_status
)foo

ERROR: column "id" does not exist

drop table test_f_files_steps;
drop table test_f_files_status;