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 <har...@klimachs.de> 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
