Re: BUG #18764: server closed the connection unexpectedly - Mailing list pgsql-bugs

I wrote:
> Needs more digging.

Actually ... why is that Sort there at all?  The whole plan looks like

regression=# explain (costs off) 
SELECT c2 AS ca2, c2 AS ca3 FROM t0
UNION
SELECT DISTINCT ca8 AS ca5, ca7 AS ca6
  FROM (SELECT c1 AS ca7, c2 AS ca8 FROM t0) AS ta1
       JOIN
       (SELECT c1 AS ca10, c1 AS ca11 FROM t0) AS ta2
       ON TRUE;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Unique
   ->  Sort
         Sort Key: t0.c2, t0.c2
         ->  Append
               ->  Seq Scan on t0
               ->  Subquery Scan on "*SELECT* 2"
                     ->  Sort
                           Sort Key: t0_1.c2, t0_1.c1 USING <
                           ->  HashAggregate
                                 Group Key: t0_1.c2, t0_1.c1
                                 ->  Nested Loop
                                       ->  Seq Scan on t0 t0_1
                                       ->  Materialize
                                             ->  Seq Scan on t0 t0_2
(14 rows)

There is no value in forcing a sort of the subquery's output,
and the previous code didn't do so:

regression=# explain (costs off) 
SELECT c2 AS ca2, c2 AS ca3 FROM t0
UNION
SELECT DISTINCT ca8 AS ca5, ca7 AS ca6
  FROM (SELECT c1 AS ca7, c2 AS ca8 FROM t0) AS ta1
       JOIN
       (SELECT c1 AS ca10, c1 AS ca11 FROM t0) AS ta2
       ON TRUE;
                       QUERY PLAN                        
---------------------------------------------------------
 HashAggregate
   Group Key: t0.c2, t0.c2
   ->  Append
         ->  Seq Scan on t0
         ->  Subquery Scan on "*SELECT* 2"
               ->  HashAggregate
                     Group Key: t0_1.c2, t0_1.c1
                     ->  Nested Loop
                           ->  Seq Scan on t0 t0_1
                           ->  Materialize
                                 ->  Seq Scan on t0 t0_2
(11 rows)

I'm not sure if the change from hash to sort-and-unique at the
top level means anything.  But we surely shouldn't have bothered
with sorted output from the second UNION arm, even if we were
generating the right sort keys :-(.

Also, I've confirmed by looking at the plan tree that the implicit
cast of c1 from integer to numeric is done in the targetlist of
the Subquery Scan node.  (I'm surprised that EXPLAIN VERBOSE hides
that function call; it's not very helpful that it does so.)
But the lower Sort node does have

                      :sortOperators ( 1754 1754)

so it's trying to apply numeric_lt to both columns even though
the second one is still integer at that point.

I'm thinking at this point that the bug boils down to trying to
push pathkeys into the subplan without regard for the type
conversion that occurs at the set-operation level.  Once we've
done that, the lower level will generate this incorrectly-sorted
Path, and that probably wins the add_path tournament on the basis
of being better sorted and fuzzily the same cost as the unsorted
path.  So that's how come that path gets chosen even though
the sort is useless in context.

            regards, tom lane