[sqlite-dev] UPSERT syntax and trigger OR IGNORE conflict

[David Butler]

SQLite-dev,

Even using the latest update:

$ sqlite3

SQLite version 3.28.0 2019-04-12 16:25:42

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> .version

SQLite 3.28.0 2019-04-12 16:25:42 7b771405a9adc3ec191156be4ebe7122f4c698d88d69ae2134c75acb8d8feebb

zlib version 1.2.8

gcc-4.8.4

sqlite> CREATE table t1(i INTEGER PRIMARY KEY);

sqlite> CREATE table t2(i INTEGER PRIMARY KEY,c1);

sqlite> CREATE TRIGGER t2_au AFTER UPDATE OF c1 ON t2 BEGIN

   ...>  INSERT OR IGNORE INTO t1(i)VALUES(NEW.i);

   ...> END;

sqlite> INSERT INTO t2(i,c1)VALUES(1,1);

sqlite> INSERT INTO t2(i,c1)VALUES(1,2)ON CONFLICT(i)DO UPDATE SET(c1)=(excluded.c1);

sqlite> INSERT INTO t2(i,c1)VALUES(1,1)ON CONFLICT(i)DO UPDATE SET(c1)=(excluded.c1);

Error: UNIQUE constraint failed: t1.i

sqlite>

The “OR IGNORE” in the trigger’s INSERT is being ignored.

David

[David Butler]

SQLite-dev,

In reviewing the code in trigger.c, this behavior is intentional (on conflict policy of the top level statement overrides on conflict policy of trigger statements).

However, coding the trigger statement as an UPSERT:

INSERT INTO t1(i) VALUES (NEW.i) ON CONFLICT DO NOTHING;

Does *not* draw the UNIQUE CONSTRAINT.

David

_______________________________________________
sqlite-dev mailing list
sqlit...@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev

[David Butler]

SQLite-dev,

I think the problem is at upset.c in sqlite3UpsertDoUpdate() in the call to sqlite3Update(). Change OE_Abort to OE_Default.

A gentle suggestion. Add to the grammar of the UPSERT statement, after DO UPDATE, an ON CONFLICT section to specify the CONFLICT policy of the UPDATE (which, obviously can’t be the same as the CONFLICT policy on the top level INSERT).

David

[Richard Hipp]

On 4/15/19, David Butler <g...@dbsystems.com> wrote:
> SQLite-dev,
>

> I think the problem is at upset.c in sqlite3UpsertDoUpdate() in the call to
> sqlite3Update(). Change OE_Abort to OE_Default.
>
> A gentle suggestion. Add to the grammar of the UPSERT statement, after DO
> UPDATE, an ON CONFLICT section to specify the CONFLICT policy of the UPDATE
> (which, obviously can’t be the same as the CONFLICT policy on the top level
> INSERT).

Thank you. We are in the middle of trying to get out the 3.28.0
release right this moment. We will consider enhancement requests
afterwards.

--
D. Richard Hipp
d...@sqlite.org

[Bob Friesenhahn]

On Mon, 15 Apr 2019, Richard Hipp wrote:

> On 4/15/19, David Butler <g...@dbsystems.com> wrote:
>> SQLite-dev,
>>
>> I think the problem is at upset.c in sqlite3UpsertDoUpdate() in the call to
>> sqlite3Update(). Change OE_Abort to OE_Default.
>>
>> A gentle suggestion. Add to the grammar of the UPSERT statement, after DO
>> UPDATE, an ON CONFLICT section to specify the CONFLICT policy of the UPDATE
>> (which, obviously can’t be the same as the CONFLICT policy on the top level
>> INSERT).
>
> Thank you. We are in the middle of trying to get out the 3.28.0
> release right this moment. We will consider enhancement requests
> afterwards.

It seems that this issue has not been addressed yet in the latest
sqlite3 release. Hopefully it has not been forgotten. It would be
very useful if it were addressed so that triggers work as expected
when using the upsert syntax.

Bob
--
Bob Friesenhahn
bfri...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer, http://www.GraphicsMagick.org/
Public Key, http://www.simplesystems.org/users/bfriesen/public-key.txt

[G. David Butler]

Most interesting is this generated two bug reports and a feature request. None are visible at https://sqlite.org/src/rptview?rn=2

David

Sent from my phone. Please excuse misspellings and terseness.