SQLite CLI Column Misalignment with CJK Characters in Table, Box, and Markdown Modes

---

CJK Character Display and Column Alignment Limitations in SQLite CLI

---

Unicode Glyph Width Handling in Terminal Output Modes

The SQLite command-line interface (CLI) provides several output modes, including table, box, and markdown, designed to format query results in a human-readable tabular layout. However, when these modes are used to display data containing Chinese, Japanese, or Korean (CJK) characters, column alignment often breaks due to incorrect width calculations. CJK characters are typically rendered as double-width glyphs in monospace fonts, whereas SQLite CLI treats all Unicode characters as single-width. This discrepancy leads to misaligned vertical separators (|) and uneven column spacing, particularly in columns adjacent to CJK text. The issue is exacerbated when CJK characters appear in one column and single-width characters (e.g., Latin letters) appear in another, as seen in the provided example where the code column’s left separator shifts unpredictably.

The root cause lies in SQLite CLI’s reliance on character count rather than display width for formatting. For example, the string 曑 (a CJK character) is counted as one character but occupies two terminal columns. When the CLI computes column widths, it underestimates the space required for CJK-heavy columns, causing misalignment. This problem is not unique to SQLite; many terminal-based tools struggle with mixed-width Unicode characters. However, SQLite CLI lacks built-in support for East Asian Width (EAW) properties defined by Unicode, which classify characters as full-width (double-width), half-width (single-width), or ambiguous. Without this metadata, the CLI cannot dynamically adjust column padding based on actual glyph dimensions.

---

Terminal Environment Constraints and Unicode Complexity

The misalignment issue arises from three interrelated factors: Unicode glyph width variability, terminal emulator behavior, and SQLite CLI’s formatting logic.

1. Unicode Glyph Width Standards:
   The Unicode Standard defines the East Asian Width property to specify whether a character should occupy one or two terminal columns. For example, most CJK ideographs (Unicode block U+4E00–U+9FFF) are classified as Wide, while ASCII characters (e.g., A, 1) are Narrow. However, SQLite CLI does not reference EAW data when calculating column widths. Instead, it assumes all characters contribute equally to column width, leading to incorrect padding.

2. Terminal Emulator Inconsistencies:
   Terminal emulators vary in their handling of double-width characters. For instance, Windows Console (prior to Windows 10) and modern terminals like Windows Terminal or Linux’s GNOME Terminal handle CJK characters differently. Even if SQLite CLI were to correctly compute display widths, alignment might still fail if the terminal misrenders glyphs. This creates a dependency on terminal-specific APIs (e.g., Windows Console’s GetConsoleFontSize) that are either unstable or unavailable across platforms.

3. SQLite CLI’s Minimalist Design Philosophy:
   SQLite prioritizes simplicity and portability. Implementing EAW-aware formatting would require embedding a Unicode property database or integrating platform-specific APIs to query glyph widths. Both approaches conflict with SQLite’s goal of being a self-contained, zero-configuration tool. The current workaround—using predefined column widths—fails for mixed CJK/non-CJK data because it cannot dynamically adjust to variable glyph widths.

---

EAW-Aware Formatting and Terminal Workarounds

To resolve CJK alignment issues, SQLite CLI must adopt EAW-aware column width calculations and provide terminal-specific overrides. Below is a detailed implementation strategy:

Step 1: Integrate East Asian Width Data
The Unicode Consortium publishes EAW property ranges in Unicode Technical Report #11. These ranges can be hardcoded into SQLite CLI as a lookup table. For example, the EAW property defines 125 contiguous ranges of Wide characters. Storing these ranges as pairs of 32-bit integers (start and end code points) would consume ~1 KB of memory—a negligible overhead. During formatting, the CLI would check each character against these ranges and count Wide characters as two columns. This approach mirrors solutions used in libraries like wcwidth (Linux) and unicodedata2 (Python).

Step 2: Modify Column Width Calculation Logic
In table, box, and markdown modes, SQLite CLI computes column widths using the length() SQL function, which returns the number of characters, not display columns. Replacing length() with a custom function that accounts for EAW properties would correct width calculations. For example:

int display_width(const char *text) {
  int width = 0;
  while (*text != '\0') {
    UChar32 c; // Code point variable
    text = utf8_decode(text, &c); // Decode UTF-8
    width += (is_wide(c)) ? 2 : 1;
  }
  return width;
}

Here, is_wide(c) checks if c falls within any EAW Wide range. This function would replace length() when determining padding.

Step 3: Terminal-Specific Adjustments
Some terminals (e.g., Windows Console) use non-monospaced fonts for CJK characters or apply legacy width heuristics. To handle these cases, SQLite CLI could introduce a .terminal command to override width assumptions. For example:

.terminal cjkwidth 1

This command would force the CLI to treat all CJK characters as double-width, regardless of terminal behavior. Advanced users could fine-tune widths for specific terminals.

Step 4: Testing and Fallback Mechanisms
To ensure robustness, SQLite CLI should include a fallback mode that disables EAW processing if terminal probing fails. A new PRAGMA cli_unicode_widths=ON|OFF could let users toggle EAW awareness. Testing would involve running the provided CJK test case across terminals and verifying alignment.

---

Temporary Workarounds for Users
While awaiting an official fix, users can:

1. Switch to .mode list:
   List mode avoids alignment issues by separating columns with a delimiter (e.g., |), though it sacrifices tabular formatting.

2. Post-Process Output with External Tools:
   Pipe SQLite output to utilities like column (Linux) or PowerShell Format-Table, which handle CJK widths better:

   sqlite3 db.sqlite "SELECT * FROM cangjie5;" | column -t -s '|'
   

3. Custom Build with EAW Support:
   Advanced users can modify SQLite’s source code to integrate EAW ranges. The fts5_unicode2 tokenizer (part of the FTS5 extension) already includes Unicode tables, which could be reused for width calculations.

---

By adopting EAW-aware formatting and offering terminal overrides, SQLite CLI can achieve correct CJK alignment without sacrificing portability. This approach balances Unicode compliance with the practical constraints of terminal diversity.