Re: BUG in temp tables involving a temp table not properly hiding a regular table as well as allowing non-existent column names

Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl> writes:
> Looking forward to your analysis of the following bug:

This is not a bug, it is user error.

> CREATE TYPE full_sequence_type AS (id int);

> CREATE OR REPLACE FUNCTION full_sequence(integer, integer)
> RETURNS SETOF full_sequence_type
> ...

> create table f1 as select id as id2 from full_sequence(1, 100);

> create temp table f1 as select id as id2 from full_sequence(1, 100);

Since both tables have column id2, not id, the problem has certainly not
got anything to do with referencing the wrong table.

> select count(*) from full_sequence(1, 100) where id in (select id from f1);

The actual problem here is that "id" is a perfectly valid outer
reference from the sub-select. Think of this as

select count(*) from full_sequence(1, 100) as x
where x.id in (select x.id from f1);

That WHERE clause will always succeed as long as f1 isn't empty
(and id isn't null).

regards, tom lane