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