IS NULL in UPDATE RETURNING clause yields an incorrect result

(1) By goranb on 2023-01-13 18:13:44 [source]

Let's say we have the following table:

CREATE TABLE bug (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    description VARCHAR (255) DEFAULT NULL
);

INSERT INTO bug (id, description)
VALUES (20, NULL);

For this statement:

update bug
set description = null
where id = 20
returning description is null;

sqlite returns 0(false), although the description is null.

It seems the bug was introduced with 3.38 release. It worked correctly before this release.

(2) By Richard Hipp (drh) on 2023-01-13 18:30:43 in reply to 1 [link] [source]

Thanks for the bug report.

Your work-around, until a proper fix is available, is to omit the redundant NOT NULL constraint on the "id" column. Since "is" is an INTEGER PRIMARY KEY, it is guaranteed to be NOT NULL anyhow, so the NOT NULL constraint does not add anything. But it does seem to be causing this error, perhaps because it RETURNING clause is mistakenly thinking that the NOT NULL constraint applies to the "description" column and is hence optimizing out the "IS NULL" test.

The previous paragraph is a guess and is subject to change as I dig further into the problem.

The simplified test case I am working with is as follows:

CREATE TABLE bug(id INTEGER PRIMARY KEY NOT NULL, x);
INSERT INTO bug(id,x) VALUES(20, NULL);
UPDATE bug SET x=NULL WHERE id = 20 RETURNING x, x IS NULL;

(3.1) By Richard Hipp (drh) on 2023-01-13 19:18:42 edited from 3.0 in reply to 2 [link] [source]

Bisect: https://sqlite.org/src/timeline?bid=n9b8dcd7905-n89c459e766-ybd41822c74-n84c8f63a1c-nf2f0426035-n19247e919f-y37e6e10f43-n23f042669a-ne654b57a9f-n524c2b87d7-n4711fb6954-y155c3e9c7c-y416602a851

Edit: This bisect turns out to be a red herring...

(4) By Richard Hipp (drh) on 2023-01-13 19:36:42 in reply to 1 [link] [source]

Thanks again for the succinct bug report. The problem should now be fixed on trunk as well as on the 3.40 branch and in the prerelease snapshot tarball on the Download Page.

Please confirm that the problem is fixed for you and let me know if you encounter any further difficulties.

(5) By goranb on 2023-01-14 16:54:16 in reply to 4 [link] [source]

Thanks for the quick replay. I can confirm that the workaround and prerelease snapshot works as expected.