BUG #6209: Invalid subquery is accepted within a IN() clause - Mailing list pgsql-bugs

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;