Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
"Dickson S. Guedes"
Date:
2013/4/9  <th@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=8FgXH8ciR7byKyMGMbdDfLdpWfzng@mail.gmail.com
[2] http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5b7b5518d0ea56c422a197875f7efa5deddbb388

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

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

From
Jov
Date:
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@guedesoft.net>
2013/4/9  <th@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=8FgXH8ciR7byKyMGMbdDfLdpWfzng@mail.gmail.com
[2] http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5b7b5518d0ea56c422a197875f7efa5deddbb388

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


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



--
Jov

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

From
Andres Freund
Date:
On 2013-04-25 16:27:47 +0800, Jov wrote:
> I can  reproduce on 9.2.4 too.
> 
> plan, but also wrong plan. I think it is a terrible bug.  *

Just in case you missed it, there's ongoing work to fix it. For some
explanations see:
http://www.postgresql.org/message-id/6546.1365701142@sss.pgh.pa.us

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

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

From
Jov
Date:
my fault,thanks!


2013/4/25 Andres Freund <andres@2ndquadrant.com>
On 2013-04-25 16:27:47 +0800, Jov wrote:
> I can  reproduce on 9.2.4 too.
>
> plan, but also wrong plan. I think it is a terrible bug.  *

Just in case you missed it, there's ongoing work to fix it. For some
explanations see:
http://www.postgresql.org/message-id/6546.1365701142@sss.pgh.pa.us

Greetings,

Andres Freund

--
 Andres Freund                     http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services



--
Jov