Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16

On Fri, Oct 23, 2026 at 9:09 PM Nitin Motiani <nitinmotiani(at)google(dot)com> wrote:
> Attached is v4 based on your suggestions, which keeps all cloned
> outer-join clauses and sets norm_selec = 1.0 and outer_selec = 1.0 on
> them.

Setting norm_selec and outer_selec to 1.0 is not enough. Those are
only cached values, and clause_selectivity_ext() skips the cache when
varRelid is nonzero and the clause references more than one base rel.
That is the case when estimating a parameterized scan, so the kept
clause gets its full selectivity there and is double-counted with the
derived INNERVAR = CONSTANT clause.

I think a simple way to fix this is to restore the redundancy marker
that v15 and earlier used and b448f1c8d removed: the kept clause gets
norm_selec = 2.0, and clause_selectivity_ext() returns 1.0 for any
clause with norm_selec > 1, before the cache check.

Also I think we should put the new tests in the session of "regression
test for improper pushing of constants across outer-join clauses", and
reuse the existing zt1/zt2/zt3 tables.

Hence, here is the v5 patch.

- Richard