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

[Simon Slavin]

On 17 Jan 2018, at 8:40am, Christian Höhne  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Christian Höhne]

Please unsubscribe me.

Christian Hoehne

Il 16 gen 2018 13:34, "Harald Klimach"  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Andreas Kupries]

> On 1/16/18, Don V Nielsen  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 = foofrom 0 == foo
vs  foo = 0from foo == 0

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

-- 
See you,
Andreas Kupries 

Developer @ SUSE (MicroFocus Canada LLC)

---




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter Da Silva]

On 1/16/18, 8:12 AM, "sqlite-users on behalf of Don V Nielsen" 
 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On 1/16/18, Don V Nielsen  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Don V Nielsen]

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  wrote:

> On 1/16/18, Harald Klimach  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
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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



signature.asc
Description: Message signed with OpenPGP using GPGMail
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On 1/16/18, Harald Klimach  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users