CSV Virtual Table Fails to Parse Rows with Leading or Trailing Empty Fields

Header Parsing Succeeds but Data Rows Are Skipped When Fields Are Empty

The core issue revolves around the SQLite CSV virtual table module (csv.c) and its inability to correctly parse CSV data when rows contain empty fields in specific positions. Two distinct failure modes were identified:

1. Leading Empty Field in Data Row: When the first field of the first data row is empty (e.g., ,2,3 after the header), the CSV virtual table parses only the header row and skips subsequent data rows entirely. The parser incorrectly interprets the empty field as an unrecoverable error, leading to premature termination of data ingestion.
2. Trailing Empty Field with Missing Record Separator: When a data row ends with an empty field (e.g., 1,2, with no newline after the final comma), the CSV virtual table again parses only the header row. This occurs even though RFC4180 permits CSV files to omit the final record separator (e.g., newline). The parser expects a record separator to finalize the row and discards incomplete data.

These issues manifest in SQLite versions prior to specific fixes applied in July 2022 (check-in b12ddabf07b5e06b and 587795d47fcaf514). Misparsing leads to incomplete query results, as the virtual table returns only the header or partial data.

Root Causes: Buffer Allocation Errors and RFC4180 Compliance Gaps

Leading Empty Field: Buffer Initialization Failure

The CSV virtual table’s parser uses a dynamic buffer to accumulate field values. When parsing begins, the buffer is uninitialized. If the first character of the first data field is a delimiter (e.g., a comma), the parser attempts to read zero bytes into the unallocated buffer. This triggers a false memory allocation error (p->z==0 && p->nAlloc==0), causing the parser to abort row processing and return EOF prematurely. The error state propagates upward, leading the virtual table to believe no data rows exist.

Trailing Empty Field: Missing Record Separator Handling

The CSV parser enforces strict adherence to field and record separators. A data row ending with a field separator (,) but lacking a record separator (\n) is considered malformed. The parser expects a newline to finalize the row. Without it, the trailing empty field is left in a "pending" state, and the parser discards the row entirely. This violates RFC4180 Section 2, which states:

The last record in the file may or may not have an ending line break.

The parser’s strictness stems from its internal state machine, which transitions between fields and records based on separators. A missing record separator leaves the parser in an intermediate state, unable to commit the row to the virtual table.

Interaction with VSV Module

The VSV (Vertical-Separated Values) module shares code with the CSV module. Both were affected by the buffer initialization issue, as the VSV parser inherited the same logic for handling empty leading fields.

Resolving Buffer Initialization and RFC4180 Compliance

Step 1: Apply Buffer Initialization Fix

The first issue is resolved by ensuring the parser’s buffer is allocated before processing the first field. This prevents false memory allocation errors when encountering empty leading fields.

Code Fix (csv.c and vsv.c):

if (p->z == 0 && p->nAlloc == 0) {  
  p->z = sqlite3_malloc(100);  
  if (p->z == 0) return 0;  
  p->nAlloc = 100;  
}  

This modification initializes a 100-byte buffer if unallocated, allowing the parser to handle empty fields without aborting.

Verification:

1. Create a virtual table with a CSV containing a leading empty field:

   CREATE VIRTUAL TABLE temp.csv USING csv(data='a,b,c\n,2,3', header=true);  
   SELECT * FROM csv;  
   

2. Confirm the output includes both header and data rows.

Step 2: Handle Missing Record Separators per RFC4180

The second fix modifies the parser to treat EOF as an implicit record separator, allowing data rows ending with a field separator to be processed correctly.

Code Fix (csv.c):
Adjust the state machine to finalize the current row when EOF is reached, even if the last character is a field separator.

Verification:

1. Create a virtual table with a CSV ending in a trailing comma:

   CREATE VIRTUAL TABLE temp.csv USING csv(data='a,b,c\n1,2,', header=true);  
   SELECT * FROM csv;  
   

2. Confirm the output includes the data row 1,2,"".

Step 3: Update SQLite Version or Backport Fixes

Ensure the SQLite instance uses a version containing the fixes:

• Buffer initialization: Check-in b12ddabf07b5e06b (July 5, 2022).
• RFC4180 compliance: Check-in 587795d47fcaf514 (July 6, 2022).

For environments where upgrading is not feasible, manually backport the fixes to the CSV and VSV modules.

Workarounds for Unpatched Versions

1. Add Placeholder Values: Replace leading/trailing empty fields with a placeholder (e.g., NULL):

   CREATE VIRTUAL TABLE temp.csv USING csv(data='a,b,c\n<NULL>,2,3', header=true);  
   

2. Append Record Separators: Ensure CSV data ends with a newline:

   CREATE VIRTUAL TABLE temp.csv USING csv(data='a,b,c\n1,2,\n', header=true);  
   

Diagnosing Parsing Errors

1. Enable Debug Logging: Compile SQLite with -DSQLITE_DEBUG and trace parser state transitions.
2. Inspect Intermediate Buffers: Use debuggers to monitor the p->z buffer during parsing.

Final Note: The CSV virtual table’s robustness hinges on precise handling of edge cases. Developers should validate CSV inputs against RFC4180 and test with diverse datasets to uncover parsing anomalies. When in doubt, consult the SQLite change log for fixes related to csv.c and vsv.c.