Re: remove_useless_joins vs. bug #19560

I wrote:
> What's apparently happening is that we pull up the sub-select,
> and eval_const_expressions flattens the CASE to constant-NULL,
> so we don't see the reference to sample_1.a and conclude that
> sample_1 can be dropped from the query. But when we do that,
> we try to mutate the original copy of the sub-select's tlist
> which still contains sample_1.a, and so we hit the Assert
> saying we should no longer see any such Vars.

More specifically, the raw CASE expression gets pushed down into
the lateral subquery, wrapped in a PlaceHolderVar. When we
pull it back up in extract_lateral_references(), we apply
expression preprocessing which flattens the CASE to constant-NULL.
So the lateral reference expression seen by the upper query has
no reference to sample_1, allowing join removal to remove sample_1.
But the PHV inside the subquery still has a reference, which makes
ChangeVarNodes fail, and even if we prevented that it'd still be
trouble when we repeat extract_lateral_references() after restarting.

So after contemplating my navel for awhile, the least grotty
solution I can see is to update the subquery's copy of the PHV
with the preprocessed expression, as attached. I don't really
love doing that, but the alternatives I can think of are worse.
Anyone have another idea?

One interesting point about this query is that if you remove
the topmost right join ("RIGHT JOIN public.ruletest_tbl2 AS ref_3
ON NULL" in the original example, "right join int4_tbl i42 on false"
in my proposed regression test), there's no failure. That's not
so surprising, because without that join we don't have to wrap the
CASE in a PlaceHolderVar, and then there's no discrepancy between what
the subquery contains and what the outer level thinks it contains.
But what is surprising is that we then fail to make the join removal,
which seems like a missed optimization. I didn't poke into that, but
I think it'd be worth figuring out why the removal doesn't happen.

regards, tom lane