[BUGS] BUG #4982: Wrong result of cartesian product when function result included in where clause

[hwa]

The following bug has been logged online:

Bug reference:      4982
Logged by:          hwa
Email address:      helmut.r.wag...@googlemail.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...

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs