Re: BUG #17978: Unexpected error: "wrong varnullingrels (b) (expected (b 5)) for Var 6/2" triggered by JOIN

On Tue, Jun 20, 2023 at 4:59 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > Anyway, what I'm inclined to do is flesh out the attached by updating
> > the comments for remove_useless_results() and then push it. Later
> > on we can look for why it's not finding the better join order; that's
> > a separable issue, and if it is about avoid-clauseless-joins then we
> > might choose to live with it rather than incur a lot of planner cost
> > to fix it.
>
> I couldn't resist poking into that, and it seems there's less here
> than meets the eye. I'd been guessing that the presence or absence
> of a t2 reference in the WHERE clause was affecting this, but no: the
> SpecialJoinInfos look exactly the same for both queries, and the set
> of joins considered is the same in both. What is causing the
> different plan shape is that the selectivity estimates for these
> WHERE clauses are a lot different:

I was also wondering why the plan changes for this existing query after
applying this fix. After some investigation I came to the same
conclusion: it's all about different size estimates.

For this query, we'd form joinrel that includes {t1, t2, t3}, with or
without the fix. However, without the fix we form the joinrel with
{t1/t2} and {t3}, and thus treat 't2.f1 > 0' and 't3.f1 IS NULL' as
restrict clauses, and with that we calculate the joinrel size as 1.
After applying this fix, the t1/t2 join is not legal anymore (which I
think is right), and we form joinrel {t1/t2/t3} with {t1} and {t2/t3}.
This time we treat 't2.f1 = t1.f1' as restrict clause and calculate the
joinrel size as 5. I manually changed its size to 1 with gdb and then
the final plan changed back to the previous one, ie, the one that joins
t4 last, with exactly the same cost as previously.

Thanks
Richard