From: | Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG in temp tables involving a temp table not properly hiding a regular table as well as allowing non-existent column names |
Date: | 2005-06-07 16:06:32 |
Message-ID: | 200506071806.34914.ftm.van.vugt@foxi.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2005-06-07 16:23:32 | Re: BUG in temp tables involving a temp table not properly |
Previous Message | Tom Lane | 2005-06-07 14:11:45 | Re: BUG #1455: pg_dumpall fails |