Re: BUG #17700: An assert failed in prepjointree.c

On Mon, Nov 28, 2022 at 5:27 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> WITH table3 ( column34 ) AS NOT MATERIALIZED ( SELECT 1 ) SELECT 1 FROM (
> SELECT 1 FROM table3 WHERE column34 = ANY ( SELECT alias4 . column34 FROM
> table3 FULL JOIN table3 AS alias4 ON TRUE )) AS alias5 ;

Thanks for the report! I can reproduce this issue on HEAD.

I haven't got too much time looking into it. But the comment near the
assertion failure that says

* Unlike the LEFT/RIGHT cases, we just Assert that there are
* no PHVs that need to be evaluated at the semijoin's RHS,
* since the rest of the query couldn't reference any outputs
* of the semijoin's RHS.

I doubt this is true as a semijoin's qual can actually reference its
RHS. In this case the assertion failure happens because there is PHV in
the join's qual.

I tried the change as to also check for PHVs that have to be evaluated
in the semijoin's RHS, like how we do for left/right join, and it can
avoid the assertion failure. But I'm not sure if this is a reasonable
fix.

Thanks
Richard