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
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
