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

L.S.

Looking forward to your analysis of the following bug:


database=# select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 8.0.3 on i586-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)


****
**** START HERE TO REPRODUCE
****

CREATE TYPE full_sequence_type AS (id int);
CREATE OR REPLACE FUNCTION full_sequence(integer, integer)
    RETURNS SETOF full_sequence_type
    LANGUAGE 'plpgsql'
    IMMUTABLE
    STRICT
    SECURITY INVOKER
    AS '    DECLARE
            my_from ALIAS FOR $1;
            my_to ALIAS FOR $2;
            result full_sequence_type%ROWTYPE;
        BEGIN
            -- just loop
            FOR i IN my_from..my_to LOOP
                result.id = i;
                RETURN NEXT result;
            END LOOP;

            -- finish
            RETURN;
        END;';

-- create a regular table and show that it holds 100 records
create table f1 as select id as id2 from full_sequence(1, 100);
-- show that it holds 100 records
select count(*) from f1;

-- create a regular table and show that it holds 100 records
create temp table f1 as select id as id2 from full_sequence(1, 100);
select count(*) from f1;

-- remove 99 records from the temp table and show that 1 row remains
delete from f1 where id2 > 1;
select count(*) from f1;

-- try to select a non-existent column, show that it fails
select id from f1;
ERROR:  column "id" does not exist

-- however, the following query will happily run AND return a wrong result
-- based on the regular table instead of the temporary one
select count(*) from full_sequence(1, 100) where id in (select id from f1);
 count
-------
   100
(1 row)



****
**** USE THIS TO CLEANUP
****

DROP FUNCTION full_sequence(integer, integer);
DROP TYPE full_sequence_type;
DROP TABLE f1;
DROP TABLE f1;




--
Best,




Frank.

On Tue, 7 Jun 2005, Frank van Vugt wrote:

> Looking forward to your analysis of the following bug:

You've analyzed the situation incorrectly I believe.

> -- however, the following query will happily run AND return a wrong result
> -- based on the regular table instead of the temporary one
> select count(*) from full_sequence(1, 100) where id in (select id from f1);

In this query in the subselect the id column is an outer
reference to the id column generated from full_sequence.  As far as I can
see, as long as there is at least one row in f1, all the rows generated by
full_sequence will match since it will compare the id in the outer row
against itself (and they are non null). This behavior is marginally
unfortunate, but seems to be required by spec.

Frank van Vugt <ftm.van.vugt@foxi.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

Ouch,

SS> You've analyzed the situation incorrectly I believe.
SS> In this query in the subselect the id column is an outer reference to
SS> the id column generated from full_sequence.

TL> This is not a bug, it is user error.
TL> The actual problem here is that "id" is a perfectly valid outer reference

You're right, now why didn't I see that myself.... ;)



Sorry to have bothered you guys / the list!




--
Best,




Frank.
an Oost 102-008
5013 CD  Tilburg
Tel: (+31).13.5425551, Fax: (+31).13.5452087
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Would you buy a car with the hood welded shut? (Linux, the maintainable OS)
This is Unix Country. On a quiet night you can hear NT reboot.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
De in dit emailbericht verstrekte informatie is vertrouwelijk en uitsluiten=
d=20
bestemd voor de geadresseerde. Het kopi=EBren van of verstrekken aan en geb=
ruik=20
door derden van deze informatie is niet toegestaan. Door de elektronische=20
verzending van dit bericht kunnen er geen rechten worden ontleend aan de=20
informatie.
This email and any files transmitted with it are confidential and intended=
=20
solely for the use of the individual or entity to whom they are addressed.
Copyright (c) 2005 Foxi C.i.T. BV All world wide rights reserved.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D