BUG #8049: Incorrect results when using ORDER BY and query planner options - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8049
Logged by:          Teun Hoogendoorn
Email address:      th@atsc.nl
PostgreSQL version: 9.2.4
Operating system:   CentOS 6.3 final 64bit
Description:        =


Hi,

I've got a strange problem with a query that produces more results than
expected. I made
a reproducible example to illustrate the problem.

The following query should give only 1 result (instead of 2):

*****************************************************************

CREATE TABLE _bug_header
(
   h_n integer,
   CONSTRAINT _bug_header_unique UNIQUE (h_n)
);

CREATE TABLE _bug_line
(
   h_n integer,
   l_n integer
);

INSERT INTO _bug_header VALUES(1);
INSERT INTO _bug_line VALUES(NULL, 1);
INSERT INTO _bug_line VALUES(NULL, 2);

SET sort_mem TO 64; SET enable_seqscan TO 0; SET enable_hashjoin TO 0; SET
enable_mergejoin TO 0; SET enable_sort TO 1; SET enable_indexscan TO 1;
SELECT * FROM
(
    SELECT (COALESCE(h_n || '/', '') || l_n)::text AS fault
    FROM
    (
        SELECT _bug_header.h_n, _bug_line.l_n
        FROM _bug_line
        LEFT OUTER JOIN _bug_header on (_bug_line.h_n =3D _bug_header.h_n)
    ) AS tmp
) AS tmp2
    WHERE (lower(fault) =3D E'1')    =

ORDER BY =

    lower(fault) -- Removing the ORDER BY shows 1 (ok) record instead of 2
(wrong)
OFFSET 0;

*****************************************************************

Thanks,

Teun Hoogendoorn