From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: tharakan@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17769: Assert triggered in indxpath.c
In-reply-to: 
 <CAMbWs484gsu4sYFu_YgUXZsZoEMKAVAfHjgTOZTmnmPtVpES+Q@mail.gmail.com>
References: <17769-e4f7a5c9d84a80a7@postgresql.org>
 <CAMbWs484gsu4sYFu_YgUXZsZoEMKAVAfHjgTOZTmnmPtVpES+Q@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Fri, 03 Feb 2023 14:10:33 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2187721.1675542500.1@sss.pgh.pa.us>
Date: Sat, 04 Feb 2023 15:28:20 -0500
Message-ID: <2187722.1675542500@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2187722.1675542500%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> I believe what happens here is that after we pull up the LATERAL
> subquery we would have a FromExpr with only one child.  Then
> remove_useless_result_rtes elides it and merges its qual, which is
> 'n.nspname = c.relname', up to its parent.  This qual is then handed to
> EC machinery.

> When it comes to remove_useless_joins, we try to remove the LEFT join.
> We are supposed to remove any joinquals referencing rel 'n'.  But here
> remove_rel_from_query only checks rel->joininfo, and neglects to notice
> that there is another joinqual referencing rel 'n' existing in EC.  I
> think this is where the problem arises.

Actually, the problem is that we *shouldn't* try to remove the left
join, because the reference to ref_0.schemaname is logically "above"
that join.  I messed up the test for that in join_is_removable().
Apparently, we had no test cases that would expose such a condition,
probably because such a reference would typically cause strength-reducing
the left join.

> Another finding is that in remove_useless_result_rtes after we elide the
> single-child FromExpr and merge its qual up to its parent, the LEFT join
> is actually can be reduced to inner join.  But we fail to perform that
> since all this happens after we've done outer join reduction.

Yeah.  If we *did* do that then this bug would have escaped detection
awhile longer :-(.  I'm not terribly concerned about the lack of such
an optimization, but maybe we can look at it someday.

Fix pushed, thanks for investigating!

			regards, tom lane