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

[t***@atsc.nl]

The following bug has been logged on the website:

Bug reference: 8049
Logged by: Teun Hoogendoorn
Email address: ***@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

[Dickson S. Guedes]

Post by t***@atsc.nl
Bug reference: 8049
Logged by: Teun Hoogendoorn
PostgreSQL version: 9.2.4
Operating system: CentOS 6.3 final 64bit
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.
*****************************************************************
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;
*****************************************************************

I can reproduce that here and my EXPLAIN ANALYZE output is:

QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=10000000000.00..10000000716.58 rows=11
width=8) (actual time=0.049..0.061 rows=2 loops=1)
-> Seq Scan on _bug_line (cost=10000000000.00..10000000031.40
rows=2140 width=8) (actual time=0.010..0.011 rows=2 loops=1)
-> Index Only Scan using _bug_header_unique on _bug_header
(cost=0.00..0.31 rows=1 width=4) (actual time=0.003..0.003 rows=0
loops=2)
Index Cond: (h_n = _bug_line.h_n)
Filter: (lower((COALESCE(((h_n)::text || '/'::text),
''::text) || (_bug_line.l_n)::text)) = '1'::text)
Heap Fetches: 0
Total runtime: 0.155 ms

rows=2

Once I did an ANALYZE _bug_header; ANALYZE _bug_line; my EXPLAIN
ANALYZE output is:

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=10000000000.00..10000000009.39 rows=1 width=8)
Join Filter: (_bug_line.h_n = _bug_header.h_n)
Filter: (lower((COALESCE(((_bug_header.h_n)::text || '/'::text),
''::text) || (_bug_line.l_n)::text)) = '1'::text)
-> Seq Scan on _bug_line (cost=10000000000.00..10000000001.02
rows=2 width=8)
-> Materialize (cost=0.00..8.27 rows=1 width=4)
-> Index Only Scan using _bug_header_unique on _bug_header
(cost=0.00..8.27 rows=1 width=4)

rows=1

I tested against 9.1.x also but couldn't reproduce that behavior.

[]s
--
Dickson S. Guedes
mail/xmpp: ***@guedesoft.net - skype: guediz
http://github.com/guedes - http://guedesoft.net
http://www.postgresql.org.br

[Dickson S. Guedes]

Post by t***@atsc.nl
I've got a strange problem with a query that produces more results than
expected.

I tested this [1] and saw that 9.1 don't has the behavior then I
started bisect from REL9_1_9 to HEAD and found that commit 5b7b5518d0e
[2] introduced it.

I'm putting a copy to -hacker list in a hope that this help some one
with more experience on that code to go further.

[1] http://www.postgresql.org/message-id/CAHHcrepFavBPnTSE9Ho3=***@mail.gmail.com
[2] http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5b7b5518d0ea56c422a197875f7efa5deddbb388

[]s
--
Dickson S. Guedes
mail/xmpp: ***@guedesoft.net - skype: guediz
http://github.com/guedes - http://guedesoft.net
http://www.postgresql.org.br