[BUGS] BUG #8049: Incorrect results when using ORDER BY and query planner options

[th]

The following bug has been logged on the website:

Bug reference:      8049
Logged by:          Teun Hoogendoorn
Email address:      t...@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 = _bug_header.h_n)
        ) AS tmp
) AS tmp2
        WHERE (lower(fault) = E'1')     
ORDER BY 
        lower(fault) -- Removing the ORDER BY shows 1 (ok) record instead of 2
(wrong)
OFFSET 0;

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

Thanks,

Teun Hoogendoorn



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