[PR #5879] [MERGED] fix(sqlite): attribute generated-column evaluation failures to the selection #7096

New issue

Closed

opened 2026-11-01 21:45:21 +00:00 by bsakaguchi · 0 comments

bsakaguchi commented

2026-11-01 21:45:21 +00:00

Owner

📋 Pull Request Information

Original PR: https://github.com/morluto/rea/pull/5879
Author: @kaoru0822-kitauji
Created: 10/21/2026
Status: ✅ Merged
Merged: 10/21/2026
Merged by: @kaoru0822-kitauji

Base: main ← Head: fix/sqlite-generated-column-evaluation

📝 Commits (3)

3817db8 fix(sqlite): attribute generated-column evaluation failures to the selection
5236834 fix(sqlite): recommend only probed-readable columns after evaluation failures
6cbe0ae Merge remote-tracking branch 'origin/main' into fix/sqlite-generated-column-evaluation

📊 Changes

7 files changed (+473 additions, -36 deletions)

View changed files

📝 docs/sqlite-databases.md (+6 -1)
📝 src/sqlite/SqliteDatabaseInspection.ts (+133 -20)
📝 src/sqlite/SqliteDatabaseLimits.ts (+18 -0)
📝 src/sqlite/SqliteDatabaseWorker.ts (+24 -14)
📝 src/sqlite/SqliteDatabaseWorkerExecution.ts (+24 -1)
📝 tests/acceptance/sqliteDatabaseCli.test.ts (+95 -0)
📝 tests/boundary/sqlite/sqliteDatabaseInspection.test.ts (+173 -0)

📄 Description

What broke: Selecting a virtual generated column whose expression REA's read-only SQLite connection cannot evaluate (e.g. sha3() supplied by the stock shell) returned invalid_request / invalid_format on path, telling callers to correct a readable database path.
Fix: Row reads attribute SQLITE_ERROR from selected cells to the failing virtual generated columns. They return a typed capability_unavailable refusal naming the table and each failing column with SQLite's message, and recommend only columns whose sampled values were read.
Risk: Low; limited to the row-read error path of inspect_sqlite_database. Corruption, TOOBIG and NOMEM keep their existing classification; the success path is unchanged.

Summary

Fixes #5719

inspect_sqlite_database classified every unrecognized native SQLite error in the worker as format, which the parent projects as an invalid path. A generated-column evaluation failure (unknown function, or an SQL error the expression raises for a sampled row) is a property of the selected cells, not of the file: schema-only inspection and base-column selections of the same database succeed.

Problem and expected behavior

Smallest trigger: CREATE TABLE t(a TEXT, b TEXT GENERATED ALWAYS AS (sha3(a)) VIRTUAL) written by the stock sqlite3 shell, then rea inspect-sqlite-database hashes.db --table t [--column b --column a] --json. Violated invariant: each input rejection names the argument that needs correction (SqliteDatabaseWorkerExecution.ts). Expected: a typed refusal that names the selection and the SQLite reason, with guidance to select the readable columns or read the schema.

Change and scope

SqliteDatabaseInspection.readRows owns the cell-evaluation stage. On an SQLITE_ERROR (primary code 1) while preparing or iterating the selected-cell statements, it probes each distinct selected column alone over the same sampled rows. The probe uses the shared cellByteLength expression, which also builds the main length pass. Every probe failure is recorded with its own SQLite message (including TOOBIG). The refusal is raised only when a virtual generated column (hidden = 2, the only cells SQLite evaluates when read) fails with SQLITE_ERROR; otherwise the original cause keeps its classification.
Only columns whose probe succeeded are recommended. When none of the selection is readable, ordinary and stored generated columns are discovered from pragma_table_xinfo (this also covers inventory scope, whose metadata holds only the selection). Undecodable names are skipped rather than thrown, and the discovered columns are probed the same way.
sqlitePrimaryResultCode in SqliteDatabaseLimits.ts is the single interpreter of node:sqlite errcode; the worker's memory / output-limit mapping uses it as well (it now masks extended codes).
The worker reply protocol gains a strict column-evaluation variant with readable_columns; the parent maps it to AnalysisCapabilityUnavailableError (non-retryable). Recovery gives the structured columns: [...] selection ("CLI: one --column per name") or schema_scope: database. It never emits shell command text built from database-supplied names.
Probing runs only on this failure path, so successful inspections do the same work as before.
Not included: partial success (the documented contract says "Failures return no partial success"), extension loading, or function registration. Scope decision: not applicable (fix within existing boundaries).

Contract and boundary impact

Semantic owner and earliest changed stage: SQLite worker row read (SqliteDatabaseInspection.readRows) and the worker-reply mapping.
Contribution scope fit and linked maintainer decision: ordinary fix within the existing provider; not applicable.
CLI and MCP/tool-catalog contract: input/output schemas unchanged. The error for this case changes from invalid_request (path, invalid_format) to capability_unavailable, the same through CLI and MCP.
Provider, bridge, target-format, or platform compatibility: none.
Evidence, artifact, provenance, or reconstruction contract: none; failures still return no Evidence.
Target selection, authority, upstream behavior, and cleanup details: unchanged; the read-only, trust-off, no-extension connection is preserved.
Generated metadata, package, or installation impact: none. docs/sqlite-databases.md documents the refusal.
Read existing files or captured data.

Evidence and regression coverage

Tests added or updated:
- tests/acceptance/sqliteDatabaseCli.test.ts: a real database whose generated column uses an application function; explicit and default selections through CLI and MCP return capability_unavailable with the column and its remaining columns, while --column a and schema_scope: database still succeed and the source bytes are unchanged.
- tests/boundary/sqlite/sqliteDatabaseInspection.test.ts: per-column attribution of a compile error (unknown function) and a row evaluation error (malformed JSON, planted with writable_schema); remaining columns with duplicates; the stored-column fallback; stored, built-in generated and unsampled cells staying readable; a mixed missing-function and TOOBIG selection that recommends only the probed-readable column; and an inventory-scope fallback that skips an invalid-UTF-8 column name.
Base reproduction: both original new tests fail on f801b2fbf with the fix stashed (2 failed / 32 passed). The two review-follow-up tests fail on the first PR commit's src (3 failed / 20 passed) and pass now.
User-visible CLI output (--table t --column b --column a, stock-shell sha3 database, isolated HOME/XDG):

Before (main):

{"code":"invalid_request","category":"invalid_input",
 "details":{"issues":[{"path":["path"],"reason":"invalid_format",
  "message":"Selected SQLite database is invalid (unknown function: sha3()): …/hashes.db"}]},
 "remediation":{"action":"Read the declared input schema or CLI usage for inspect_sqlite_database; … Correct the listed fields, then retry. …"}}

After:

{"code":"capability_unavailable","category":"unsupported_provider","retryable":false,
 "message":"SQLite cannot evaluate selected column(s) of table \"t\" on REA's read-only connection: \"b\": unknown function: sha3()",
 "remediation":{"action":"The database is readable; only the listed column values failed. REA's SQLite connection loads no extensions or application functions and disables schema trust. Select columns whose values were read: columns: [\"a\"] (CLI: one --column per name); omit table and use schema_scope: database to read the stored declarations without evaluating rows. Obtaining the failed values needs a SQLite connection able to evaluate them. Do not repeat the unchanged request."}}

The default selection (--table t) returns the same error. A non-database file still reports invalid_format on path.

Remaining proof gaps: Windows and Linux were not run locally (CI covers them). The real-CLI repro used macOS SQLite 3.51.0 sqlite3; the tests use node:sqlite producers so they don't depend on the shell. "unsafe use of …" failures under trusted_schema=OFF take the same SQLITE_ERROR path, but I couldn't build a built-in trigger for them (every built-in I tried that generated columns allow is innocuous).
Observed, derived, and inferred claims remain distinguishable.
Artifact identity, source provenance, and failed attempts remain preserved.
Unsupported, incomplete, unavailable, or uncertain outcomes remain visible.

Validation performed

Real CLI repro (issue script, macOS arm64, Node v26.5.0): before, --table t --column b --column a and --table t exit 1 with invalid_format on path; after, both exit 1 with the capability_unavailable above. --schema-scope database and --column a exit 0. Elapsed time is unchanged: about 1.12 s before and 1.11–1.21 s after over 3 runs.
Extra real cases (node:sqlite producer): a mixed table with an application function plus a planted json_extract on malformed JSON attributes both columns; stored generated and upper() generated columns read successfully.
npm run test:focused -- tests/boundary/sqlite/sqliteDatabaseInspection.test.ts tests/acceptance/sqliteDatabaseCli.test.ts tests/boundary/sqlite/sqliteColumnIdentity.test.ts: 39 passed, 2 skipped.
Same two new tests with the fix stashed: 2 failed (the new tests), 32 passed.
VITEST_MAX_WORKERS=2 npm run test:sqlite (after the review fixes and merging origin/main): 12 files, 159 passed, 3 skipped.
Real CLI with a stock-shell table whose column is named $(id), using --column b: the recovery lists columns: ["$(id)"] as JSON only; 1.16 s.
npm run check:fast: passed.
npm run format:check: passed.
npm run docs:check: passed.

Compatibility, safety, and release

Breaking changes or migration steps: none. Only the error code and recovery for this failure change, from a misleading input error to a typed capability refusal.
Real Hopper/Ghidra, browser, or OS coverage: not applicable; macOS only locally.
Package or release metadata impact: none.
Security, privacy, process, or containment review: the connection still disables extensions and schema trust. Probes are read-only SELECTs on the private snapshot and run inside the existing worker deadline.

Author readiness

Human owner: @kaoru0822-kitauji

I reviewed the final diff and relevant existing code, understand the change and its edge cases, and take responsibility for the complete PR, including AI-assisted work.
I checked the evidence above, confirmed it supports the current change, and recorded meaningful validation gaps.
I declare this PR ready for initial maintainer review.

Before merge

This PR addresses a concrete problem or an agreed enhancement.
Material scope and authority questions are resolved, and any required maintainer scope decision covers the final diff.
The PR has one focused outcome and the title follows type(scope): outcome.
Related issue is linked, or the reason for not linking one is stated above.
Tests cover changed observable behavior and meaningful failure paths.
Owning docs, contracts, and generated metadata are updated where needed.
User-visible CLI/MCP changes include representative output.
I checked the final diff for secrets, unrelated cleanup, and unsupported claims.

🤖 Generated with Claude Code

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/morluto/rea/pull/5879 **Author:** [@kaoru0822-kitauji](https://github.com/kaoru0822-kitauji) **Created:** 10/21/2026 **Status:** ✅ Merged **Merged:** 10/21/2026 **Merged by:** [@kaoru0822-kitauji](https://github.com/kaoru0822-kitauji) **Base:** `main` ← **Head:** `fix/sqlite-generated-column-evaluation` --- ### 📝 Commits (3) - [`3817db8`](https://github.com/morluto/rea/commit/3817db874e14df5a57c3a67f409914978cdea34c) fix(sqlite): attribute generated-column evaluation failures to the selection - [`5236834`](https://github.com/morluto/rea/commit/5236834a70d4a4c286a8ea9b677dfe841823d530) fix(sqlite): recommend only probed-readable columns after evaluation failures - [`6cbe0ae`](https://github.com/morluto/rea/commit/6cbe0ae6fcff2042cfbfdc91afcc88d2ebb2978e) Merge remote-tracking branch 'origin/main' into fix/sqlite-generated-column-evaluation ### 📊 Changes **7 files changed** (+473 additions, -36 deletions) <details> <summary>View changed files</summary> 📝 `docs/sqlite-databases.md` (+6 -1) 📝 `src/sqlite/SqliteDatabaseInspection.ts` (+133 -20) 📝 `src/sqlite/SqliteDatabaseLimits.ts` (+18 -0) 📝 `src/sqlite/SqliteDatabaseWorker.ts` (+24 -14) 📝 `src/sqlite/SqliteDatabaseWorkerExecution.ts` (+24 -1) 📝 `tests/acceptance/sqliteDatabaseCli.test.ts` (+95 -0) 📝 `tests/boundary/sqlite/sqliteDatabaseInspection.test.ts` (+173 -0) </details> ### 📄 Description **What broke:** Selecting a virtual generated column whose expression REA's read-only SQLite connection cannot evaluate (e.g. `sha3()` supplied by the stock shell) returned `invalid_request` / `invalid_format` on `path`, telling callers to correct a readable database path. **Fix:** Row reads attribute `SQLITE_ERROR` from selected cells to the failing virtual generated columns. They return a typed `capability_unavailable` refusal naming the table and each failing column with SQLite's message, and recommend only columns whose sampled values were read. **Risk:** Low; limited to the row-read error path of `inspect_sqlite_database`. Corruption, `TOOBIG` and `NOMEM` keep their existing classification; the success path is unchanged. ## Summary Fixes #5719 `inspect_sqlite_database` classified every unrecognized native SQLite error in the worker as `format`, which the parent projects as an invalid `path`. A generated-column evaluation failure (unknown function, or an SQL error the expression raises for a sampled row) is a property of the selected cells, not of the file: schema-only inspection and base-column selections of the same database succeed. ## Problem and expected behavior Smallest trigger: `CREATE TABLE t(a TEXT, b TEXT GENERATED ALWAYS AS (sha3(a)) VIRTUAL)` written by the stock `sqlite3` shell, then `rea inspect-sqlite-database hashes.db --table t [--column b --column a] --json`. Violated invariant: each input rejection names the argument that needs correction (`SqliteDatabaseWorkerExecution.ts`). Expected: a typed refusal that names the selection and the SQLite reason, with guidance to select the readable columns or read the schema. ## Change and scope - `SqliteDatabaseInspection.readRows` owns the cell-evaluation stage. On an `SQLITE_ERROR` (primary code 1) while preparing or iterating the selected-cell statements, it probes each distinct selected column alone over the same sampled rows. The probe uses the shared `cellByteLength` expression, which also builds the main length pass. Every probe failure is recorded with its own SQLite message (including `TOOBIG`). The refusal is raised only when a virtual generated column (`hidden = 2`, the only cells SQLite evaluates when read) fails with `SQLITE_ERROR`; otherwise the original cause keeps its classification. - Only columns whose probe succeeded are recommended. When none of the selection is readable, ordinary and stored generated columns are discovered from `pragma_table_xinfo` (this also covers inventory scope, whose metadata holds only the selection). Undecodable names are skipped rather than thrown, and the discovered columns are probed the same way. - `sqlitePrimaryResultCode` in `SqliteDatabaseLimits.ts` is the single interpreter of node:sqlite `errcode`; the worker's `memory` / `output-limit` mapping uses it as well (it now masks extended codes). - The worker reply protocol gains a strict `column-evaluation` variant with `readable_columns`; the parent maps it to `AnalysisCapabilityUnavailableError` (non-retryable). Recovery gives the structured `columns: [...]` selection ("CLI: one --column per name") or `schema_scope: database`. It never emits shell command text built from database-supplied names. - Probing runs only on this failure path, so successful inspections do the same work as before. - Not included: partial success (the documented contract says "Failures return no partial success"), extension loading, or function registration. Scope decision: not applicable (fix within existing boundaries). ## Contract and boundary impact - Semantic owner and earliest changed stage: SQLite worker row read (`SqliteDatabaseInspection.readRows`) and the worker-reply mapping. - Contribution scope fit and linked maintainer decision: ordinary fix within the existing provider; not applicable. - CLI and MCP/tool-catalog contract: input/output schemas unchanged. The error for this case changes from `invalid_request` (`path`, `invalid_format`) to `capability_unavailable`, the same through CLI and MCP. - Provider, bridge, target-format, or platform compatibility: none. - Evidence, artifact, provenance, or reconstruction contract: none; failures still return no Evidence. - Target selection, authority, upstream behavior, and cleanup details: unchanged; the read-only, trust-off, no-extension connection is preserved. - Generated metadata, package, or installation impact: none. `docs/sqlite-databases.md` documents the refusal. - [x] Read existing files or captured data. ## Evidence and regression coverage - Tests added or updated: - `tests/acceptance/sqliteDatabaseCli.test.ts`: a real database whose generated column uses an application function; explicit and default selections through CLI and MCP return `capability_unavailable` with the column and its remaining columns, while `--column a` and `schema_scope: database` still succeed and the source bytes are unchanged. - `tests/boundary/sqlite/sqliteDatabaseInspection.test.ts`: per-column attribution of a compile error (unknown function) and a row evaluation error (`malformed JSON`, planted with `writable_schema`); remaining columns with duplicates; the stored-column fallback; stored, built-in generated and unsampled cells staying readable; a mixed missing-function and `TOOBIG` selection that recommends only the probed-readable column; and an inventory-scope fallback that skips an invalid-UTF-8 column name. - Base reproduction: both original new tests fail on `f801b2fbf` with the fix stashed (2 failed / 32 passed). The two review-follow-up tests fail on the first PR commit's `src` (3 failed / 20 passed) and pass now. - User-visible CLI output (`--table t --column b --column a`, stock-shell `sha3` database, isolated HOME/XDG): Before (main): ```json {"code":"invalid_request","category":"invalid_input", "details":{"issues":[{"path":["path"],"reason":"invalid_format", "message":"Selected SQLite database is invalid (unknown function: sha3()): …/hashes.db"}]}, "remediation":{"action":"Read the declared input schema or CLI usage for inspect_sqlite_database; … Correct the listed fields, then retry. …"}} ``` After: ```json {"code":"capability_unavailable","category":"unsupported_provider","retryable":false, "message":"SQLite cannot evaluate selected column(s) of table \"t\" on REA's read-only connection: \"b\": unknown function: sha3()", "remediation":{"action":"The database is readable; only the listed column values failed. REA's SQLite connection loads no extensions or application functions and disables schema trust. Select columns whose values were read: columns: [\"a\"] (CLI: one --column per name); omit table and use schema_scope: database to read the stored declarations without evaluating rows. Obtaining the failed values needs a SQLite connection able to evaluate them. Do not repeat the unchanged request."}} ``` The default selection (`--table t`) returns the same error. A non-database file still reports `invalid_format` on `path`. - Remaining proof gaps: Windows and Linux were not run locally (CI covers them). The real-CLI repro used macOS SQLite 3.51.0 `sqlite3`; the tests use node:sqlite producers so they don't depend on the shell. `"unsafe use of …"` failures under `trusted_schema=OFF` take the same `SQLITE_ERROR` path, but I couldn't build a built-in trigger for them (every built-in I tried that generated columns allow is innocuous). - [x] Observed, derived, and inferred claims remain distinguishable. - [x] Artifact identity, source provenance, and failed attempts remain preserved. - [x] Unsupported, incomplete, unavailable, or uncertain outcomes remain visible. ## Validation performed - Real CLI repro (issue script, macOS arm64, Node v26.5.0): before, `--table t --column b --column a` and `--table t` exit 1 with `invalid_format` on `path`; after, both exit 1 with the `capability_unavailable` above. `--schema-scope database` and `--column a` exit 0. Elapsed time is unchanged: about 1.12 s before and 1.11–1.21 s after over 3 runs. - Extra real cases (node:sqlite producer): a mixed table with an application function plus a planted `json_extract` on malformed JSON attributes both columns; stored generated and `upper()` generated columns read successfully. - `npm run test:focused -- tests/boundary/sqlite/sqliteDatabaseInspection.test.ts tests/acceptance/sqliteDatabaseCli.test.ts tests/boundary/sqlite/sqliteColumnIdentity.test.ts`: 39 passed, 2 skipped. - Same two new tests with the fix stashed: 2 failed (the new tests), 32 passed. - `VITEST_MAX_WORKERS=2 npm run test:sqlite` (after the review fixes and merging `origin/main`): 12 files, 159 passed, 3 skipped. - Real CLI with a stock-shell table whose column is named `$(id)`, using `--column b`: the recovery lists `columns: ["$(id)"]` as JSON only; 1.16 s. - `npm run check:fast`: passed. - `npm run format:check`: passed. - `npm run docs:check`: passed. ## Compatibility, safety, and release - Breaking changes or migration steps: none. Only the error code and recovery for this failure change, from a misleading input error to a typed capability refusal. - Real Hopper/Ghidra, browser, or OS coverage: not applicable; macOS only locally. - Package or release metadata impact: none. - Security, privacy, process, or containment review: the connection still disables extensions and schema trust. Probes are read-only `SELECT`s on the private snapshot and run inside the existing worker deadline. ## Author readiness Human owner: @kaoru0822-kitauji - [x] I reviewed the final diff and relevant existing code, understand the change and its edge cases, and take responsibility for the complete PR, including AI-assisted work. - [x] I checked the evidence above, confirmed it supports the current change, and recorded meaningful validation gaps. - [x] I declare this PR ready for initial maintainer review. ## Before merge - [x] This PR addresses a concrete problem or an agreed enhancement. - [x] Material scope and authority questions are resolved, and any required maintainer scope decision covers the final diff. - [x] The PR has one focused outcome and the title follows `type(scope): outcome`. - [x] Related issue is linked, or the reason for not linking one is stated above. - [x] Tests cover changed observable behavior and meaningful failure paths. - [x] Owning docs, contracts, and generated metadata are updated where needed. - [x] User-visible CLI/MCP changes include representative output. - [x] I checked the final diff for secrets, unrelated cleanup, and unsupported claims. 🤖 Generated with [Claude Code](https://claude.com/claude-code) --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

bsakaguchi

2026-11-01 21:45:21 +00:00