Duplicate Rows in WITHOUT ROWID Table When Using SQLiteDataReader

Schema Conversion Artifacts & Data Retrieval Anomalies

The core issue revolves around unexpected duplicate rows appearing during data retrieval via SQLiteDataReader after converting a standard SQLite table to a WITHOUT ROWID configuration. This behavior manifests specifically around gaps in the primary key sequence and disappears when reverting to a ROWID-based table. The anomaly is not reproducible in SQLite database browsers, indicating a disconnect between the storage engine’s behavior and the .NET data provider’s interpretation of the physical data organization.

Structural Implications of WITHOUT ROWID Conversion

SQLite’s WITHOUT ROWID optimization fundamentally alters the storage mechanics of a table. In standard tables, the implicit ROWID column serves as the primary key for the B-tree that stores the table’s data. When a table is converted to WITHOUT ROWID, the primary key column(s) replace ROWID as the clustering key for the B-tree. This means the physical order of rows is dictated by the primary key’s collation and sorting logic. Gaps in the primary key sequence (e.g., deleted rows with player_id 5 and 6 missing) do not inherently cause duplication in properly structured tables. However, discrepancies in how the storage engine organizes data versus how client libraries traverse the B-tree can surface under specific conditions.

The System.Data.SQLite provider interacts with the SQLite C API through prepared statements and cursor-based navigation. When a WITHOUT ROWID table’s primary key has gaps, the B-tree’s traversal logic might expose edge cases in the provider’s implementation, especially if the provider makes assumptions about row ordering or uniqueness that are violated by the storage engine’s behavior. For example, if the provider uses ROWID-based pagination internally (despite the table not exposing ROWID), converting to WITHOUT ROWID could disrupt these assumptions, leading to repeated fetches of the same logical row.

Additionally, the absence of ROWID changes how SQLite handles internal cursors. In standard tables, ROWID provides a stable, unique identifier for each row, which cursors use to track their position. In WITHOUT ROWID tables, cursors rely entirely on the primary key. If the primary key has gaps, cursor movement operations (e.g., stepping through rows via sqlite3_step) might behave differently, particularly if the client library does not account for the lack of a ROWID anchor. This could explain why the SQLiteDataReader encounters duplicates—its internal cursor management might be skipping or revisiting rows due to misaligned position tracking.

Provider-Specific Cursor Handling & Index Corruption Suspicions

The duplication anomaly could stem from three interrelated factors: improper cursor management in the System.Data.SQLite provider, undetected index corruption, or residual artifacts from schema conversion.

Cursor Management in System.Data.SQLite
The SQLiteDataReader class wraps the SQLite C API’s sqlite3_stmt object, which manages the cursor state during query execution. When iterating over a WITHOUT ROWID table, the cursor advances based on the primary key’s order. If the provider’s implementation of Read() does not fully respect the WITHOUT ROWID table’s physical organization (e.g., using ROWID-based heuristics internally), it might misnavigate the B-tree, causing rows to be revisited. This is exacerbated by primary key gaps, which can create discontinuities in the cursor’s progression. For instance, after fetching player_id 4, the cursor might attempt to seek to the next ROWID (which doesn’t exist), leading to repositioning errors that inadvertently re-fetch row 7.

Index Corruption
Although the user reports that SQLite Expert and SQLite Studio display correct results, index corruption could still manifest selectively. WITHOUT ROWID tables store all data in a single B-tree structured by the primary key. If this B-tree develops logical inconsistencies (e.g., duplicate entries for the same primary key), the SQLite engine itself would typically report errors during integrity checks. However, if the corruption is subtle—such as overlapping pages or misaligned pointers—it might only surface under specific access patterns, such as those employed by the System.Data.SQLite provider. Running PRAGMA integrity_check; is critical to rule out this possibility.

Schema Conversion Artifacts
Converting an existing table to WITHOUT ROWID requires creating a new table, copying data, and dropping the original. This process can leave behind residual artifacts if not executed atomically. For example, if the conversion tool (SQLite Expert) does not properly handle indexes or triggers during the migration, the resulting table might reference outdated metadata. The System.Data.SQLite provider, which parses schema details at runtime, could then misinterpret the table’s structure, leading to incorrect data retrieval.

Diagnostic Procedures & Corrective Actions

To resolve the duplication issue, a systematic approach involving schema validation, provider behavior analysis, and low-level diagnostics is required.

Step 1: Validate Schema Integrity
Begin by confirming the table’s schema and integrity:

1. Verify Table Definition: Execute .schema DYN_joueur in the SQLite CLI to ensure the WITHOUT ROWID clause is correctly applied and no residual triggers or indexes reference the old structure.
2. Run Integrity Check: Execute PRAGMA quick_check; followed by PRAGMA integrity_check; to detect B-tree or index corruption. If errors are found, rebuild the database using .dump and .read.
3. Inspect Primary Key Uniqueness: Query SELECT player_id, COUNT(*) FROM DYN_joueur GROUP BY player_id HAVING COUNT(*) > 1; to confirm no logical duplicates exist.

Step 2: Isolate the Provider-Specific Behavior

1. Reproduce with Different Tools: Use the SQLite CLI to execute the same query (SELECT * FROM DYN_joueur;) and compare the output with the System.Data.SQLite results. Redirect the CLI output to a file and diff it against the .NET application’s output.
2. Update System.Data.SQLite: Ensure the latest version of the provider is used, as older versions might have bugs in WITHOUT ROWID table handling.
3. Test with Alternative Providers: Use Microsoft.Data.Sqlite (the newer, lighter-weight provider) to determine if the issue persists across libraries.

Step 3: Analyze Cursor Behavior

1. Enable Tracing: Configure SQLite to log cursor operations by compiling a custom build with SQLITE_DEBUG and SQLITE_ENABLE_SELECTTRACE. This will log B-tree traversal steps, revealing whether the cursor is revisiting rows.
2. Inspect Query Plans: Execute EXPLAIN SELECT * FROM DYN_joueur; in both ROWID and WITHOUT ROWID configurations. Compare the bytecode to identify differences in data access patterns.

Step 4: Address Primary Key Gaps
While gaps in the primary key are permissible, they can exacerbate cursor navigation issues in buggy providers. Consider:

1. Reassigning player_id Values: If application logic allows, update existing rows to eliminate gaps using UPDATE DYN_joueur SET player_id = rowid WHERE player_id IN (4,7,8); after temporarily disabling WITHOUT ROWID.
2. Using AUTOINCREMENT: Add AUTOINCREMENT to the primary key definition to enforce monotonic allocation, though this does not eliminate existing gaps.

Step 5: Rebuild the Table Atomically
If conversion artifacts are suspected, manually rebuild the table:

BEGIN TRANSACTION;
CREATE TABLE temp_DYN_joueur ([player_id] INTEGER PRIMARY KEY NOT NULL, [joueur_nom] nvarchar(40), [joueur_prenom] nvarchar(40)) WITHOUT ROWID;
INSERT INTO temp_DYN_joueur SELECT * FROM DYN_joueur ORDER BY player_id;
DROP TABLE DYN_joueur;
ALTER TABLE temp_DYN_joueur RENAME TO DYN_joueur;
COMMIT;

This ensures a clean B-tree structure without residual metadata.

Step 6: Modify .NET Data Retrieval Logic
The nested SQLiteCommand inside the reader loop is unnecessary and could cause unintended side effects. Refactor the code to decouple data reading from updates:

var playerIds = new List<int>();
using (var command = source.CreateCommand()) 
{
    command.CommandText = "SELECT player_id FROM DYN_joueur";
    using (var reader = command.ExecuteReader()) 
    {
        while (reader.Read()) 
        {
            int id = reader.GetInt32(0);
            playerIds.Add(id);
        }
    }
}
foreach (int id in playerIds) 
{
    using (var commandUpdate = source.CreateCommand()) 
    {
        // Perform updates using the pre-fetched IDs
    }
}

This eliminates concurrent command execution within the reader loop, reducing the risk of cursor interference.

Step 7: Utilize Lower-Level Debugging Tools

1. Database File Analysis: Use sqlite3_analyzer to inspect the B-tree structure of the DYN_joueur table. Look for anomalies in page utilization or duplicate keys.
2. Memory Dump Inspection: Attach a debugger to the .NET process and inspect the memory regions holding the SQLiteDataReader’s internal state during iteration. Check for incorrect row caching or cursor position values.

Final Consideration: WITHOUT ROWID Trade-Offs
While WITHOUT ROWID tables optimize storage and performance for certain workloads, they impose stricter requirements on primary key design and client library compatibility. If provider-specific issues persist, reverting to a ROWID table with proper indexing might be a pragmatic workaround until the underlying cause is resolved.