From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18652: Planner can not find pathkey item to sort for query
 with expression and expression index
In-reply-to: 
 <CAMbWs49Pcnm_cX=7azYY9d8+qRrOOKDjJDCa0KGRk9kWWMOz3A@mail.gmail.com>
References: <18652-deaa782ebcca85d1@postgresql.org>
 <2527690.1728510183@sss.pgh.pa.us>
 <CAMbWs49Pcnm_cX=7azYY9d8+qRrOOKDjJDCa0KGRk9kWWMOz3A@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Thu, 10 Oct 2024 11:52:41 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2572173.1728534842.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Thu, 10 Oct 2024 00:34:02 -0400
Message-ID: <2572174.1728534842@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2572174.1728534842%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> 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.
> ...
> I think this is because find_computable_ec_member() only tries to
> match Vars.  Maybe we should teach it to also match OpExprs?

Not sure.  Having nothing much better to do this evening,
I cranked up a VM with an old OS and was able to "git bisect"
the problem with some tedious manual hackery.  The end result was

11cad29c91524aac1d0b61e0ea0357398ab79bf8 is the first bad commit
commit 11cad29c91524aac1d0b61e0ea0357398ab79bf8
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Thu Oct 14 16:56:39 2010 -0400

    Support MergeAppend plans, to allow sorted output from append relations.

So it's specific to MergeAppend and it's been wrong from day zero.
That makes me think it's probably not find_computable_ec_member's
fault directly.  Fixing it there might be the most expedient answer,
but I feel like first we should drill down a bit further to understand
the root problem.  I'm too tired to do more tonight though.

			regards, tom lane