Re: [sqlite] Bug in transitive closure extension?

2018-05-02 Thread 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  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

Re: [sqlite] Bug in transitive closure extension?

2018-05-02 Thread Dan Kennedy 

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] Bug in transitive closure extension?

2018-05-01 Thread 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users