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

2013/4/9 <th(at)atsc(dot)nl>:
> 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;
>
> *****************************************************************

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: guedes(at)guedesoft(dot)net - skype: guediz
http://github.com/guedes - http://guedesoft.net
http://www.postgresql.org.br