Hi,
I believe the patch is almost ready. But here are few optional issues.
1. Other never-true forms are not folded
CREATE TEMP TABLE s (a int);
INSERT INTO s VALUES (1), (NULL);
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE a NOT IN (42, NULL); -- One-Time Filter: false
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE a IN (NULL, NULL); -- Seq Scan
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE a = ANY ('{}'::int[]); -- Seq Scan
With a strict operator, every comparison against an all-NULL array
yields NULL. With an empty array, ANY is false whatever the operator.
saop_never_true() recognizes neither.
2. FULL JOIN and MERGE errors disappear
CREATE TEMP TABLE f1 (a int);
CREATE TEMP TABLE f2 (a int);
SELECT * FROM f1 FULL JOIN f2 ON f1.a NOT IN (1, NULL);
master: ERROR: FULL JOIN is only supported with merge-joinable or
hash-joinable join conditions
v8: succeeds
Whether a statement works now depends on the parameter value:
PREPARE p(int[]) AS
SELECT * FROM f1 FULL JOIN f2 ON f1.a <> ALL ($1);
EXECUTE p('{1,NULL}');
EXECUTE p('{1,2}');
master: both fail with the same ERROR
v8: the first succeeds, the second fails
MERGE now modifies data where it used to fail:
CREATE TEMP TABLE mt (a int, b int);
CREATE TEMP TABLE ms (a int, b int);
INSERT INTO mt VALUES (1, 1), (2, 2);
INSERT INTO ms VALUES (1, 100), (3, 300);
MERGE INTO mt USING ms ON mt.a NOT IN (1, NULL)
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET b = -2
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (ms.a, ms.b);
master: the same ERROR
v8: MERGE 4
3. Evaluation errors disappear
CREATE TEMP TABLE d (a int);
INSERT INTO d VALUES (1);
SELECT * FROM d WHERE (a / 0) NOT IN (1, NULL);
master: ERROR: division by zero
v8: 0 rows
master already behaves this way for similar forms, so this seems
acceptable:
SELECT * FROM d WHERE (a / 0) = NULL; -- 0 rows
SELECT * FROM d WHERE a NOT IN (NULL, 1 / (a - 1)); -- 0 rows
Still, the commit message should probably mention 2 and 3.
Best regards,
Denis Smirnov
