Hello everyone,
I've been experimenting with an interesting form of statement that tries to
implement an "upsert" operation and came across some unusual behavior. For
context, my table has a notion of a "logical key" aside from the primary
key, and this is what determines whether to update or insert a row. For
simplicity's sake, we can use this table:
CREATE TABLE demo (
id INTEGER PRIMARY KEY AUTOINCREMENT,
guid TEXT UNIQUE NOT NULL,
someInt INTEGER NOT NULL DEFAULT -1
);
The "upsert" statement looks like this:
WITH new (guid, someInt) AS (VALUES('abc', 777))
INSERT OR REPLACE INTO demo (id, guid, someInt)
SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
The idea is to maintain ROWID and merge the new and old values if I have a
matching guid, otherwise insert a new row. (I realize there are problems
with this approach and it's better to just use multiple statements in a
transaction. Roll with me.)
INSERT INTO demo (guid, someInt) VALUES ('abc', 7);
INSERT INTO demo (guid) VALUES ('def');
INSERT INTO demo (guid, someInt) VALUES ('abc', null);
Error: NOT NULL constraint failed: demo.someInt
select * from demo;
id guid someInt
---------- ---------- ----------
1 abc 7
2 def -1
--So far nothing unexpected. Let's see about the upsert...
WITH new (guid, someInt) AS (VALUES('def', 99))
INSERT OR REPLACE INTO demo (id, guid, someInt)
SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
--UPDATES OK
WITH new (guid, someInt) AS (VALUES('ghi', 1234))
INSERT OR REPLACE INTO demo (id, guid, someInt)
SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
--INSERTS OK
select * from demo;
id guid someInt
---------- ---------- ----------
1 abc 7
2 def 99
3 ghi 1234
--Now the part that has me baffled
WITH new (guid, someInt) AS (VALUES('xyz', null))
INSERT OR REPLACE INTO demo (id, guid, someInt)
SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
--No Error?!
select * from demo;
id guid someInt
---------- ---------- ----------
1 abc 7
2 def 99
3 ghi 1234
4 xyz -1 --???
--How did it insert a new row with the default value for someInt?
--Here's what the select produces for the insert:
WITH new (guid, someInt) AS (VALUES('o_O?', null))
SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
id guid IFNULL(new.someInt, old.someInt)
---------- ---------- --------------------------------
<null> o_O? <null>
--But wait...
INSERT INTO demo (id, guid, someInt) VALUES (null, 'o_O?', null);
Error: NOT NULL constraint failed: demo.someInt
It seems this statement circumvents the NOT NULL constraint failed error
and also inserts the proper default value instead of null. Is this behavior
expected? If it is, is it also documented somewhere? If it's not, is this a
bug?
Thanks for your attention,
Jonathan Koren
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
