SQLite 3.44.1 CLI .output/.once Failure & Multi-Line Input Issues

CLI Command Failures and Console Input Handling in SQLite 3.44.1

Issue Overview: .output/.once Command Failures and Multi-Line Input Parsing Errors

The SQLite 3.44.1 release introduced critical regressions in the command-line interface (CLI) tool, primarily affecting two areas:

1. Redirection of Query Output via .output and .once Commands
   Users reported that after upgrading to SQLite 3.44.1, the .output and .once commands failed to redirect query results to specified files. Instead, output continued to display on the console, and empty (zero-byte) files were created. This issue occurred across multiple platforms, including Windows 10/11 (CMD, PowerShell, third-party shells like Take Command) and macOS. For example:

   .output test.csv
   SELECT * FROM table;
   

   Created test.csv as a zero-byte file while printing results to the console.

2. Multi-Line SQL Statement Parsing Errors
   A separate issue arose when pasting multi-line SQL statements into the CLI. Users observed unexpected continuation prompts ((x1...>) and incomplete statement execution. For instance:

   CREATE TABLE course(
     c_id INT NOT NULL,
     cname VARCHAR(50),
     note VARCHAR(500),
     PRIMARY KEY(c_id)
   );
   

   Resulted in a stuck CLI session with no table created. This behavior was inconsistent: manually typing the same statement worked, but pasting it (especially without a trailing newline) caused failures.

3. Post-Fix Regression in .timer Output Redirection
   After the initial fixes in SQLite 3.44.2, a new problem emerged: the .timer on output, which previously printed to the console (stderr), began redirecting to the file specified by .output. This disrupted workflows relying on timer diagnostics appearing separately from query results.

Possible Causes: UTF-16 Console I/O Changes and Line Ending Handling

1. UTF-16 Console I/O Implementation in 3.44.1
   The root cause of the .output/.once failure traces back to a change in how the CLI handles console input/output. SQLite 3.44.1 switched to UTF-16 encoding for console interactions on Windows to better support Unicode characters. However, this change inadvertently broke the redirection logic for file output. The CLI’s internal buffering mechanism failed to properly flush UTF-16-encoded data to disk, resulting in empty files.

2. Line Ending and Clipboard Interaction in Multi-Line Input
   The multi-line parsing issue stemmed from how different console hosts handle pasted text. Consoles like cmd.exe, PowerShell, and third-party terminals (e.g., ConEmu, FAR Manager) inject line endings inconsistently when pasting. SQLite 3.44.1’s updated input parser misinterpreted the absence of a final newline character as an incomplete statement, leaving the CLI in a continuation prompt loop. This was exacerbated by variations in how consoles emulate the Win32 Console API, particularly on older Windows versions (e.g., Windows 7).

3. Stream Merging in .timer Fix
   The 3.44.2 patch corrected the .output/.once failure by ensuring proper file writes but unintentionally merged stderr (used for .timer output) with stdout. This violated the conventional separation of diagnostic and data streams, causing timer metrics to pollute redirected output files.

Troubleshooting Steps, Solutions & Fixes

Step 1: Verify SQLite CLI Version and Affected Commands

Confirm the issue exists by checking the CLI version:

sqlite3 --version
# SQLite 3.44.1 2023-11-22 14:18:12 ... (UTF-16 console I/O)

Test .output:

.output test.txt
SELECT 'Hello, World!';
.exit

If test.txt is empty, the installation is affected.

Step 2: Immediate Workarounds for .output/.once Failures

• Downgrade to SQLite 3.44.0: Use a pre-3.44.1 CLI where UTF-8 console I/O is the default.
• Manual Flushing with .system: Force file writes via shell commands:

  .output test.txt
  SELECT 'Hello, World!';
  .system sync  # Linux/macOS
  .system fsutil file flush test.txt  # Windows
  

Step 3: Apply Official Fixes in SQLite 3.44.2+

Upgrade to SQLite 3.44.2 or later, which resolves the UTF-16 file write issue. Pre-release builds were validated by users:

-- Post-upgrade validation
.output test.txt
SELECT 'Test';
.exit

test.txt should now contain "Test".

Step 4: Address Multi-Line Input Parsing Errors

• Ensure Trailing Newline in Pasted Text: Edit pasted SQL to end with a semicolon and newline.
• Use Batch Mode for Scripts: Execute multi-line statements from a file:

  sqlite3 < script.sql
  

• Temporary Reversion to Legacy Console I/O: Launch the CLI with -no-utf8 (if available) to bypass UTF-16 handling:

  sqlite3 -no-utf8
  

Step 5: Correct .timer Output Redirection

In SQLite 3.44.2, timer diagnostics are erroneously written to the .output file. Two solutions exist:

• Upgrade to SQLite 3.45.0+: The trunk version restores pre-3.44.1 behavior, directing .timer output to stderr.
• Filter Timer Output via Scripts: Use shell redirection to separate stdout and stderr:

  sqlite3 2> timer.log
  .output data.txt
  .timer on
  SELECT 1;
  

Step 6: Platform-Specific Console Configuration

• Windows Consoles: For third-party shells (Take Command, ConEmu), ensure they fully support UTF-16 or force UTF-8 via:

  chcp 65001  # Set console codepage to UTF-8
  sqlite3 -no-utf8
  

• macOS/Linux Terminals: Verify $LANG environment variable includes UTF-8 (e.g., en_US.UTF-8) to avoid locale conflicts.

Step 7: Reporting Edge Cases and Testing Pre-Releases

If issues persist, especially on niche consoles or older OS versions:

• Capture a minimal reproduction case, including:
  • Exact CLI version (.version command)
  • Console host and version (e.g., cmd.exe, powershell.exe, ConEmu 230724)
  • Full input sequence (preferably as a pastable script)
• Test with pre-release builds from the SQLite Download Page and report findings to the SQLite team via the forum or direct email.

This guide provides a comprehensive pathway to diagnose, mitigate, and resolve the regressions introduced in SQLite 3.44.1, emphasizing the importance of version-specific testing and platform-aware configuration. Users are encouraged to leverage official patches and contribute to community debugging efforts for rare edge cases.