Re: Query result differences between PostgreSQL 17 vs 16 - Mailing list pgsql-bugs

On Wed, Feb 26, 2025 at 4:03 PM Richard Guo <guofenglinux@gmail.com> wrote:
> On Wed, Feb 26, 2025 at 3:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Thanks for that.  The code and comment added to
> > restriction_is_always_true look good, but I can't help wondering
> > whether we don't need the same in restriction_is_always_false.
> > Not very sure what a query proving the need for that would
> > look like, but leaving it asymmetric feels wrong.

> Yeah, I think you are right.  The thing here is that we don't have a
> reliable way to determine if the input expression of a NullTest is
> non-nullable if it's a clone clause.  This applies to both
> restriction_is_always_true and restriction_is_always_false.  So I
> think we should add the same check in restriction_is_always_false too.
>
> I'll give it a try to find a query that shows this is necessary.

After some attempts, I failed to find such a query.  An IS NULL qual
is not strict, and it makes the outer join not commutable with other
joins per outer-join identity 3.  We could add another strict qual to
the join clauses to make the outer join commutable.  However, in this
case whether we reduce the IS NULL qual to a constant false would not
affect the join outputs, due to the strict qual.

I'm starting to believe that reducing an IS NULL clone clause to a
constant false should be safe.  Any thoughts?

Thanks
Richard