Thread: 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@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 =3D _bug_header.h_n) ) AS tmp ) AS tmp2 WHERE (lower(fault) =3D E'1') = ORDER BY = lower(fault) -- Removing the ORDER BY shows 1 (ok) record instead of 2 (wrong) OFFSET 0; ***************************************************************** Thanks, Teun Hoogendoorn
Re: BUG #8049: Incorrect results when using ORDER BY and query planner options
From
"Dickson S. Guedes"
Date:
2013/4/9 <th@atsc.nl>: > The following bug has been logged on the website: > > Bug reference: 8049 > Logged by: Teun Hoogendoorn > Email address: th@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; > > ***************************************************************** 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@guedesoft.net - skype: guediz http://github.com/guedes - http://guedesoft.net http://www.postgresql.org.br
Re: 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
I can reproduce on 9.2.4 too.
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
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
(
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;
(
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 thanI tested this [1] and saw that 9.1 don't has the behavior then I
> expected.
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
blog: http:amutu.com/blog
Re: 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
my fault,thanks!
2013/4/25 Andres Freund <andres@2ndquadrant.com>
On 2013-04-25 16:27:47 +0800, Jov wrote:> plan, but also wrong plan. I think it is a terrible bug. *
> I can reproduce on 9.2.4 too.
>
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
blog: http:amutu.com/blog