Re: Query result differences between PostgreSQL 17 vs 16

On Thu, Feb 27, 2025 at 1:28 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I've not actually worked through the bug case to verify my hypothesis
> that it's the lack of a representative for a clone group that is
> breaking things. (Maybe you did?) But if that is the direct
> cause of the failure, then it's plausible that none of these
> callers are subject to it.

Yeah, I think this is the cause of the failure. Specifically, when we
start with (A/B)/C and transform to A/(B/C) per OJ identity 3, we need
a way to prevent upper clauses that use only C Vars from being pushed
down and applied as a filter clause at the lower B/C join. To achieve
this, we consider that the pushed-down B/C join has not completed, and
hence do not include B/C join's ojrelid in its relid set (it will be
added when we form the A/B join).

This is why, in this case, the version of 'customer.cid IS NOT NULL'
that is not marked as nullable by the left join to customer is chosen:
we've assumed that that left join has not completed yet. However,
performing the NOT NULL deduction on this clone would be unsafe. It
seems to me that, for clone clauses, the nullingrels mark is not that
reliable for performing such deductions.

> I'm a bit inclined to argue that we should put in the no-clone-quals
> limitation anyway, to protect future callers that might not manage
> to dodge the problem.

Now I'm inclined to agree with you. It seems we've been lucky so far
not to encounter issues with IS NULL deductions on clone clauses, but
this feels like a problem waiting to happen in the future.

The no-clone-quals limitation in restriction_is_always_false makes the
fix in f00ab1fd1 unnecessary. I think we should revert those changes.

Attached is the updated patch.

> OTOH, if we get rid of these functions
> altogether as I expressed my desire to do nearby, it's all moot.

Yeah, if we can find a way to perform the NullTest deduction during
eval_const_expressions, it will be much cleaner.

Thanks
Richard