BUG: UPDATE with correlated sub-query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG: UPDATE with correlated sub-query

Dudu Markovitz
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.

Thanks

Dudu Markovitz

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

bug_report.sql (2K) Download Attachment
bug_report.txt (7K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: BUG: UPDATE with correlated sub-query

Dan Kennedy-4
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BUG: UPDATE with correlated sub-query

Dudu Markovitz
Thanks Dan

I couldn't find a reference to this behaviour in the documentation.
Do you think it would be possible to add a few words?




On Mon, Feb 13, 2017 at 7:49 PM Dan Kennedy <[hidden email]> wrote:

> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BUG: UPDATE with correlated sub-query

James K. Lowden
In reply to this post by Dan Kennedy-4
On Tue, 14 Feb 2017 00:49:29 +0700
Dan Kennedy <[hidden email]> wrote:

> 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.

Thank you for that distillation, Dan.  You saved me the effort of
understanding the problem report.  

> It's surprising, but a consequence of the way SQLite has always
> worked.

For some value of "worked", yes.  It's another example of the
ramifications of SQLite's nonatomic update.  

Your explanation leaves out fundamental step 0: First, [dbms] isolates
the UPDATE statement, such that no changes made to the database affect
the statement's input during execution.  Your representation "runs the
correlated query a second time" is the correct way to think about it
logically *if* you treat the table as stable during the query's
execution.  

SQLite's nonatomic update deserves its own documentation page.  Users
who understand atomicity correctly recognize it as a bug, both because
it's not how SQL defines UPDATE and because it's not documented.
Not to put too fine a point on it, the claim "Transactions in SQLite
are SERIALIZABLE" is false because -- as you just explained -- the
update transaction is not isolated from *itself*.  

--jkl


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users