Bug in transitive closure extension?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug in transitive closure extension?

Charles Leifer
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in transitive closure extension?

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


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in transitive closure extension?

Charles Leifer
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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users