Thread: BUG #4982: Wrong result of cartesian product when function result included in where clause

The following bug has been logged online:

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

"hwa" <helmut.r.wagner@googlemail.com> writes:
> -- 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());

I get 9 rows from this in CVS tip.  I think you're getting bit by one of
the semijoin optimization bugs that we've already found in 8.4.
(IWO it's the IN that's the problem, not specifically either the
cartesian join or the function...)

            regards, tom lane