Does sqlite ignore WHERE clause after ON CONFLICT of UPSERT?

Question

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.

Answer 1

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.

Answer 2

It's just the way upsert works it does not involve the WHERE, a specific constraint in this case, x can be handled by each ON CONFLICT clause. SQLite selects the first matching clause in a conflict and ignores the others. Which clause is selected is unaffected by the WHERE; it only determines whether the update actually executes, SQLite skips the second ON CONFLICT(x) in your query and uses the first one despite WHERE 0 making it do nothing. Because of this, the outcome is 1|9 instead of 1|10.

If you want conditional logic use this then

INSERT INTO t VALUES (1,3)
ON CONFLICT(x) DO UPDATE
SET y = CASE WHEN 0 THEN 9 ELSE 10 END;