Issue Overview: Ambiguity in ATTACH Database Precedence for Table Name Resolution

The core issue revolves around how SQLite resolves table name conflicts when multiple databases are attached using the ATTACH command. The documentation states that when a table with the same name exists in multiple attached databases, the table chosen is the one in the database that was "least recently attached." This phrasing has led to confusion about whether "least recently" refers to the temporal order of attachment (i.e., the earliest attached database) or a typographical error (e.g., "last" instead of "least").

SQLite’s schema search order for resolving unqualified table names (i.e., tables referenced without a database_name.table_name prefix) follows a strict hierarchy:

1. The temp database (schema for temporary objects).
2. The main database (the primary database opened with the connection).
3. Attached databases in the order they were attached.

The confusion arises from the term "least recently attached" in the documentation. In standard English, "least recently" often implies the action that occurred furthest in the past (e.g., "least recently used" caches). However, in SQLite’s context, this term is tied to the order of attachment, not the timing relative to the current moment. If Database A is attached first, followed by Database B, Database A is considered "least recently attached" because it was attached earlier in the sequence. This terminology clash creates ambiguity, especially when databases are detached and reattached dynamically.

For example, consider the following sequence:

• Attach Database A → Database B → Database C.
• Detach Database B.
• Reattach Database B.

In this scenario, the new attachment order is Database A → Database C → Database B. The term "least recently attached" now applies to Database A (first in the current attachment sequence), even though it was originally attached before Database B’s reattachment. The documentation’s wording does not account for detachment and reattachment dynamics, leading to further misinterpretation.

Possible Causes: Misinterpretation of Temporal Order vs. Attachment Sequence

The ambiguity stems from three interrelated factors:

1. Terminology Mismatch: The phrase "least recently attached" conflates temporal order (i.e., time since attachment) with logical sequence (order of attachment). In SQLite, the precedence of attached databases is determined solely by their attachment order, not the absolute time they were attached. If a database is detached and reattached, it is treated as a new entry in the sequence, effectively resetting its "attachment time."

2. Dynamic Attachment/Detachment Behavior: SQLite allows databases to be dynamically attached and detached during a session. This flexibility complicates the interpretation of "least recently attached" because a reattached database is prioritized over previously attached databases that have not been detached. For instance, if Database X is attached, detached, and then reattached, it becomes the "most recently attached" in the new sequence, even though it was originally attached earlier.

3. Lack of Explicit Documentation on Reattachment: The SQLite documentation does not explicitly address how detachment affects the attachment order. This omission creates uncertainty about whether the original attachment time or the latest attachment time determines precedence.

To illustrate, consider this example:

ATTACH 'db1.db' AS db1; -- Attached first (least recently in current sequence)  
ATTACH 'db2.db' AS db2; -- Attached second  
DETACH db1;  
ATTACH 'db1.db' AS db1; -- Reattached third (now most recently attached)  

In this case, the search order for tables would be temp → main → db2 → db1, even though db1 was originally attached before db2. The term "least recently attached" now refers to db2, which was attached before db1’s reattachment.

Troubleshooting Steps, Solutions & Fixes: Clarifying Precedence and Avoiding Ambiguity

To resolve confusion and ensure correct table name resolution, follow these steps:

Step 1: Understand the Actual Search Order

SQLite resolves unqualified table names using the following fixed hierarchy:

1. The temp schema.
2. The main schema.
3. Attached databases in the order they were attached, with the earliest attached database taking precedence over later ones.

This means that if two attached databases contain a table named tbl, the one in the database attached first will be accessed when querying tbl without a schema qualifier.

Verification Example:

ATTACH 'dbA.db' AS dbA;  
ATTACH 'dbB.db' AS dbB;  

If both dbA and dbB have a table tbl, querying SELECT * FROM tbl; will access dbA.tbl because dbA was attached first.

Step 2: Use Explicit Schema Qualifiers

To eliminate ambiguity, always qualify table names with their schema (database name):

SELECT * FROM dbX.tbl;  

This bypasses the search order entirely and ensures the correct table is accessed.

Step 3: Monitor Attachment Order Dynamically

SQLite provides the sqlite_schema (formerly sqlite_master) and pragma_database_list to inspect attachment order:

PRAGMA schema_list;  

This returns a list of attached databases in the order they were attached, including temp and main. The seq column indicates the attachment sequence, where lower values correspond to earlier attachments.

Example Output:

0|main|/path/to/main.db  
1|temp|  
2|dbA|/path/to/dbA.db  
3|dbB|/path/to/dbB.db  

Here, dbA (seq=2) takes precedence over dbB (seq=3).

Step 4: Handle Detachment and Reattachment Correctly

When a database is detached and reattached, it is added to the end of the attachment sequence. To reset the precedence order, detach and reattach databases in the desired sequence.

Example:

ATTACH 'db1.db' AS db1;  
ATTACH 'db2.db' AS db2;  
DETACH db1;  
ATTACH 'db1.db' AS db1; -- db1 is now after db2 in the sequence  

After reattachment, db2 has precedence over db1.

Step 5: Update Documentation Interpretation

Replace the phrase "least recently attached" with "attached earliest in the current sequence" to align with SQLite’s actual behavior. This avoids conflating temporal order with logical attachment sequence.

Step 6: Test Edge Cases with Reattachment

Create test scenarios to validate behavior:

Test Case 1: Basic Attachment Order

ATTACH 'db1.db' AS db1;  
ATTACH 'db2.db' AS db2;  
SELECT * FROM tbl; -- Accesses db1.tbl  

Test Case 2: Detach and Reattach

ATTACH 'db1.db' AS db1;  
ATTACH 'db2.db' AS db2;  
DETACH db1;  
ATTACH 'db1.db' AS db1;  
SELECT * FROM tbl; -- Accesses db2.tbl (db1 is now last)  

Step 7: Implement Best Practices

1. Avoid Unqualified Table Names: Always use database.table syntax.
2. Standardize Attachment Sequences: Attach databases in a consistent order if precedence matters.
3. Audit Schema Dependencies: Use PRAGMA schema_list to verify attachment order during development.

By following these steps, developers can eliminate ambiguity and ensure predictable table resolution in SQLite.