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

I can reproduce on 9.2.4 too.

it seams a filter was incorrect push down, I find a SQL produce the same
plan,but filter in the inner query.

test=# explain analyze SELECT * FROM
(
SELECT (COALESCE(h_n::varchar , '') || l_n) 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 fault = '1'
ORDER BY
1;
Nested Loop Left Join (cost=10000000000.00..10000000687.59 rows=11
width=8) (actual time=0.017..0.024 rows
=2 loops=1)
-> Seq Scan on _bug_line (cost=10000000000.00..10000000031.40
rows=2140 width=8) (actual time=0.005..0.
006 rows=2 loops=1)
-> Index Only Scan using _bug_header_unique on _bug_header
(cost=0.00..0.30 rows=1 width=4) (actual tim
e=0.002..0.002 rows=0 loops=2)
Index Cond: (h_n = _bug_line.h_n)
*
Filter: (((COALESCE((h_n)::character varying, ''::character
varying))::text || (_bug_line.l_n)::tex
t) = '1'::text)*
Heap Fetches: 0
Total runtime: 0.059 ms

*it produce same query plan and result with this SQL:*

explain analyze SELECT * FROM
(
SELECT (COALESCE(h_n::varchar , '') || l_n) 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 and
(COALESCE(_bug_header.h_n::varchar , '') || _bug_line.l_n) = '1')
*
) AS tmp
) AS tmp2;

Nested Loop Left Join (cost=10000000000.00..10000000714.21 rows=2140
width=8) (actual time=0.015..0.024 ro
ws=2 loops=1)
-> Seq Scan on _bug_line (cost=10000000000.00..10000000031.40
rows=2140 width=8) (actual time=0.005..0.
007 rows=2 loops=1)
-> Index Only Scan using _bug_header_unique on _bug_header
(cost=0.00..0.30 rows=1 width=4) (actual tim
e=0.002..0.002 rows=0 loops=2)
Index Cond: (h_n = _bug_line.h_n)
Filter: (((COALESCE((h_n)::character varying, ''::character
varying))::text || (_bug_line.l_n)::tex
t) = '1'::text)
Heap Fetches: 0
Total runtime: 0.058 ms

*but after analyze both tables,the problem is gone.*
*
*
*so, less accurate stat data can not only produce less efficient query
plan, but also wrong plan. I think it is a terrible bug. *

2013/4/10 Dickson S. Guedes <listas(at)guedesoft(dot)net>

> 2013/4/9 <th(at)atsc(dot)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=8FgXH8ciR7byKyMGMbdDfLdpWfzng@mail.gmail.com
> [2]
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5b7b5518d0ea56c422a197875f7efa5deddbb388
>
> []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
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

--
Jov
blog: http:amutu.com/blog <http://amutu.com/blog>