FTS5 Index and .expert Command: Error Handling and Shadow Table Conflicts

FTS5 Virtual Tables and .expert Command Limitations

The core issue revolves around the interaction between SQLite’s .expert command and Full-Text Search 5 (FTS5) virtual tables. The .expert command is designed to analyze SQL queries and recommend indexes that could improve query performance. However, when applied to queries involving FTS5 virtual tables, it exhibits unexpected behavior, including cryptic error messages and conflicts with shadow tables.

The primary symptom is the error message "Error: not an error," which occurs when attempting to use .expert on a query that includes an ORDER BY clause with an FTS5 virtual table. This error message is misleading and stems from the underlying error code SQLITE_OK (which signifies no error) being incorrectly interpreted. Additionally, there are cases where .expert fails outright when dealing with FTS5 virtual tables, particularly when shadow tables are involved, leading to errors like "fts5: error creating shadow table fts_idx_data: table ‘fts_idx_data’ already exists."

The root cause of these issues lies in the way .expert interacts with virtual tables. Virtual tables, such as those created using FTS5, are not traditional SQLite tables. They are implemented through a set of user-defined functions and can have side effects or require special handling. The .expert command, as originally designed, does not account for these nuances, leading to incomplete or incorrect analysis of queries involving virtual tables.

Shadow Table Creation and Schema Ordering Conflicts

A significant part of the problem is related to the creation and management of shadow tables in FTS5. Shadow tables are internal tables used by FTS5 to store auxiliary data, such as indexes and document sizes. When an FTS5 virtual table is created, SQLite automatically generates these shadow tables. However, the .expert command attempts to create its own set of shadow tables during its analysis, leading to conflicts when these tables already exist.

The issue is exacerbated by the order in which tables are created in the database schema. In some cases, the shadow tables are created before the FTS5 virtual table itself, either through direct SQL statements or as part of a database dump. This ordering can cause .expert to fail when it tries to create shadow tables that already exist, resulting in errors like "fts5: error creating shadow table fts_idx_data: table ‘fts_idx_data’ already exists."

The problem is further complicated by the use of the PRAGMA writable_schema directive, which allows direct modification of the sqlite_schema table. When an FTS5 virtual table is created by inserting a row into sqlite_schema, it bypasses the normal table creation process, including the creation of shadow tables. This can lead to inconsistencies and errors when .expert attempts to analyze the query.

Resolving Shadow Table Conflicts and Improving .expert Behavior

To address these issues, several steps can be taken. First, the .expert command should be updated to handle virtual tables more gracefully. This includes recognizing when a table is virtual and skipping the creation of shadow tables if they already exist. Additionally, the error handling for .expert should be improved to provide more informative error messages, rather than the cryptic "Error: not an error."

For users encountering the "fts5: error creating shadow table fts_idx_data: table ‘fts_idx_data’ already exists" error, a workaround is to ensure that the FTS5 virtual table is created before any shadow tables. This can be achieved by modifying the database schema to create the virtual table first, followed by any necessary shadow tables. Alternatively, users can avoid using PRAGMA writable_schema to insert rows into sqlite_schema and instead use standard CREATE statements for FTS5 virtual tables.

In cases where the issue persists, updating to the latest version of SQLite is recommended. The SQLite development team has addressed some of these issues in recent updates, including improvements to the .expert command’s handling of FTS5 virtual tables. For example, version 3.47.0 and newer include fixes that prevent the "Error: not an error" message and improve the overall stability of .expert when used with FTS5.

For developers working with FTS5 and .expert, it is important to be aware of these limitations and plan accordingly. This includes testing queries thoroughly and being prepared to handle edge cases where .expert may not provide useful recommendations. Additionally, developers should monitor the SQLite changelog for updates and improvements related to FTS5 and .expert, as the SQLite team continues to refine these features.

In summary, the interaction between FTS5 virtual tables and the .expert command in SQLite can lead to confusing error messages and conflicts with shadow tables. By understanding the underlying causes and applying the appropriate fixes, users can mitigate these issues and make more effective use of both FTS5 and .expert in their SQLite databases.