Thank you so much. I'm always impressed by the responsiveness of the SQLite team. Very grateful for your help and hard work.
On Wed, May 2, 2018 at 3:13 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 05/02/2018 03:50 AM, Charles Leifer wrote: > >> Hi all, >> >> I'm noticing a bug in the transitive closure extension in the latest >> version of SQLite. >> > > Thanks for reporting this. Should now be fixed here: > > http://www.sqlite.org/src/info/0c67150749cb3d06 > > Dan. > > > > >> Reproducing requires the closure extension, which I compiled: >> >> gcc -g -fPIC -shared -lsqlite3 closure.c -o closure.so >> >> SQL to populate db: >> >> .load closure >> -- create category table with self-referential parent fk. >> CREATE TABLE "category" ("id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL, >> "parent_id" INTEGER, FOREIGN KEY ("parent_id") REFERENCES "category" >> ("id")); >> CREATE INDEX IF NOT EXISTS "category_parent_id" ON "category" >> ("parent_id"); >> >> -- create closure table vtable >> CREATE VIRTUAL TABLE IF NOT EXISTS "categoryclosure" USING >> transitive_closure (idcolumn=id, parentcolumn=parent_id, >> tablename=category); >> >> -- populate some data for a book catalog >> INSERT INTO "category" ("name", "parent_id") VALUES ('books', NULL); -- 1 >> INSERT INTO "category" ("name", "parent_id") VALUES ('fiction', 1); -- 2 >> INSERT INTO "category" ("name", "parent_id") VALUES ('scifi', 2); -- 3 >> INSERT INTO "category" ("name", "parent_id") VALUES ('hard scifi', 3); -- >> 4 >> INSERT INTO "category" ("name", "parent_id") VALUES ('dystopian', 3); -- 5 >> >> -- get hard scifi and all its parents: >> SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth" >> FROM "category" AS "t1" >> INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root") >> WHERE ("t2"."id" = 4); >> >> -- results: >> -- 1|books||3 >> -- 2|fiction|1|2 >> -- 3|scifi|2|1 >> -- 4|hard scifi|3|0 >> >> -- get relations of "hard scifi" specifying depth > 0. >> SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth" >> FROM "category" AS "t1" >> INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root") >> WHERE (("t2"."id" = 4) AND ("t2"."depth" > 0)); >> >> -- results: >> -- 1|books||3 >> -- 2|fiction|1|2 >> -- 3|scifi|2|1 >> >> >> -- THIS BREAKS with: "Error: categoryclosure.xBestIndex malfunction" >> -- get grand-parent category for hard-scifi (depth=2) >> SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth" >> FROM "category" AS "t1" >> INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root") >> WHERE (("t2"."id" = 4) AND ("t2"."depth" = 2)); >> >> It seems that when we query for equality on the categoryclosure id *and* >> depth, xBestIndex runs into trouble? >> >> Thanks, >> >> Charles Leifer >> _______________________________________________ >> 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
