thank you very much Richard! very interesting to get some info about these rather internal issues. This gives us a good background to review some potential dangerous loops. Your hint with the temp table is extremely useful.
Best wishes Marcus Grimm > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
