morluto/rea

Fork 0

mirror of https://github.com/morluto/rea.git synced 2026-11-05 06:45:22 +00:00

[GH-ISSUE #5719] [Bug] SQLite generated-column evaluation failures blame the database path #3467

New issue

Closed

opened 2026-10-31 00:40:47 +00:00 by bsakaguchi · 2 comments

bsakaguchi commented

2026-10-31 00:40:47 +00:00

Owner

Originally created by @coygeek on GitHub (Oct 20, 2026).
Original GitHub issue: https://github.com/morluto/rea/issues/5719

Originally assigned to: @kaoru0822-kitauji on GitHub.

Before submitting

I searched the open and closed issues for a related report.
I removed credentials, tokens, private paths, and sensitive target data.

Area

CLI

Observed behavior

Selecting a generated column from a readable SQLite database returns invalid_request, invalid_format on path, and guidance to correct the listed input fields. The same file returns its schema and base-column rows successfully. The generated column requires sha3, which the stock SQLite shell supplies but REA's connection does not. Refusing that evaluation is expected; blaming the database path and directing the caller to correct it is the defect.

Reproduction

Run from the source checkout below with locked dependencies installed and the CLI built. The stock sqlite3 shell must provide sha3. This shell block was independently verified and replayed during submission:

work_dir="$(mktemp -d)"
mkdir -p "$work_dir/home" "$work_dir/cfg" "$work_dir/data" \
  "$work_dir/cache" "$work_dir/state" "$work_dir/tmp"

sqlite3 "$work_dir/hashes.db" <<'SQL'
CREATE TABLE t (a TEXT, b TEXT GENERATED ALWAYS AS (sha3(a)) VIRTUAL);
INSERT INTO t(a) VALUES ('hello'), ('world');
SQL

sqlite3 "$work_dir/hashes.db" \
  'PRAGMA integrity_check; SELECT a, length(b) FROM t;'

rea_snapshot() {
  env -i PATH="$PATH" HOME="$work_dir/home" \
    XDG_CONFIG_HOME="$work_dir/cfg" XDG_DATA_HOME="$work_dir/data" \
    XDG_CACHE_HOME="$work_dir/cache" XDG_STATE_HOME="$work_dir/state" \
    TMPDIR="$work_dir/tmp" REA_LOG_LEVEL=silent \
    node scripts/rea.mjs inspect-sqlite-database "$work_dir/hashes.db" "$@" --json
}

rea_snapshot --schema-scope database
rea_snapshot --table t --column a
rea_snapshot --table t --column b --column a
rea_snapshot --table t

The SQLite control prints ok, hello|32, and world|32. Schema-only inspection and selecting a succeed. Explicitly selecting b and using the default all-column selection each fail with the same error excerpt below. Both failing commands have empty stderr.

Expected behavior

A selected-expression failure should identify the unavailable evaluation dependency and retain the selected table or column context. Recovery should explain that readable base columns can be selected separately. A usable database path should not be diagnosed as malformed because one generated value cannot be computed.

docs/sqlite-databases.md:57-69 documents exact column selection, inclusion of generated columns by default, and non-evaluation of unselected cells. src/sqlite/SqliteDatabaseWorkerExecution.ts:63-69 states that each input rejection names the argument requiring correction. These references are at the tested baseline.

Impact and scope

P2, supported: the demonstrated selection-dependent limitation receives incorrect structured attribution and recovery guidance. Selecting only a returns both base rows, and schema-only inspection returns both column definitions. These are verified alternatives for the available data; obtaining b still requires a connection that supplies its function. No data loss, security boundary crossing, or general availability failure was observed. MCP and other platforms were not exercised; other generated-expression triggers remain untested.

REA version

rea-agents 6.7.0, source commit 9d8e7803326a382cbbb3ffdb900f5e93e5442d68, prepared built CLI. Rechecked fetched origin/main at 84c89d1107cda017509bc3cd851573d073f9906d: the cited documentation and SQLite inspection, worker, and error-mapping files are unchanged. Current-main applicability is source-inspected, not a separate runtime reproduction on main. The baseline reproduction was replayed during submission.

Runtime and platform

Node.js v26.12.0; macOS 27.0.1 arm64; stock SQLite shell 3.54.0, also reported by successful REA inspection. No external analysis provider is involved.

Target and artifact context

Synthetic ordinary SQLite table t with base column a and virtual generated column b, defined as sha3(a), containing two rows. This is an ordinary table with a generated column, not a virtual table. The generated values are 32-byte BLOBs despite the declared TEXT affinity. HOME, XDG roots, and the temporary runtime directory are isolated in the reproduction.

Interface

CLI

Earliest failure stage

Transport or result projection

Relevant output or evidence

Both failing selections exit 1 with this relevant subset on stdout:

{
  "code": "invalid_request",
  "category": "invalid_input",
  "retryable": false,
  "details": {
    "issues": [
      {
        "path": ["path"],
        "reason": "invalid_format",
        "message": "unknown function: sha3()"
      }
    ]
  },
  "remediation": {
    "action": "Read the declared input schema or CLI usage for inspect_sqlite_database; if using MCP and the catalog may be stale, call tools/list. Correct the listed fields, then retry. Do not repeat the unchanged call."
  }
}

Selecting only a returns columns: ["a"], returned_rows: 2, and text values hello and world. Schema-only inspection returns both column definitions with rows: null. The integrity check and successful shell query establish the contrasting result when the function is supplied. Evidence provenance: observed, independently reproduced and replayed at submission.

Typed outcome or recovery expected

Keep a typed refusal for the unavailable function, with selected-row evaluation context and actionable guidance to request readable columns separately. No extension loading, function registration, or partial success is required. Proposed restoration check: repeat both failing selections and require accurate attribution and recovery, while preserving schema-only and base-column success and the existing extension/schema-trust restrictions.

Analysis or suspected cause

Source-derived reasoning: src/sqlite/SqliteDatabaseInspection.ts:445-473 accepts the existing column name, then prepares a selected-column length preflight that requires sha3. The failure can occur during SQL preparation, before projected-row iteration. src/sqlite/SqliteDatabaseWorker.ts:31-44 classifies an otherwise unrecognized SQLite exception as format; src/sqlite/SqliteDatabaseWorkerExecution.ts:106-111 turns that reason into an invalid_format issue on path. This agrees with the observed selection-dependent error.

Additional context

Prepared with AI assistance and independently verified. No last-known-good version or successful fix was established.

Related reports cover different contracts: #2613 and merged PR #2774 concern nonexistent column names attributed to table; this column exists and passes that guard. #5520 and open PR #5564 add path context for genuinely malformed databases. #4184 and #5456 concern output budgets and recovery, and #4188 concerns filesystem path resolution. None covers attribution of an existing generated column's unavailable evaluation dependency.

Originally created by @coygeek on GitHub (Oct 20, 2026). Original GitHub issue: https://github.com/morluto/rea/issues/5719 Originally assigned to: @kaoru0822-kitauji on GitHub. ### Before submitting - [x] I searched the open and closed issues for a related report. - [x] I removed credentials, tokens, private paths, and sensitive target data. ### Area CLI ### Observed behavior Selecting a generated column from a readable SQLite database returns `invalid_request`, `invalid_format` on `path`, and guidance to correct the listed input fields. The same file returns its schema and base-column rows successfully. The generated column requires `sha3`, which the stock SQLite shell supplies but REA's connection does not. Refusing that evaluation is expected; blaming the database path and directing the caller to correct it is the defect. ### Reproduction Run from the source checkout below with locked dependencies installed and the CLI built. The stock `sqlite3` shell must provide `sha3`. This shell block was independently verified and replayed during submission: ```sh work_dir="$(mktemp -d)" mkdir -p "$work_dir/home" "$work_dir/cfg" "$work_dir/data" \ "$work_dir/cache" "$work_dir/state" "$work_dir/tmp" sqlite3 "$work_dir/hashes.db" <<'SQL' CREATE TABLE t (a TEXT, b TEXT GENERATED ALWAYS AS (sha3(a)) VIRTUAL); INSERT INTO t(a) VALUES ('hello'), ('world'); SQL sqlite3 "$work_dir/hashes.db" \ 'PRAGMA integrity_check; SELECT a, length(b) FROM t;' rea_snapshot() { env -i PATH="$PATH" HOME="$work_dir/home" \ XDG_CONFIG_HOME="$work_dir/cfg" XDG_DATA_HOME="$work_dir/data" \ XDG_CACHE_HOME="$work_dir/cache" XDG_STATE_HOME="$work_dir/state" \ TMPDIR="$work_dir/tmp" REA_LOG_LEVEL=silent \ node scripts/rea.mjs inspect-sqlite-database "$work_dir/hashes.db" "$@" --json } rea_snapshot --schema-scope database rea_snapshot --table t --column a rea_snapshot --table t --column b --column a rea_snapshot --table t ``` The SQLite control prints `ok`, `hello|32`, and `world|32`. Schema-only inspection and selecting `a` succeed. Explicitly selecting `b` and using the default all-column selection each fail with the same error excerpt below. Both failing commands have empty stderr. ### Expected behavior A selected-expression failure should identify the unavailable evaluation dependency and retain the selected table or column context. Recovery should explain that readable base columns can be selected separately. A usable database path should not be diagnosed as malformed because one generated value cannot be computed. `docs/sqlite-databases.md:57-69` documents exact column selection, inclusion of generated columns by default, and non-evaluation of unselected cells. `src/sqlite/SqliteDatabaseWorkerExecution.ts:63-69` states that each input rejection names the argument requiring correction. These references are at the tested baseline. ### Impact and scope P2, supported: the demonstrated selection-dependent limitation receives incorrect structured attribution and recovery guidance. Selecting only `a` returns both base rows, and schema-only inspection returns both column definitions. These are verified alternatives for the available data; obtaining `b` still requires a connection that supplies its function. No data loss, security boundary crossing, or general availability failure was observed. MCP and other platforms were not exercised; other generated-expression triggers remain untested. ### REA version `rea-agents` 6.7.0, source commit `9d8e7803326a382cbbb3ffdb900f5e93e5442d68`, prepared built CLI. Rechecked fetched `origin/main` at `84c89d1107cda017509bc3cd851573d073f9906d`: the cited documentation and SQLite inspection, worker, and error-mapping files are unchanged. Current-main applicability is source-inspected, not a separate runtime reproduction on main. The baseline reproduction was replayed during submission. ### Runtime and platform Node.js v26.12.0; macOS 27.0.1 arm64; stock SQLite shell 3.54.0, also reported by successful REA inspection. No external analysis provider is involved. ### Target and artifact context Synthetic ordinary SQLite table `t` with base column `a` and virtual generated column `b`, defined as `sha3(a)`, containing two rows. This is an ordinary table with a generated column, not a virtual table. The generated values are 32-byte BLOBs despite the declared TEXT affinity. HOME, XDG roots, and the temporary runtime directory are isolated in the reproduction. ### Interface CLI ### Earliest failure stage Transport or result projection ### Relevant output or evidence Both failing selections exit 1 with this relevant subset on stdout: ```json { "code": "invalid_request", "category": "invalid_input", "retryable": false, "details": { "issues": [ { "path": ["path"], "reason": "invalid_format", "message": "unknown function: sha3()" } ] }, "remediation": { "action": "Read the declared input schema or CLI usage for inspect_sqlite_database; if using MCP and the catalog may be stale, call tools/list. Correct the listed fields, then retry. Do not repeat the unchanged call." } } ``` Selecting only `a` returns `columns: ["a"]`, `returned_rows: 2`, and text values `hello` and `world`. Schema-only inspection returns both column definitions with `rows: null`. The integrity check and successful shell query establish the contrasting result when the function is supplied. Evidence provenance: observed, independently reproduced and replayed at submission. ### Typed outcome or recovery expected Keep a typed refusal for the unavailable function, with selected-row evaluation context and actionable guidance to request readable columns separately. No extension loading, function registration, or partial success is required. Proposed restoration check: repeat both failing selections and require accurate attribution and recovery, while preserving schema-only and base-column success and the existing extension/schema-trust restrictions. ### Analysis or suspected cause Source-derived reasoning: `src/sqlite/SqliteDatabaseInspection.ts:445-473` accepts the existing column name, then prepares a selected-column length preflight that requires `sha3`. The failure can occur during SQL preparation, before projected-row iteration. `src/sqlite/SqliteDatabaseWorker.ts:31-44` classifies an otherwise unrecognized SQLite exception as `format`; `src/sqlite/SqliteDatabaseWorkerExecution.ts:106-111` turns that reason into an `invalid_format` issue on `path`. This agrees with the observed selection-dependent error. ### Additional context Prepared with AI assistance and independently verified. No last-known-good version or successful fix was established. Related reports cover different contracts: #2613 and merged PR #2774 concern nonexistent column names attributed to `table`; this column exists and passes that guard. #5520 and open PR #5564 add path context for genuinely malformed databases. #4184 and #5456 concern output budgets and recovery, and #4188 concerns filesystem path resolution. None covers attribution of an existing generated column's unavailable evaluation dependency.

bsakaguchi closed this issue

2026-10-31 00:40:48 +00:00

bsakaguchi commented

2026-10-31 00:40:49 +00:00

Author

Owner

@matbrgz commented on GitHub (Oct 21, 2026):

Cross-referencing with canonical issue #4184 (SQLite provider leaves lock file on aborted schema inspection) and sibling SQLite diagnostic reports #5284, #5286, #5294, #5520.

Consolidated under Cluster AS (SQLite Provider Cleanup, Worker Failure & Diagnostic Attribution) in tracker #4330.

@matbrgz commented on GitHub (Oct 21, 2026): Cross-referencing with canonical issue #4184 (`SQLite provider leaves lock file on aborted schema inspection`) and sibling SQLite diagnostic reports #5284, #5286, #5294, #5520. Consolidated under **Cluster AS (SQLite Provider Cleanup, Worker Failure & Diagnostic Attribution)** in tracker #4330.

bsakaguchi commented

2026-10-31 00:40:49 +00:00

Author

Owner

@Xipong commented on GitHub (Oct 29, 2026):

Independent corroboration on installed rea-agents 6.7.0, Node 26.12.1, Linux x64/WSL via a separate target-free stdio MCP server. This is historical-version evidence, not a request to reopen an already-fixed issue.

Our synthetic ordinary table had a stored payload='not-json', then a VIRTUAL generated result using json_extract(payload,'$.x'). Inventory and a selection of columns:["payload"] succeeded. Selecting the generated value instead returned invalid_request with details.issues=[{path:["path"],reason:"invalid_format",message:"malformed JSON"}]. The source file's SHA-256 did not change.

This corroborates the row-evaluation case in addition to the missing-application-function trigger in the original report. I did not use PRAGMA integrity_check as a green oracle: it also evaluates this intentionally invalid virtual expression and returns SQLITE_ERROR, not SQLITE_CORRUPT.

I checked that #5879 is merged and inspected the relevant current-main error paths. The published 6.8.0 source has specialized evaluation handling too; I have not runtime-tested 6.8.0 or current main, so this comment does not claim a fresh fix PASS. No new issue is needed for this already-covered contract.

@Xipong commented on GitHub (Oct 29, 2026): Independent corroboration on **installed rea-agents 6.7.0, Node 26.12.1, Linux x64/WSL** via a separate target-free stdio MCP server. This is historical-version evidence, not a request to reopen an already-fixed issue. Our synthetic ordinary table had a stored `payload='not-json'`, then a VIRTUAL generated `result` using `json_extract(payload,'$.x')`. Inventory and a selection of `columns:["payload"]` succeeded. Selecting the generated value instead returned `invalid_request` with `details.issues=[{path:["path"],reason:"invalid_format",message:"malformed JSON"}]`. The source file's SHA-256 did not change. This corroborates the row-evaluation case in addition to the missing-application-function trigger in the original report. I did **not** use `PRAGMA integrity_check` as a green oracle: it also evaluates this intentionally invalid virtual expression and returns SQLITE_ERROR, not SQLITE_CORRUPT. I checked that #5879 is merged and inspected the relevant current-main error paths. The published 6.8.0 source has specialized evaluation handling too; I have **not** runtime-tested 6.8.0 or current main, so this comment does not claim a fresh fix PASS. No new issue is needed for this already-covered contract.

bsakaguchi referenced this issue

2026-10-31 01:09:12 +00:00

[PR #3467] [CLOSED] test(browser): cover WebMCP cleanup outcomes #5758

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

morluto/rea#3467

No description provided.

Rows
Columns