Re: BUG #8049: Incorrect results when using ORDER BY and query planner options - Mailing list pgsql-bugs

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