Re: Fold NOT IN / <> ALL expressions containing NULL to FALSE

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, failed
Spec compliant: tested, passed
Documentation: tested, passed

Hi Ilia,

Denis Smirnov and I reviewed v2 and found these issues:

1. Multidimensional arrays

CREATE TEMP TABLE mda (a int[]);
INSERT INTO mda VALUES (NULL::int[]);
SELECT * FROM mda WHERE 1 <> ALL (ARRAY[NULL::int[], a]);

master: 1 row
v2: 0 rows

2. ON CONFLICT with a partial index

CREATE TEMP TABLE t (a int, b int);
CREATE UNIQUE INDEX ti ON t (a) WHERE b <> ALL (ARRAY[1, NULL]);
INSERT INTO t VALUES (1, 5)
ON CONFLICT (a) WHERE b <> ALL (ARRAY[1, NULL]) DO NOTHING;

master: succeeds
v2: ERROR: there is no unique or exclusion constraint matching
the ON CONFLICT specification

3. No folding under AND/OR

CREATE TEMP TABLE s (x int);

-- Plans with v2:
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL); -- One-Time Filter: false
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL) AND x = 1; -- Seq Scan
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL) OR false; -- Seq Scan

Perhaps this could be handled in canonicalize_qual().

Regards,
--
Rustam Allakov
Denis Smirnov

The new status of this patch is: Waiting on Author