Обсуждение: 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

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

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

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