Hello sqlite-users,
Apologies if this isn’t formatted correctly, I’m not used to submitting bug
reports via mailing lists.
Although I’m sure some people will be shaking their head, I chose to use
periods between words in table names. I’ve been careful to escape table names
everywhere, so this has worked out fine for the most part. However, there is an
issue when attempting to search an FTS5 table with ORDER BY rank. From the
below example:
SELECT * FROM "My.Table" WHERE Text MATCH 'table' ORDER BY rank; -- BUG: near
"Table": syntax error
As the comment indicates, this produces the message “near ‘Table’: syntax
error”. My armchair debugging skills are telling me that it seems to be an
issue with the table name not being properly escaped. It’s my understanding
that using ORDER BY bm25(`My.Table`) should be functionally identical to ORDER
BY rank:
SELECT * FROM "My.Table" WHERE Text MATCH 'table' ORDER BY bm25(`My.Table`);
-- Works fine
Full example below (Ctrl+F “BUG” for the line that produces the error)
-- Create table
CREATE VIRTUAL TABLE "My.Table" USING fts5(Text);
-- Insert some data
INSERT INTO "My.Table" VALUES ('hello this is a test');
INSERT INTO "My.Table" VALUES ('of trying to order by');
INSERT INTO "My.Table" VALUES ('rank on an fts5 table');
INSERT INTO "My.Table" VALUES ('that have periods in');
INSERT INTO "My.Table" VALUES ('the table names.');
INSERT INTO "My.Table" VALUES ('table table table');
-- Search FTS table - works fine
SELECT * FROM "My.Table" WHERE Text MATCH 'table';
-- Search FTS table with ordering
SELECT * FROM "My.Table" WHERE Text MATCH 'table' ORDER BY bm25(`My.Table`);
-- Works fine
SELECT * FROM "My.Table" WHERE Text MATCH 'table' ORDER BY rank; -- BUG: near
"Table": syntax error
-- Change the table name to remove the period
ALTER TABLE "My.Table" RENAME TO "My_Table";
-- Search FTS table - all of these work perfectly now
SELECT * FROM "My_Table" WHERE Text MATCH 'table';
SELECT * FROM "My_Table" WHERE Text MATCH 'table' ORDER BY rank;
SELECT * FROM "My_Table" WHERE Text MATCH 'table' ORDER BY bm25(`My_Table`);
Thanks,
Matt
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
