[SOLVED] Does sqlite ignore WHERE clause after ON CONFLICT of UPSERT?

Does sqlite ignore WHERE clause after ON CONFLICT of UPSERT?

This is a minimal example:

CREATE TABLE t(x PRIMARY KEY,y);
INSERT INTO t VALUES (1,2);
INSERT INTO t VALUES (1,3)
    ON CONFLICT(x) WHERE 0 DO UPDATE SET y = 9
    ON CONFLICT DO UPDATE SET y = 10;
SELECT * FROM t;

The result is confusingly 1|9 where 1|10 is expected from my sense.

sqlite docs seems did not mention this where clause at all, although it is in the diagram.

Solution

It looks like the correct place to put the WHERE for a condition on the ON CONFLICT is after the DO.

INSERT INTO t VALUES (1,3)
    ON CONFLICT(x) DO UPDATE SET y = 9 WHERE 0
    ON CONFLICT DO UPDATE SET y = 10;

db<>fiddle

SQlite says the syntax of UPDATE follows Postgres. In Postgres, this syntax is used to specify that you are targeting a filtered index (ie it's part of the index column spec), rather than as part of the ON CONFLICT condition. However, it doesn't throw an error, because it infers that the non-filtered primary key also matches.

If an index_predicate is specified, it must, as a further requirement for inference, satisfy arbiter indexes. Note that this means a non-partial unique index (a unique index without a predicate) will be inferred (and thus used by ON CONFLICT) if such an index satisfying every other criteria is available.