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

The following bug has been logged on the website:

Bug reference: 8049
Logged by: Teun Hoogendoorn
Email address: th(at)atsc(dot)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