SQLite .expert Mode Error: “Error: not an error” Due to Index Name Collision

Issue Overview: Understanding the ".expert" Mode Error When Reusing Suggested Index Names

The SQLite command-line interface (CLI) includes a utility mode called .expert, designed to suggest indexes that could optimize query performance. This feature analyzes a given query and recommends indexes that might reduce execution time. However, a specific edge case arises when a user creates an index with the same name as the one suggested by .expert but with a different column structure. When this occurs, subsequent uses of .expert for the same query result in the cryptic error message: "Error: not an error".

This issue is rooted in how the .expert mode generates index names and validates existing indexes. The tool uses a hashing mechanism to create unique index names, assuming that the generated name will not collide with existing indexes. However, if a user manually creates an index with the same name as the suggested one but a different column order or subset, .expert fails to handle the mismatch gracefully. Instead of detecting the structural discrepancy, the tool encounters an internal inconsistency, leading to the nonsensical error message.

The problem is particularly insidious because it combines two factors:

1. Index Name Reuse: The user leverages the convenience of reusing the suggested index name (e.g., t_idx_00012959) to avoid typing a new name, inadvertently creating a structurally different index.
2. Insufficient Validation: The .expert mode checks for the existence of an index by name but does not verify whether the existing index matches the structure it would recommend.

This combination results in a broken state where the CLI cannot proceed with its analysis, triggering an error that provides no actionable information. The error message itself, "Error: not an error," is a placeholder that fails to communicate the root cause, leaving users confused and unable to resolve the issue without external guidance.

Possible Causes: Why Index Name Collisions and Structural Mismatches Trigger Errors

1. Index Name Generation Algorithm Limitations

The .expert mode generates index names using a deterministic hash of the proposed index’s column list and table name. For example, an index on columns (c, b) for table t might generate a name like t_idx_00012959, where the suffix is derived from a hash of the column list. The algorithm assumes that this hash is sufficiently unique to prevent name collisions. However, this assumption breaks down when a user manually creates an index with the same name but a different column list. The tool does not account for pre-existing indexes that share the same name but have different structures.

2. Lack of Structural Validation for Existing Indexes

When .expert is invoked, it first checks whether the suggested index already exists. However, this check is performed by name only, not by structure. If an index with the suggested name exists but has a different column order or subset, the tool incorrectly assumes that the index is valid for the query. This leads to a contradiction: the query planner cannot use the existing index (because it is structurally inadequate), but .expert has already "reserved" the name. The internal logic of .expert then encounters an unexpected state, resulting in the unhelpful error message.

3. Inadequate Error Handling for Edge Cases

The error message "Error: not an error" is a default response that arises when the .expert mode encounters an unexpected condition but lacks a specific error handler for that scenario. This message is generated by the sqlite3_error_offset() function, which returns a generic error code (SQLITE_ERROR) without a corresponding descriptive message. The absence of context-aware error reporting exacerbates the problem, as users cannot discern whether the issue is due to a syntax error, a missing table, or an index mismatch.

Troubleshooting Steps, Solutions & Fixes: Resolving Index Name Collisions and Improving Query Analysis

1. Upgrade to a Fixed Version of SQLite

The SQLite development team addressed this issue in commit 4a3f98ad0dd2a8c0. Starting with this revision, the .expert mode performs two critical checks:

• It verifies whether an index with the suggested name already exists.
• If the index exists, it compares the index’s column list and collation sequence against the proposed index.

If a structural mismatch is detected, the tool now generates a new index name with an incremented hash suffix (e.g., t_idx_00012959_2) to avoid collisions. If no unique name can be found after multiple attempts, it outputs the clear error message: "Cannot find a unique index name to propose."

Actionable Steps:

• Download the latest SQLite trunk build or wait for the next official release (version 3.37.0 or newer).
• Replace the existing sqlite3 CLI executable with the updated version.

2. Avoid Reusing Suggested Index Names

While the convenience of reusing index names from .expert output is tempting, doing so risks creating structural mismatches. Instead, adopt a naming convention that incorporates the column list or purpose of the index. For example:

CREATE INDEX t_idx_b_c ON t(b, c);  -- Descriptive name

This practice eliminates the risk of name collisions and makes the schema more maintainable.

3. Manually Verify Existing Indexes

If you encounter the "Error: not an error" message in an older SQLite version, manually inspect the indexes on the involved tables:

PRAGMA index_list(t);  -- List all indexes for table 't'
PRAGMA index_info(t_idx_00012959);  -- Show columns in index 't_idx_00012959'

Compare the output of index_info with the columns suggested by .expert. If they differ, drop the conflicting index and rerun .expert:

DROP INDEX t_idx_00012959;
.expert
SELECT * FROM t AS t1 JOIN t AS t2 USING (b, c);

4. Use Custom Index Names in .expert Mode

If you must reuse a name, append a version suffix or timestamp to ensure uniqueness:

CREATE INDEX t_idx_00012959_v2 ON t(c, b);  -- Custom suffix

This approach retains the convenience of name reuse while avoiding conflicts.

5. Leverage the Query Planner Directly

Bypass .expert and use EXPLAIN QUERY PLAN to analyze how the query planner utilizes existing indexes:

EXPLAIN QUERY PLAN
SELECT * FROM t AS t1 JOIN t AS t2 USING (b, c);

The output will indicate whether the existing index (t_idx_00012959) is being used and how. If the index is ignored or misapplied, consider altering or replacing it.

6. Implement Collation-Aware Indexes

As noted in the discussion, .expert may recommend COLLATE NOCASE indexes for LIKE queries. Manually create such indexes if performance issues persist:

CREATE INDEX t_idx_b_nocase ON t(b COLLATE NOCASE);

This ensures case-insensitive comparisons are optimized, aligning with the query planner’s expectations.

By following these steps, users can resolve index name collisions, improve query performance, and avoid the pitfalls of the ".expert" mode’s historical limitations. The key takeaway is to treat .expert as a advisory tool rather than a fully automated solution, combining its suggestions with manual validation and schema best practices.