[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] [FTS5] Potential table name escape issue with ORDER BY rank
From:       Dan Kennedy <danielk1977 () gmail ! com>
Date:       2019-08-20 19:12:38
Message-ID: 818bcf7b-e278-6842-4fd4-dbcecd6b9dcf () gmail ! com
[Download RAW message or body]


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


[prev in list] [next in list] [prev in thread] [next in thread]