Re: BUG #18652: Planner can not find pathkey item to sort for query with expression and expression index

On Thu, Oct 10, 2024 at 5:43 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It looks like we are generating a Path tree in which one of the
> inputs to a MergeAppend is a plain unsorted seqscan, which'd
> be all right except it doesn't expose the required sort value
> in its targetlist.

Correct.  In addition, find_computable_ec_member() fails to find a
computable expression from its targetlist.

Here is a slightly simpler repro query.

SELECT i + 0 AS c FROM (SELECT i FROM t UNION ALL SELECT i + 1 FROM t)
ORDER BY c;
ERROR:  could not find pathkey item to sort


I think the expected plan should look like:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT i + 0 AS c FROM (SELECT i FROM t UNION ALL SELECT i + 1 FROM t)
ORDER BY c;
                         QUERY PLAN
------------------------------------------------------------
 Result
   Output: ((t.i + 0))
   ->  Merge Append
         Sort Key: ((t.i + 0))
         ->  Index Scan using t_expr_idx on public.t
               Output: t.i, (t.i + 0)
         ->  Sort
               Output: ((t_1.i + 1)), (((t_1.i + 1) + 0))
               Sort Key: (((t_1.i + 1) + 0))
               ->  Seq Scan on public.t t_1
                     Output: (t_1.i + 1), ((t_1.i + 1) + 0)
(11 rows)

For the indexscan path, find_computable_ec_member() is able to find
(t.i + 0) which can be computed from its tlist item 't.i'.

For the seqscan path, though, find_computable_ec_member() is not able
to find ((t_1.i + 1) + 0) from its tlist item '(t_1.i + 1)'.

I think this is because find_computable_ec_member() only tries to
match Vars.  Maybe we should teach it to also match OpExprs?

Thanks
Richard