From: Tom Lane <tgl@sss.pgh.pa.us>
To: lauri.kajan@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18152: Join condition is not pushed down to union all
 subquery
In-reply-to: <18152-72d0fe42ca4283d9@postgresql.org>
References: <18152-72d0fe42ca4283d9@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Tue, 10 Oct 2023 06:26:26 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <335645.1696953980.1@sss.pgh.pa.us>
Date: Tue, 10 Oct 2023 12:06:20 -0400
Message-ID: <335646.1696953980@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/335646.1696953980%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> The join condition is not pushed down to a subquery containing UNION ALL
> when even a single query within the subquery contains a WHERE clause.

I think this is less about "can't push down" than "can't pull up",
specifically that prepjointree.c fails to flatten that subquery
into an "appendrel member", per the comments in is_safe_append_member:

     * It's only safe to pull up the child if its jointree contains exactly
     * one RTE, else the AppendRelInfo data structure breaks. The one base RTE
     * could be buried in several levels of FromExpr, however.  Also, if the
     * child's jointree is completely empty, we can pull up because
     * pull_up_simple_subquery will insert a single RTE_RESULT RTE instead.
     *
     * Also, the child can't have any WHERE quals because there's no place to
     * put them in an appendrel.  (This is a bit annoying...)

I don't recall at the moment if there are fundamental reasons not to
have per-child quals in appendrels, or if it could be done with the
application of enough elbow grease.  But it's probably not trivial.
That comment has been there quite awhile.

			regards, tom lane