From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: Benoit Ryder <b.ryder@ateme.com>,
        "David G. Johnston" <david.g.johnston@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18429: Inconsistent results on similar queries with join
 lateral
In-reply-to: 
 <CAMbWs49KeppzynvCV-0-DFp5w=ih8ZcDQkCvYJHSMe6rATjnwQ@mail.gmail.com>
References: <18429-8982d4a348cc86c6@postgresql.org>
 <CAKFQuwaTm7gWL7S3KVFJCit92-JSFBoQ3Ax-msU+5P5YAw=C2w@mail.gmail.com>
 <441100.1712933417@sss.pgh.pa.us>
 <MR1P264MB1971C01DA74122691EBBEE9FF9042@MR1P264MB1971.FRAP264.PROD.OUTLOOK.COM>
 <450117.1712937002@sss.pgh.pa.us> <265068.1713049119@sss.pgh.pa.us>
 <CAMbWs4-7=CxbY6c_4w1DFfNSo1v+bwoOXAp3bRy2ycMPQGkSrg@mail.gmail.com>
 <508653.1713113077@sss.pgh.pa.us>
 <CAMbWs49KeppzynvCV-0-DFp5w=ih8ZcDQkCvYJHSMe6rATjnwQ@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Mon, 15 Apr 2024 17:09:19 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <742264.1713190833.1@sss.pgh.pa.us>
Date: Mon, 15 Apr 2024 10:20:33 -0400
Message-ID: <742265.1713190833@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/742265.1713190833%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> I agree that the new test case for v15 does not seem to be worth
> including in v16+.  It seems to me that it would be better if we can
> have another new test case to verify that we've included child rel's
> em_relids even for appendrel child relations with pseudoconstant
> translated variables, i.e. to verify that the change in equivclass.c
> takes effect.  Maybe with a query like below:

> explain (costs off)
> select * from tenk1 t1
>   left join lateral
>     (select t1.unique1 as t1u, 0 as c
>      union all
>      select t1.unique1 as t1u, 1 as c) s on true
> where t1.unique1 = s.c;

> Without the change in equivclass.c, this query would trigger the new
> added assert in get_baserel_parampathinfo for v16, and give a wrong plan
> for v15.  What do you think?

I didn't add such a test because there are already several cases
(in foreign_data.sql, IIRC) that trigger the assert, which is
how come I found the problem in the first place.  Admittedly,
those depend on potentially-changeable details of an
information_schema view, so maybe it'd be better to have a
bespoke test.

			regards, tom lane