Headers Not Displaying with .eqp full in SQLite Shell

Understanding Header Suppression in SQLite Explain Query Plan Mode

The SQLite command-line shell provides several diagnostic and formatting tools to aid developers in query optimization and debugging. Two such tools are the .head and .eqp directives. The .head on command enables column header display for query results, while .eqp (Explain Query Plan) modes (on and full) generate execution plan details. A specific interaction between these commands has been observed: enabling .eqp full suppresses the column headers configured with .head on, whereas .eqp on retains header visibility. This behavior raises questions about whether it is intentional, a bug, or an artifact of implementation details.

To understand this issue, we must dissect how SQLite’s shell processes result formatting and query plan generation. When .eqp full is active, the shell prints the bytecode-level execution plan of a query, followed by the query results. However, the header row configured via .head on is omitted in this mode. In contrast, .eqp on (which prints a high-level query plan) and .eqp off (no plan) both respect the .head on setting. This discrepancy stems from differences in how the shell’s output pipeline handles result rendering and plan display.

The core problem lies in output stream management. The SQLite shell uses separate code paths for rendering query results and diagnostic information. When .eqp full is active, the bytecode plan is treated as a diagnostic output stream, which bypasses the result formatting logic responsible for headers. This creates an inconsistency where headers are visible in some modes but not others. The behavior is reproducible across SQLite versions and is tied to the shell’s internal architecture rather than the SQLite library itself.

Interaction Between .eqp full and Header Display Configuration

1. Output Pipeline Segmentation in the SQLite Shell

The SQLite shell segregates output generation into distinct phases:

• Diagnostic Output: Includes query plans, bytecode dumps, and error messages.
• Result Output: Includes formatted query results (rows, columns, headers).

The .eqp full command merges diagnostic and result output into a single stream but prioritizes diagnostic formatting. When the shell prepares to print a bytecode plan, it initializes a diagnostic context that does not inherit settings from the result formatting subsystem (such as .head on). Subsequent result rows are emitted without re-initializing the header display logic.

2. Header Initialization Timing

Headers are typically printed once per query execution, immediately before the first result row. However, when .eqp full is enabled, the bytecode plan is printed before the result rows. The act of emitting this plan resets or bypasses the header initialization step. This occurs because the bytecode rendering logic does not account for the header display flag (ShellState.showHeader), leaving it in a state that suppresses headers for the subsequent results.

3. Legacy vs. Modern Shell Codebases

The current SQLite shell (as of version 3.37.2) uses a monolithic codebase where diagnostic and result output share overlapping logic. The .eqp full implementation predates more modular output handling, leading to unintended interactions between subsystems. In contrast, the newer extensible SQLite shell (under active development) refactors these components, decoupling diagnostics from result formatting. This explains why the behavior is acknowledged as a limitation rather than a bug.

Workarounds and Alternatives for Header Visibility with Query Plans

1. Use .eqp on Instead of .eqp full

The simplest workaround is to use .eqp on when headers are required. This mode prints a high-level query plan (e.g., SCAN sqlite_master) without suppressing headers. While less detailed than .eqp full, it provides sufficient information for most optimization tasks.

Example:

.eqp on
.head on
SELECT * FROM sqlite_master;

This outputs:

QUERY PLAN
`--SCAN sqlite_master
type|name|tbl_name|rootpage|sql
table|sqlite_stat1|sqlite_stat1|2|CREATE TABLE sqlite_stat1(tbl,idx,stat)

2. Separate Diagnostic and Result Outputs

If bytecode-level analysis is essential, execute the query twice: once with .eqp full to capture the plan and once without to capture results with headers.

Example:

.eqp full
SELECT * FROM sqlite_master;
.eqp off
.head on
SELECT * FROM sqlite_master;

This isolates the diagnostic output (bytecode) from the formatted results, ensuring headers appear in the second query.

3. Scripted Output Post-Processing

For automated workflows, redirect the shell’s output to a file or script, then extract and merge headers programmatically. Use the .once command to save output:

.once plan.txt
.eqp full
SELECT * FROM sqlite_master;
.once results.txt
.eqp off
.head on
SELECT * FROM sqlite_master;

Combine plan.txt and results.txt using shell scripts or tools like sed/awk.

4. Leverage the EXPLAIN Keyword

SQLite’s EXPLAIN command generates bytecode plans without relying on .eqp full. Execute EXPLAIN queries separately while retaining .head on for result sets:

.head on
EXPLAIN SELECT * FROM sqlite_master;
SELECT * FROM sqlite_master;

This approach decouples plan generation from result formatting, though it requires parsing the EXPLAIN output manually.

5. Adopt the Extensible SQLite Shell

Monitor the development of SQLite’s extensible shell, which aims to resolve such inconsistencies by overhauling output handling. Early prototypes demonstrate stricter separation between diagnostic and result streams, preventing header suppression.

6. Custom Shell Builds (Advanced)

For mission-critical environments, modify the SQLite shell source code to force header display in .eqp full mode. Adjust the shell.c logic responsible for printing query results:

// In the function output_plan(ShellState *p):
if (p->autoEQPtest || p->autoEQPtrace) {
    // Existing bytecode printing logic
    // Add header initialization here
    if (p->showHeader) {
        print_header(p); // Hypothetical header-printing function
    }
}

Recompile the shell after applying such patches.

7. Use Alternative Tools

Third-party SQLite browsers and IDEs (e.g., DB Browser for SQLite, DBeaver) often handle query plans and headers more gracefully. These tools bypass the native shell’s limitations by implementing their own output formatters.

By understanding the architectural roots of this behavior and applying targeted workarounds, developers can maintain visibility into both query execution details and result set structure. The SQLite team’s ongoing efforts to modernize the shell promise long-term resolutions for such edge cases.