It seems this is intended behavior. From sqlite.org/lang_conflict.html
under REPLACE:
> If a NOT NULL constraint violation occurs, the REPLACE conflict
resolution replaces
> the NULL value with the default value for that column, or if the column
has no default
> value, then the ABORT algorithm is used.
Sorry for the trouble. Thanks,
Jonathan Koren
On Fri, Jan 20, 2017 at 9:59 PM, Jonathan Koren <jdko...@gmail.com> wrote:
> 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
