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:
sqlite returns 0(false), although the description is null.
update bug set description = null where id = 20 returning 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]
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]
(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.