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