CREATE TABLE "public"."foo" (apple integer,banana integer,charlie integer);
CREATE INDEX "idx1" on "public"."foo" ("apple"); -- when this index is removed, all queries return results
INSERT INTO "foo" (apple, banana, charlie) values (10, 20, 30);
SELECT * FROM FOO;
SELECT * FROM FOO WHERE TRUE;
SELECT * FROM FOO WHERE TRUE AND TRUE;
SELECT * FROM FOO WHERE APPLE = 10 AND TRUE;
SELECT * FROM FOO WHERE APPLE IS NOT NULL;
SELECT * FROM FOO WHERE TRUE AND APPLE IS NOT NULL;
SELECT * FROM FOO WHERE APPLE IS NOT NULL AND TRUE; -- **bug** Actual is empty, but expected results
-- just to prove it's both 'IS NULL' and 'IS NOT NULL' we flip it around too:
UPDATE FOO SET APPLE = NULL;
SELECT * FROM FOO;
SELECT * FROM FOO WHERE TRUE;
SELECT * FROM FOO WHERE TRUE AND TRUE;
SELECT * FROM FOO WHERE APPLE IS NULL;
SELECT * FROM FOO WHERE TRUE AND APPLE IS NULL;
SELECT * FROM FOO WHERE APPLE IS NULL AND TRUE; -- **bug** Actual is empty, but expected results
NOTE: When you comment out the index, all queries work.
Describe the bug
In queries/updates on rows where you conditionally select on a column with an index against NULL /plus/ one more condition, the results will be empty.
Conditions required for bug:
AND
'd togetherIS NULL
orIS NOT NULL
on an indexed column.true
or1=1
, but it must be present.To Reproduce
https://oguimbal.github.io/pg-mem-playground/
NOTE: When you comment out the index, all queries work.
pg-mem version
2.8.1