From: | "hwa" <helmut(dot)r(dot)wagner(at)googlemail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4982: Wrong result of cartesian product when function result included in where clause |
Date: | 2009-08-12 18:10:26 |
Message-ID: | 200908121810.n7CIAQsd086946@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4982
Logged by: hwa
Email address: helmut(dot)r(dot)wagner(at)googlemail(dot)com
PostgreSQL version: 8.4.0, 64 Bit
Operating system: Mac OS X 10.5.8
Description: Wrong result of cartesian product when function result
included in where clause
Details:
Full test case below.
-- Creation of 2 test tables.
CREATE TABLE public.konten
(
konto_id int4 not null,
kontoname varchar(32) not null
);
CREATE TABLE public.abschlusstermine
(
abschlussdatum date not null,
abschlussart varchar(50) not null
);
-- Some test data
INSERT INTO public.konten (konto_id, kontoname) VALUES (1, 'Testkonto 1');
INSERT INTO public.konten (konto_id, kontoname) VALUES (2, 'Testkonto 2');
INSERT INTO public.konten (konto_id, kontoname) VALUES (3, 'Testkonto 3');
INSERT INTO public.konten (konto_id, kontoname) VALUES (4, 'Testkonto 4');
INSERT INTO public.abschlusstermine (abschlussdatum, abschlussart) VALUES
('2006-12-31', 'Jahr');
INSERT INTO public.abschlusstermine (abschlussdatum, abschlussart) VALUES
('2007-12-31', 'Jahr');
INSERT INTO public.abschlusstermine (abschlussdatum, abschlussart) VALUES
('2008-12-31', 'Jahr');
-- Cartesian Product with 12 rows (ok in 8.3.7 and 8.4.0)
SELECT abschlussart, abschlussdatum, konto_id, kontoname FROM public.konten,
public.abschlusstermine;
-- Simple function to produce a set of values.
create or replace function public.get_konten() returns setof int4 as
$$BEGIN
return next 1;
return next 2;
return next 3;
return;
END;
$$ LANGUAGE plpgsql RETURNS NULL ON NULL INPUT STABLE;
-- check for function results, 3 rows (ok in 8.3.7 and 8.4.0)
select * from public.get_konten();
-- Cartesian product of all rows from table konten without konto_id = 4. I
expect 9 rows from this query.
SELECT abschlussart, abschlussdatum, konto_id, kontoname FROM public.konten,
public.abschlusstermine
WHERE konto_id in (select * from public.get_konten());
Result with PostgreSQL 8.3.7 (ok)
abschlussart | abschlussdatum | konto_id | kontoname
--------------+----------------+----------+-------------
Jahr | 2006-12-31 | 2 | Testkonto 2
Jahr | 2007-12-31 | 2 | Testkonto 2
Jahr | 2008-12-31 | 2 | Testkonto 2
Jahr | 2006-12-31 | 3 | Testkonto 3
Jahr | 2007-12-31 | 3 | Testkonto 3
Jahr | 2008-12-31 | 3 | Testkonto 3
Jahr | 2006-12-31 | 1 | Testkonto 1
Jahr | 2007-12-31 | 1 | Testkonto 1
Jahr | 2008-12-31 | 1 | Testkonto 1
(9 rows)
Result with PostgreSQL 8.4.0:
abschlussart | abschlussdatum | konto_id | kontoname
--------------+----------------+----------+-------------
Jahr | 2008-12-31 | 1 | Testkonto 1
Jahr | 2008-12-31 | 2 | Testkonto 2
Jahr | 2008-12-31 | 3 | Testkonto 3
(3 rows)
At least, the results differ...
From | Date | Subject | |
---|---|---|---|
Next Message | Raja Rayaprol | 2009-08-12 19:44:09 | BUG #4983: PostgreSQL build fails |
Previous Message | Magnus Hagander | 2009-08-12 17:24:24 | Re: BUG #4961: pg_standby.exe crashes with no args |