[FTS5] Potential table name escape issue with ORDER BY rank

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

[FTS5] Potential table name escape issue with ORDER BY rank

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

Re: [FTS5] Potential table name escape issue with ORDER BY rank

Dan Kennedy-4

On 21/8/62 01:06, Matt Haynie wrote:
> 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


Thanks for the thorough bug report. Now fixed here:

   https://sqlite.org/src/info/00e9a8f2730eb723

Dan.


>
> 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
> [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