On Jun 30, 2009, at 2:34 PM, Marcus Grimm wrote: > > I'm not sure what will happend if you for example > delete a table row that would be the next row that > sqlite3_step would see, but doing so to me sounds > a bad approach anyhow.
The official policy is that if you modify a table (via INSERT, UPDATE, or DELETE) in the middle of a SELECT, then what you get from the remainder of the SELECT is undefined. Sometimes, modifying a table has no effect on the remainder of the SELECT. For example, suppose you are running SELECT x FROM table1 WHERE y>10 ORDER BY x; where there is no index on x. The way SQLite implements this is to load all the rows that have y>10 into a temporary table that is sorted by x. Then it starts returning rows of the temporary table one by one. By the time the first row is returned, everything that will ever be read from table1 by this query has already been read, so modifying table1 at that point will have no effect on subsequent SELECT output. On the other hand, if you have a query like this: SELECT x FROM table1 WHERE y>10; where there is no index on y, then the query will be implemented as a full table scan. SQLite will read the rows starting at the beginning of table1 and working toward the end. As each row is read, it will be tested to see if y>10. If y>10, then the row is returned. Otherwise, SQLite advances to the next row. In such a scenario, if you modify one of the later rows in the table, the modifications will be seen when the scan reaches the corresponding position in the table. Things can get tricky when you start looking at joins. Consider: SELECT t1.x, t2.y FROM t1 JOIN t2; This will typically be implemented as a nested loop: for each row in t1: for each row in t2: output t1.x, t2.y If you delete the current row from t1 while there are still more rows to go on t2, what output do you get for t1.x on subsequent rows? The answer is "it depends". Sometimes you will continue to get the original value of t1.x. Other times you will get a NULL. Whether you get the original value of t1.x or NULL might change from one point release to the next. Bottom line: It is best not to depend on the behavior of a SELECT after any row in the table being SELECTed is modified. SQLite tries to do something "sensible". But sometimes it is not clear what the sensible behavior is. If you need to modify a table as you are reading it from a SELECT, consider storing the results of the SELECT in a TEMP table, then reading the TEMP table as you modify the original table: CREATE TEMP TABLE xyz AS SELECT * FROM table1 WHERE....; SELECT * FROM xyz; -- make changes to table1 as xyz is read DROP TEMP TABLE xyz; D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users