Schema SQL text for CREATE TABLE/INDEX is canonicalized on reload, not preserved verbatim (CHECK error + sqlite_master.sql divergence)

[timsehn]

Summary

doltlite regenerates CREATE TABLE and CREATE INDEX schema SQL from its structural catalog instead of preserving the verbatim source text. After any schema reload, the stored DDL is re-serialized in canonical form — whitespace/newlines collapse to single spaces and minor syntactic forms change (e.g. CHECK ( → CHECK(). Stock SQLite stores the original CREATE statement byte-for-byte in sqlite_master and re-parses it.

This is not a row-data correctness bug — constraints still enforce correctly and queries return correct rows. It is a schema-text-fidelity divergence with two user-observable effects:

1. SELECT sql FROM sqlite_master returns normalized text for tables/indexes after reopen.
2. CHECK-constraint failure error messages echo the normalized expression text, so the error string differs from stock when the original CHECK(...) spanned multiple lines.

Scope (verified): only TABLE and INDEX DDL is regenerated. VIEW and TRIGGER DDL is preserved verbatim (stored as raw SQL).

object	doltlite sql after reopen
TABLE	collapsed/canonicalized
INDEX	collapsed/canonicalized
VIEW	verbatim (matches stock)
TRIGGER	verbatim (matches stock)

How it was found

check check-4.9 in test/known_testfixture_divergences.txt (bulk-marked under #1119, never individually triaged). Real testfixture output:

! check-4.9 expected: [1 {CHECK constraint failed: x+y==11
        OR x*y==12
        OR x/y BETWEEN 5 AND 8
        OR -x==y+10}]
! check-4.9 got:      [1 {CHECK constraint failed: x+y==11 OR x*y==12 OR x/y BETWEEN 5 AND 8 OR -x==y+10}]

Note check-4.6 asserts the same error string and passes — because at that point the schema has not yet reloaded. check-4.8 runs PRAGMA ignore_check_constraints=ON + PRAGMA integrity_check, which forces a schema reload from the catalog; from then on the CHECK expression is the canonicalized form, so check-4.9 diverges.

Minimal repro (macOS/Linux shells in build/)

-- create table with multi-line CHECK on a file-backed db, then reopen
CREATE TABLE t4(x, y,
      CHECK (
           x+y==11
        OR x*y==12
        OR -x==y+10
      )
    );

After reopening the db:

$ sqlite3 db 'SELECT sql FROM sqlite_master WHERE name="t4"'
CREATE TABLE t4(x, y,
      CHECK (
           x+y==11
        OR x*y==12
        OR -x==y+10
      )
    )

$ doltlite db 'SELECT sql FROM sqlite_master WHERE name="t4"'
CREATE TABLE t4(x, y, CHECK( x+y==11 OR x*y==12 OR -x==y+10))

And the constraint-failure message follows the same (normalized for doltlite) text.

Assessment / priority

Low severity, cosmetic — no data or constraint-enforcement impact. It is arguably an inherent consequence of the Dolt model storing table/index schema structurally rather than as raw SQL text. Filing to track it explicitly (rather than leaving check-4.9 as an unexplained bulk-marked divergence) and to decide whether table/index sql should round-trip verbatim for .schema/sqlite_master parity. Part of the granular #1119 review.