Hi.
When addressing a review comment on the fast partition pruning thread [1],
I noticed that specifying null in the IN-list will cause constraint
exclusion to wrongly fail to refute a table's check predicate.
create table foo (a int check (a = 1));
insert into foo values (null), (1);
-- ExecEvalScalarArrayOp() won't return true for any record in foo
select * from foo where a in (null, 2);
a
---
(0 rows)
-- The null in the IN-list prevents constraint exclusion to exclude foo
-- from being scanned in the first place
explain (costs off) select * from foo where a in (null, 2);
QUERY PLAN
---------------------------------------------
Seq Scan on foo
Filter: (a = ANY ('{NULL,2}'::integer[]))
(2 rows)
I propose a patch that teaches predtest.c to disregard any null values in
a SAOP (i.e., the IN (..) expression) when performing constraint exclusion
using that SAOP, because they cause predicate_refuted_by_recurse()'s logic
to fail to conclude the refutation. There is a rule that all items of an
OR clause (SAOP is treated as one) must refute the predicate. But the
OpExpr constructed with null as its constant argument won't refute
anything and thus will cause the whole OR clause to fail to refute the
predicate.
-- With the patch
explain (costs off) select * from foo where a in (null, 2);
QUERY PLAN
--------------------------
Result
One-Time Filter: false
(2 rows)
explain (costs off) select * from foo where a in (null, 2, 1);
QUERY PLAN
-----------------------------------------------
Seq Scan on foo
Filter: (a = ANY ('{NULL,2,1}'::integer[]))
(2 rows)
Thoughts?
Thanks,
Amit
[1]
https://www.postgresql.org/message-id/64241fee-09af-fe4b-a0ab-7cd739965041%40lab.ntt.co.jp
