SQLite zipfile() Returns Zero Rows for Large ZIP Archives Over 2GB

ZIP Archive Size Exceeds SQLite zipfile’s 32-Bit Integer Limits

The SQLite zipfile virtual table allows querying ZIP archives as if they were database tables. However, users encounter unexpected behavior when querying large ZIP files (e.g., 6GB) with this feature. Executing basic queries like SELECT COUNT(*) FROM zip or SELECT name FROM zip LIMIT 1 returns zero rows despite the archive containing valid entries. This occurs because the zipfile implementation uses signed 32-bit integers to parse ZIP file metadata fields such as compressed size, uncompressed size, and header offsets. The original ZIP specification (APPNOTE.TXT) limits these fields to 4 bytes (32 bits), capping maximum representable values at 2^31 – 1 (2,147,483,647 bytes or ~2GB). Archives exceeding this threshold require the ZIP64 extension, which expands these fields to 8 bytes (64 bits). SQLite’s zipfile.c source code does not implement ZIP64 parsing logic, causing it to misinterpret archive headers for files larger than 2GB. Instead of throwing an error or warning, the virtual table silently processes malformed header values as zero or invalid offsets, resulting in empty query results. This creates a false impression of an empty archive rather than surfacing the underlying incompatibility.

The problem is exacerbated by modern use cases involving machine learning datasets, high-resolution media, or scientific data where multi-gigabyte ZIP files are common. Users expect SQLite to handle these archives given its reputation for robustness, but the absence of ZIP64 support creates a hard boundary. The lack of explicit error messaging compounds confusion, as there is no indication that the archive’s size triggered parsing failures. Developers familiar with ZIP64-enabled tools like unzip, 7z, or Python’s zipfile module may not anticipate SQLite’s limitations. Additionally, the issue affects both reading and writing operations: the zipfile virtual table cannot create archives larger than 2GB due to the same 32-bit constraints. While the original design of SQLite’s zipfile extension prioritized simplicity and compatibility with smaller archives, the growing prevalence of large datasets highlights the need for modernization.

ZIP64 Format Unsupported in SQLite’s zipfile Virtual Table Implementation

The root cause of empty query results lies in SQLite’s zipfile virtual table omitting support for the ZIP64 extension. The ZIP64 specification was introduced to address limitations in the original 1989 ZIP format, which used 32-bit fields for critical metadata. When an archive or its constituent files exceed 4GB in uncompressed/compressed size or contain more than 65,535 entries, ZIP64 replaces 32-bit header fields with 64-bit equivalents. SQLite’s zipfile.c codebase, however, exclusively parses 32-bit fields from the central directory and local file headers. For example, the cds (central directory structure) parsing logic in zipfileReadCDS reads nFileSize, nFileSizeUncompressed, and nOffset as 4-byte integers. When these values exceed 0x7FFFFFFF (the maximum positive value for a signed 32-bit integer), they overflow into negative numbers or wrap around modulo 2^32. This leads to nonsensical offsets or sizes that fail validation checks, causing the virtual table to skip entries entirely.

The absence of ZIP64 support is a deliberate design choice rather than an oversight. SQLite’s zipfile extension was initially intended for lightweight interaction with small to medium archives, such as embedded application assets or configuration bundles. The maintainers prioritized code simplicity and portability over handling edge cases like large files. Consequently, the extension does not check for the ZIP64 header identifier (0xFFFFFFFF) in size/offset fields or seek to the ZIP64 extended information extra field in central directory records. Even if an archive uses ZIP64 sparingly (e.g., one file exceeding 4GB among thousands of smaller files), the presence of any 64-bit field will invalidate the entire parsing process. Furthermore, the zipfile virtual table’s write capabilities are similarly constrained: attempting to compress a file larger than 2GB results in silent truncation or corruption due to 32-bit size limitations in the zipfile_append function.

Another compounding factor is the lack of error handling for out-of-range values. Instead of raising an SQL error or warning when encountering ZIP64 headers, the zipfile extension proceeds as if the archive is valid but empty. This violates the principle of failing loudly, making debugging unintuitive. Users must infer the cause through external tools or code inspection. The problem is not limited to archives over 2GB in total size: individual files within a smaller archive that exceed 4GB in uncompressed/compressed size will also trigger the issue. For instance, a 3GB archive containing a single 5GB file (compressed to 2.5GB) will still fail because the uncompressed size field (5GB) exceeds 32 bits. This subtlety can lead to false assumptions about safe archive configurations.

Mitigating Large ZIP File Handling in SQLite via Splitting, External Tools, or Code Modifications

To resolve the empty query results, users must either modify the ZIP archive to comply with SQLite’s 32-bit constraints, employ alternative tools for reading large archives, or patch SQLite to add ZIP64 support. The first approach involves splitting the archive into multiple sub-2GB files using utilities like zip (with the -s split option), 7z, or Python’s zipfile module. For example, splitting a 6GB archive into three 2GB parts ensures each fragment adheres to the size limits. However, this complicates query workflows, as each split must be queried separately. Alternatively, users can repackage the archive with smaller constituent files, ensuring no single file’s compressed/uncompressed size exceeds 2GB. Tools like zip -Z store (no compression) can help avoid size inflation from compression algorithms.

For scenarios requiring direct access to large ZIP files without modification, integrating external libraries or scripts is necessary. Python’s zipfile module (with allowZip64=True) can read ZIP64 archives and export metadata to a temporary SQLite database for querying. A Python script could iterate over the archive’s contents, insert records into a SQLite table, and allow subsequent SQL operations. Similarly, command-line tools like unzip -Z or zipinfo can extract metadata into CSV format, which is then imported into SQLite. While these methods introduce additional steps, they leverage mature ZIP64 implementations outside SQLite.

Advanced users willing to modify SQLite’s source code can implement ZIP64 support in zipfile.c. This involves updating the zipfileReadCDS and zipfileScanDirectory functions to detect ZIP64 headers, read 64-bit size/offset fields, and validate them against the archive’s actual size. Key steps include:

1. Checking for the ZIP64 signature (0xFFFFFFFF) in nFileSize, nFileSizeUncompressed, and nOffset fields.
2. Locating the ZIP64 Extended Information Extra Field in central directory entries to retrieve 64-bit values.
3. Replacing 32-bit integer variables with 64-bit equivalents (e.g., sqlite3_int64 instead of int).
4. Updating offset calculations in zipfileReadData to handle 64-bit file positions.
5. Modifying the zipfile_append function to write ZIP64 headers when appending large files.

These changes require thorough testing to avoid regressions in existing functionality. Developers should also consider submitting patches to the SQLite team for upstream review. Until ZIP64 support is officially added, users must weigh the trade-offs between archive size, workflow complexity, and SQLite integration depth. For mission-critical applications requiring large ZIP file interaction, transitioning to a database system with native ZIP64 support or using a hybrid approach (external preprocessing + SQLite analysis) may be prudent.