.expert Broken Error Message

(1.2) By SeverKetor on 2024-08-20 01:50:30 edited from 1.1 [link] [source]

If you use .expert on a query with an FTS index involving an ORDER BY clause, it fails and just says "Error: not an error". A wee bit contradictory I'd say.

Example: CREATE VIRTUAL TABLE f USING fts5(A); .expert SELECT * FROM f WHERE f MATCH 'a' ORDER BY A; Error: not an error .expert SELECT * FROM f WHERE f MATCH 'a'; Error: no such table: f

On a related note, why can't you use .expert for queries involving an FTS index? Is it because SQLite assumes virtual tables may have side effects and so skips them when running .expert to prevent potential issues? Or is it something else?

(2.1) By Stephan Beal (stephan) on 2024-10-12 18:07:40 edited from 2.0 in reply to 1.2 [link] [source]

Error: not an error ... On a related note, why can't you use .expert for queries involving an FTS index?

Dan explained to me that .expert is not capable of recommending indexes for virtual tables. (Edit: and then he started work on src:a201906cd3c85080f9b7!) We will look at improving that error message, though.

The string "not an error" comes from fetching the error string associated with SQLITE_OK (==0). The underlying error code is not bubbling up to that point for reasons i've not yet determined.

(3) By Stephan Beal (stephan) on 2024-10-13 22:26:19 in reply to 1.2 [link] [source]

On a related note, why can't you use .expert for queries involving an FTS index?

Please try that out with version src:43787b8ec5348207 or newer (i.e. current trunk) and report back if it's still failing for you. It "works for me," anyway (in that it no longer fails in the same way as in your report):

SQLite version 3.47.0 2024-10-12 19:33:47
...
sqlite> CREATE VIRTUAL TABLE f USING fts5(A);
sqlite> .expert
sqlite> SELECT * FROM f WHERE f MATCH 'a' ORDER BY A;
(no new indexes)

SCAN f VIRTUAL TABLE INDEX 0:M1
USE TEMP B-TREE FOR ORDER BY

sqlite> .expert
sqlite> SELECT * FROM f WHERE f MATCH 'a';
(no new indexes)

SCAN f VIRTUAL TABLE INDEX 0:M1

(4) By SeverKetor on 2024-10-14 00:23:46 in reply to 3 [link] [source]

I gave it a try and trivial tests (like what's in your post) work. However, in the actual DB, I instead get an error as soon as I use .expert.

sqlite3_expert_new: fts5: error creating shadow table fts_idx_data: table 'fts_idx_data' already exists

(5) By Stephan Beal (stephan) on 2024-10-14 01:16:43 in reply to 4 [link] [source]

However, in the actual DB, I instead get an error as soon as I use .expert.

But at least it's not "error: not an error," right? ;)

ReaLife(TM) has made our fts5 expert unavailable for a bit but i will ensure that he's made aware of this when he's freed up.

(6) By Dan Kennedy (dan) on 2024-10-19 19:03:09 in reply to 4 [link] [source]

Can you post a schema and query to reproduce this one?

Thanks.

Dan.

(7) By SeverKetor on 2024-10-19 20:13:24 in reply to 6 [source]

For the life of me I can't get a working reproduction a normal way. However, it persists through vacuums and .dumps of the DB in question. I'd say it's related to the ordering of the tables. In the dump, it creates the shadow tables first, then the adds the FTS index by directly inserting the SQL into sqlite_schema, which bypasses the attempt to create the shadow tables. Converting the INSERT INTO sqlite_schema statement into a regular CREATE statement causes a suspiciously similar error, "Runtime error: fts5: error creating shadow table fts_idx_data: table 'fts_idx_data' already exists"

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS 'fts_idx_data'(id INTEGER PRIMARY KEY, block BLOB);
INSERT INTO fts_idx_data VALUES(1,X'');
INSERT INTO fts_idx_data VALUES(10,X'00000000ff000001000000');
CREATE TABLE IF NOT EXISTS 'fts_idx_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'fts_idx_docsize'(id INTEGER PRIMARY KEY, sz BLOB, origin INTEGER);
CREATE TABLE IF NOT EXISTS 'fts_idx_config'(k PRIMARY KEY, v) WITHOUT ROWID;
INSERT INTO fts_idx_config VALUES('version',4);
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','fts_idx','fts_idx',0,'CREATE VIRTUAL TABLE fts_idx USING fts5(Title, Description, Channel, Tags, content='''', contentless_delete=1)');
PRAGMA writable_schema=OFF;
.expert
--sqlite3_expert_new: fts5: error creating shadow table fts_idx_data: table 'fts_idx_data' already exists

(8.1) By Dan Kennedy (dan) on 2024-10-20 07:21:50 edited from 8.0 in reply to 7 [link] [source]

I'd say it's related to the ordering of the tables.

Quite so. Thanks for reporting this. Hopefully now fixed here:

https://sqlite.org/src/info/7a716229

Dan.

(9) By SeverKetor on 2024-10-20 10:22:46 in reply to 8.1 [link] [source]

Yep, that did it. Thanks for the fix