Hi all,
I'm noticing a bug in the transitive closure extension in the latest
version of SQLite.
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
