Incorrect query result may caused by Conflict key word

(1) By syang (ysy111) on 2024-03-08 11:27:58 [source]

Description

I excute the following stamtements in SQLite3 Fiddle(2024-03-06). In theory, sql2 should not return more records than sql1. Instead, sql1 return 2 records while sql3 return 3.

CREATE TABLE v0 ( c1 INTEGER PRIMARY KEY ON CONFLICT REPLACE, c2 UNIQUE );  
INSERT INTO v0 VALUES ( 0, 33 ), ( 11, 22 );
REPLACE INTO v0 VALUES ( 0, 11 ) ON CONFLICT ( c2 ) DO UPDATE SET c1 = c2, c2 = c2 ON CONFLICT ( c2 ) DO UPDATE SET c1 = c1, c2 = c1;  

SELECT count(*) FROM v0; --sql1
count(*)
2

SELECT count(*) FROM v0 WHERE c2 > 8; --sql2
count(*)
3

(2) By Richard Hipp (drh) on 2024-03-08 14:36:40 in reply to 1 [link] [source]

A bug was introduced by the "Generalized UPSERT" enhancement of version 3.35.0 (item 3 at https://sqlite.org/releaselog/3_35_0.html) in which a REPLACE INTO statement that contains redundant ON CONFLICT upsert clauses can result in a corrupt index.

This is not a vulnerability in the sense that there is no way for an attacker to exploit this flaw to invoke a memory error or to otherwise gain unauthorized access to the system. Nor is this ever likely to come up in a real-world system, since no programmer would purposefully write the redundant ON CONFLICT clauses needed to make it happen. However, if an attacker can run arbitrary SQL on a database, then he can also cause a UNIQUE index to go corrupt.

Any corruption caused by this flaw is detected by PRAGMA integrity_check and the damage can be easily repaired by running REINDEX.

The problem has now been fixed on trunk, on branch-3.45, and on branch-3.44. A new patch release 3.45.2 will probably be coming out within a few days. Fiddle has already been updated so the test script shown above should now give the correct answer in fiddle.