Infinite loop when updating indexed with case and where clause

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

Infinite loop when updating indexed with case and where clause

Harald Klimach
Hi there,

here is a weird behavior I observe with the following kind of setup:

BEGIN TRANSACTION;
CREATE TABLE simple("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "lft" INTEGER, "rgt" INTEGER);
INSERT INTO simple VALUES(1,78,79);
CREATE INDEX "index_on_lft" ON "simple" ("lft");
CREATE INDEX "index_on_rgt" ON "simple" ("rgt");
COMMIT;

Now, if you try to run the following query, sqlite never returns:

UPDATE "simple" SET lft = CASE WHEN lft >= 36 THEN lft + 2 ELSE lft END WHERE (lft >= 36 OR rgt >= 36);

I encountered this when running Redmine (http://www.redmine.org).
When creating projects it uses this rails code:
http://www.redmine.org/projects/redmine/repository/entry/trunk/lib/redmine/nested_set/project_nested_set.rb#L48
def add_to_nested_set(lock=true)
  lock_nested_set if lock
  self.lft = target_lft
  self.rgt = lft + 1
  self.class.where("lft >= ? OR rgt >= ?", lft, lft).update_all([
    "lft = CASE WHEN lft >= :lft THEN lft + 2 ELSE lft END, " +
    "rgt = CASE WHEN rgt >= :lft THEN rgt + 2 ELSE rgt END",
    {:lft => lft}
  ])
end

Which actually results in the following query (that gets stuck):
SQL  UPDATE "projects" SET lft = CASE WHEN lft >= 36 THEN lft + 2 ELSE lft END, rgt = CASE WHEN rgt >= 36 THEN rgt + 2 ELSE rgt END WHERE (lft >= 36 OR rgt >= 36);

There is no problem when leaving out the where clause, which I did now as a workaround.
The above is the minimal example I found to reproduce this behavior.
I’ve tested it on OpenBSD 6.2 with SQLite version 3.20.1 2017-08-24 16:21:36,
on MacOSX 10.10.5 with SQLite version 3.21.0 2017-10-24 18:55:49
and on ArchLinux 4.14.12-1-ARCH with SQLite version 3.21.0 2017-10-24 18:55:49
(all x86_64).
The process consumes memory and cpu-cycles stuck in sqlite3VdbeExec.

Best regards,
Harald

_______________________________________________
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: Infinite loop when updating indexed with case and where clause

Richard Hipp-3
Thanks for the bug report!

A ticket for this issue is here:
https://www.sqlite.org/src/tktview/47b2581aa9bfececa7d95b2ef2aa433418c7a583

I will post another message as soon as we have it fixed.

On 1/16/18, Harald Klimach <[hidden email]> wrote:

> Hi there,
>
> here is a weird behavior I observe with the following kind of setup:
>
> BEGIN TRANSACTION;
> CREATE TABLE simple("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "lft"
> INTEGER, "rgt" INTEGER);
> INSERT INTO simple VALUES(1,78,79);
> CREATE INDEX "index_on_lft" ON "simple" ("lft");
> CREATE INDEX "index_on_rgt" ON "simple" ("rgt");
> COMMIT;
>
> Now, if you try to run the following query, sqlite never returns:
>
> UPDATE "simple" SET lft = CASE WHEN lft >= 36 THEN lft + 2 ELSE lft END
> WHERE (lft >= 36 OR rgt >= 36);
>
> I encountered this when running Redmine (http://www.redmine.org).
> When creating projects it uses this rails code:
> http://www.redmine.org/projects/redmine/repository/entry/trunk/lib/redmine/nested_set/project_nested_set.rb#L48
> def add_to_nested_set(lock=true)
>   lock_nested_set if lock
>   self.lft = target_lft
>   self.rgt = lft + 1
>   self.class.where("lft >= ? OR rgt >= ?", lft, lft).update_all([
>     "lft = CASE WHEN lft >= :lft THEN lft + 2 ELSE lft END, " +
>     "rgt = CASE WHEN rgt >= :lft THEN rgt + 2 ELSE rgt END",
>     {:lft => lft}
>   ])
> end
>
> Which actually results in the following query (that gets stuck):
> SQL  UPDATE "projects" SET lft = CASE WHEN lft >= 36 THEN lft + 2 ELSE lft
> END, rgt = CASE WHEN rgt >= 36 THEN rgt + 2 ELSE rgt END WHERE (lft >= 36 OR
> rgt >= 36);
>
> There is no problem when leaving out the where clause, which I did now as a
> workaround.
> The above is the minimal example I found to reproduce this behavior.
> I’ve tested it on OpenBSD 6.2 with SQLite version 3.20.1 2017-08-24
> 16:21:36,
> on MacOSX 10.10.5 with SQLite version 3.21.0 2017-10-24 18:55:49
> and on ArchLinux 4.14.12-1-ARCH with SQLite version 3.21.0 2017-10-24
> 18:55:49
> (all x86_64).
> The process consumes memory and cpu-cycles stuck in sqlite3VdbeExec.
>
> Best regards,
> Harald
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Infinite loop when updating indexed with case and where clause

Richard Hipp-3
In reply to this post by Harald Klimach
On 1/16/18, Harald Klimach <[hidden email]> wrote:
>
> here is a weird behavior I observe with the following kind of setup:
>

Bug fix is in.  You can download the latest snapshot from
https://sqlite.org/download.html.

Alternatively, you can apply the patch at
https://www.sqlite.org/src/info/feb2c2b6f66b0f45 to whatever prior
version of SQLite that you happen to be using.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Infinite loop when updating indexed with case and where clause

Harald Klimach
Wow,

> Bug fix is in.  You can download the latest snapshot from
> https://sqlite.org/download.html.
>
> Alternatively, you can apply the patch at
> https://www.sqlite.org/src/info/feb2c2b6f66b0f45 to whatever prior
> version of SQLite that you happen to be using.

thanks a lot for this quick response!

Harald


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

signature.asc (507 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Infinite loop when updating indexed with case and where clause

Don V Nielsen
In reply to this post by Richard Hipp-3
Off topic question regarding fix: "0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)"

Is there a performance bonus or compiler optimization if one compares a
target constant to a source condition versus comparing a target condition
to a source constant, as in "(wctrlFlags & WHERE_ONEPASS_MULTIROW)!=0"?

The only reason I ask is that it is the opposite of how I code (in any
language) and how expect to read things.

Just curious
dvn

On Tue, Jan 16, 2018 at 7:59 AM, Richard Hipp <[hidden email]> wrote:

> On 1/16/18, Harald Klimach <[hidden email]> wrote:
> >
> > here is a weird behavior I observe with the following kind of setup:
> >
>
> Bug fix is in.  You can download the latest snapshot from
> https://sqlite.org/download.html.
>
> Alternatively, you can apply the patch at
> https://www.sqlite.org/src/info/feb2c2b6f66b0f45 to whatever prior
> version of SQLite that you happen to be using.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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: Infinite loop when updating indexed with case and where clause

Richard Hipp-3
On 1/16/18, Don V Nielsen <[hidden email]> wrote:
> Off topic question regarding fix: "0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)"
>
> Is there a performance bonus or compiler optimization if one compares a
> target constant to a source condition versus comparing a target condition
> to a source constant, as in "(wctrlFlags & WHERE_ONEPASS_MULTIROW)!=0"?

Without actually checking, I'm guessing that both forms generating
identical machine code.  The difference is purely a stylistic thing.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Infinite loop when updating indexed with case and where clause

Peter da Silva
In reply to this post by Don V Nielsen
On 1/16/18, 8:12 AM, "sqlite-users on behalf of Don V Nielsen" <[hidden email] on behalf of [hidden email]> wrote:
> Off topic question regarding fix: "0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)"

> Is there a performance bonus or compiler optimization if one compares a target constant to a source condition versus comparing a target condition to a source constant, as in "(wctrlFlags & WHERE_ONEPASS_MULTIROW)!=0"?
   
> The only reason I ask is that it is the opposite of how I code (in any language) and how expect to read things.

I’ve seen this style increasingly often the last ten or fifteen years, the idea is that by putting the constant on the left side of a comparison it’s harder to accidentally typo it into an assignment.
 

_______________________________________________
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: Infinite loop when updating indexed with case and where clause

Andreas Kupries
In reply to this post by Richard Hipp-3

> On 1/16/18, Don V Nielsen <[hidden email]> wrote:
> > Off topic question regarding fix: "0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)"
> >
> > Is there a performance bonus or compiler optimization if one compares a
> > target constant to a source condition versus comparing a target condition
> > to a source constant, as in "(wctrlFlags & WHERE_ONEPASS_MULTIROW)!=0"?
>
> Without actually checking, I'm guessing that both forms generating
> identical machine code.  The difference is purely a stylistic thing.

The difference is when the operator is == and you make a typo, i.e. == vs =.

    0 = foo    from 0 == foo
vs  foo = 0    from foo == 0

The first becomes a quickly fixed syntax error, the second an
assignment, a not so easily seen bug.

--
See you,
        Andreas Kupries <[hidden email]>
                        <http://core.tcl.tk/akupries/>
        Developer @ SUSE (MicroFocus Canada LLC)
                  <[hidden email]>
-------------------------------------------------------------------------------




_______________________________________________
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: Infinite loop when updating indexed with case and where clause

Christian Höhne
In reply to this post by Harald Klimach
Please unsubscribe me.

Christian Hoehne

Il 16 gen 2018 13:34, "Harald Klimach" <[hidden email]> ha scritto:

> Hi there,
>
> here is a weird behavior I observe with the following kind of setup:
>
> BEGIN TRANSACTION;
> CREATE TABLE simple("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "lft"
> INTEGER, "rgt" INTEGER);
> INSERT INTO simple VALUES(1,78,79);
> CREATE INDEX "index_on_lft" ON "simple" ("lft");
> CREATE INDEX "index_on_rgt" ON "simple" ("rgt");
> COMMIT;
>
> Now, if you try to run the following query, sqlite never returns:
>
> UPDATE "simple" SET lft = CASE WHEN lft >= 36 THEN lft + 2 ELSE lft END
> WHERE (lft >= 36 OR rgt >= 36);
>
> I encountered this when running Redmine (http://www.redmine.org).
> When creating projects it uses this rails code:
> http://www.redmine.org/projects/redmine/repository/
> entry/trunk/lib/redmine/nested_set/project_nested_set.rb#L48
> def add_to_nested_set(lock=true)
>   lock_nested_set if lock
>   self.lft = target_lft
>   self.rgt = lft + 1
>   self.class.where("lft >= ? OR rgt >= ?", lft, lft).update_all([
>     "lft = CASE WHEN lft >= :lft THEN lft + 2 ELSE lft END, " +
>     "rgt = CASE WHEN rgt >= :lft THEN rgt + 2 ELSE rgt END",
>     {:lft => lft}
>   ])
> end
>
> Which actually results in the following query (that gets stuck):
> SQL  UPDATE "projects" SET lft = CASE WHEN lft >= 36 THEN lft + 2 ELSE lft
> END, rgt = CASE WHEN rgt >= 36 THEN rgt + 2 ELSE rgt END WHERE (lft >= 36
> OR rgt >= 36);
>
> There is no problem when leaving out the where clause, which I did now as
> a workaround.
> The above is the minimal example I found to reproduce this behavior.
> I’ve tested it on OpenBSD 6.2 with SQLite version 3.20.1 2017-08-24
> 16:21:36,
> on MacOSX 10.10.5 with SQLite version 3.21.0 2017-10-24 18:55:49
> and on ArchLinux 4.14.12-1-ARCH with SQLite version 3.21.0 2017-10-24
> 18:55:49
> (all x86_64).
> The process consumes memory and cpu-cycles stuck in sqlite3VdbeExec.
>
> Best regards,
> Harald
>
> _______________________________________________
> 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: Infinite loop when updating indexed with case and where clause

Simon Slavin-3


On 17 Jan 2018, at 8:40am, Christian Höhne <[hidden email]> wrote:

> Please unsubscribe me.

This list is run by computer.  At the bottom of every post, including this one, is a link which will allow you to unsubscribe yourself.

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