Different behavior between tables with and without rowid
(1) By Marco Bubke (marcob) on 2021-11-30 13:37:31 [link] [source]
I have an sync algorithm which is stepping over a query and comparing it with a vector of key values types. The query and the vector are ordered/sorted by the key. If the values for the same key differ I update the values with that key. This is working very well for tables with rowids even if I never use the rowid. But it is not for tables without rowids. I get in the next step the already updated row. This breaks the synchronization.
Any idea what is going wrong?
The code is open source if you think it helps to see the source code.
(2) By Gunter Hick (gunter_hick) on 2021-11-30 14:14:23 in reply to 1 [link] [source]
It would help if you could show the schema and the SQL of the queries to be sure, but what is happening is an old problem. Modifying the tree you are traversing disrupts the notion of "next record" and may cause you to process some records once, more than once or not at all, depending on the exact changes. Either reformulate your query to be independant of visitation order (this may require putting the "vector" into a table instead of memory), or store changes and apply them when you have seen all records.
(3) By Marco Bubke (marcob) on 2021-11-30 15:18:13 in reply to 2 [link] [source]
I read some time ago in the documentation that the query is stable so long you don't change any row which is in from of your query. But this is maybe only working for tables with rowids.
If an application issues a SELECT statement on a single table like "SELECT rowid, * FROM table WHERE ..." and starts stepping through the output of that statement using sqlite3_step() and examining each row, then it is safe for the application to delete the current row or any prior row using "DELETE FROM table WHERE rowid=?". It is also safe (in the sense that it will not harm the database) for the application to delete a row that expected to appear later in the query but has not appeared yet. If a future row is deleted, however, it might happen that the row turns up after a subsequent sqlite3_step(), even after it has allegedly been deleted. Or it might not. That behavior is undefined. The application can also INSERT new rows into the table while the SELECT statement is running, but whether or not the new rows appear in subsequent sqlite3_step()s of the query is undefined. And the application can UPDATE the current row or any prior row, though doing so might cause that row to reappear in a subsequent sqlite3_step(). As long as the application is prepared to deal with these ambiguities, the operations themselves are safe and will not harm the database file.
I think I found it. The row is showing up again after the update.
I was using the other two approaches but they are slower and generate more code.
(4) By Gunter Hick (gunter_hick) on 2021-11-30 15:44:10 in reply to 3 [link] [source]
Like I said, don't saw off the branch you are sitting on. It is always the PRIMARY KEY which is affected, either the ROWID in ROWID tables or the PRIMARY KEY in WITHOUT ROWID tables. Statements whose query plan includes "use temp BTree for ORDER BY" are not affected, because all the rows have been located before tha first one is returned by sqlite3_step().
(5) By Marco Bubke (marcob) on 2021-11-30 15:57:10 in reply to 4 [source]
Thank you, good to know. I think I have to tweak the algorithm a little bit and then it should work always.