On 02/13/2017 01:44 PM, Dudu Markovitz wrote:
Good morning
While answering question on stackoverflow
<http://stackoverflow.com/a/42197036/6336479>I have noticed a bug related
to UPDATE using correlated sub-query.
The demonstration code can be found in the attached file bug_report.sql and
the results of the demonstration with some comments added are in
bug_reports.txt.
I'm using SQLite 3.9.2 on windows 64 bit.
It's surprising, but a consequence of the way SQLite has always worked.
Consider:
CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 0);
Then:
UPDATE t1 SET b=(SELECT b+1 FROM t1 WHERE a=1);
In this case, the sub-query is uncorrelated and "b" is set to 2 in both
rows.
But say you added some other term to the sub-query so that it was
correlated. It is then executed separately for each row. So SQLite
updates the first row (with a=1) and sets column "b" to 2. But then,
when it goes to update the next row, it runs the correlated query a
second time. And this time it returns 3. So you end up setting "b" in
the second row to 3 instead of 2.
Something pretty similar is occurring in the example you posted.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
