Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

On Wed, 16 Jan 2019 at 03:33, James Coleman <jtc331(at)gmail(dot)com> wrote:
> > 2. I was also staring predicate_implied_by_simple_clause() a bit at
> > the use of clause_is_strict_for() to ensure that the IS NOT NULL's
> > operand matches the ScalarArrayOpExpr left operand. Since
> > clause_is_strict_for() = "Can we prove that "clause" returns NULL if
> > "subexpr" does?", in this case, your clause is the ScalarArrayOpExpr's
> > left operand and subexpr is the IS NOT NULL's operand. This means
> > that a partial index with "WHERE a IS NOT NULL" should also be fine to
> > use for WHERE strict_func(a) IN (1,2,..., 101); since strict_func(a)
> > must be NULL if a is NULL. Also also works for WHERE a+a
> > IN(1,2,...,101); I wonder if it's worth adding a test for that, or
> > even just modify one of the existing tests to ensure you get the same
> > result from it. Perhaps it's worth an additional test to ensure that x
> > IN(1,2,...,101) does not imply x+x IS NOT NULL and maybe that x+x IS
> > NULL does not refute x IN(1,2,...,101), as a strict function is free
> > to return NULL even if it's input are not NULL.
>
> Are you suggesting a different test than clause_is_strict_for to
> verify the saop LHS is the same as the null test's arg? I suppose we
> could use "equal()" instead?

I wasn't suggesting any code changes. I just thought the code was
sufficiently hard to understand to warrant some additional tests that
ensure we don't assume that if the int4 column x is not null that also
x+x is not null. Only the reverse can be implied since int4pl is
strict.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services