morluto/rea

Fork 0

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

[GH-ISSUE #6292] [Bug] SQLite result-column limits report a healthy database as malformed #3806

New issue

Closed

opened 2026-10-31 00:44:15 +00:00 by bsakaguchi · 1 comment

bsakaguchi commented

2026-10-31 00:44:15 +00:00

Owner

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

Before submitting

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

Area

MCP or agent integration

Observed behavior

One focused test run reproduced the failure in all three over-limit calls: 2,001 selected columns twice and 2,100 once. Each returned invalid_request, category invalid_input, an issue on ["path"] with reason invalid_format, and Selected SQLite database is invalid (too many columns in result set). Recovery told the caller to correct the listed fields. The healthy file passed both native integrity checks, retained its digest, and remained readable through smaller selections and inventory. A separate malformed file received the same structured path/format classification with the appropriate file is not a database reason.

Reproduction

With the source checkout and its locked dependencies installed, build once with npm run build:cached. Save the following test as tests/acceptance/sqliteSelectionLimitRepro.test.ts. This test body ran once; the presented filename is neutralized from the verifier run. It uses the production stdio MCP server, not a substituted provider.

import { createHash } from "node:crypto";
import { readFile, writeFile } from "node:fs/promises";
import { join } from "node:path";
import { DatabaseSync } from "node:sqlite";
import { expect, it } from "vitest";
import { connectLocalToolsMcp } from "../fixtures/localToolsMcp.js";
import { parseMcpToolError } from "../fixtures/mcpToolError.js";
import { createTestTempDirectory } from "../fixtures/temporaryDirectory.js";

it("records selection-limit attribution with healthy and malformed controls", async () => {
  const root = await createTestTempDirectory("rea-selection-verifier-");
  const path = join(root, "selected.db");
  const db = new DatabaseSync(path);
  db.exec("CREATE TABLE records(id INTEGER, email TEXT, note TEXT); INSERT INTO records VALUES (1, 'sample', 'retained');");
  expect(db.prepare("PRAGMA integrity_check").get()).toMatchObject({ integrity_check: "ok" });
  const engine = db.prepare("SELECT sqlite_version() AS version").get();
  db.close();
  const hash = async () => createHash("sha256").update(await readFile(path)).digest("hex");
  const before = await hash();
  const environment = Object.fromEntries(
    ["HOME", "DSH_HOME", "XDG_CONFIG_HOME", "XDG_CACHE_HOME", "TMPDIR"].flatMap((key) => process.env[key] ? [[key, process.env[key]!]] : []),
  );
  const { call } = await connectLocalToolsMcp(environment);
  const observations: unknown[] = [{ engine, integrity_before: "ok", sha256_before: before }];
  for (const count of [2, 2000, 2001, 2001, 2100, 2]) {
    const response = await call("inspect_sqlite_database", {
      path, table: "records", columns: Array(count).fill("email"), row_limit: 1,
    });
    if (count <= 2000) {
      expect(response.isError).not.toBe(true);
      const content = response.structuredContent as any;
      expect(content.normalized_result.rows.columns).toHaveLength(count);
      expect(content.normalized_result.rows.values[0]).toHaveLength(count);
      expect(content.normalized_result.rows.values[0][0]).toMatchObject({ type: "text", value: "sample" });
      observations.push({ count, success: true, returned_rows: content.normalized_result.rows.returned_rows });
    } else {
      const error = parseMcpToolError(response).error;
      expect(error).toMatchObject({ code: "invalid_request", details: { issues: [{ path: ["path"], reason: "invalid_format", message: expect.stringContaining("Selected SQLite database is invalid (too many columns in result set)") }] } });
      observations.push({ count, error });
    }
  }
  const inventory = await call("inspect_sqlite_database", { path });
  expect(inventory.isError).not.toBe(true);
  observations.push({ inventory_success: true });
  const malformedPath = join(root, "malformed.db");
  await writeFile(malformedPath, "not a SQLite database".repeat(100));
  const malformed = parseMcpToolError(await call("inspect_sqlite_database", { path: malformedPath })).error;
  expect(malformed).toMatchObject({ code: "invalid_request", details: { issues: [{ path: ["path"], reason: "invalid_format", message: expect.stringContaining("file is not a database") }] } });
  const after = new DatabaseSync(path, { readOnly: true });
  expect(after.prepare("PRAGMA integrity_check").get()).toMatchObject({ integrity_check: "ok" });
  after.close();
  expect(await hash()).toBe(before);
  observations.push({ malformed_control: malformed, integrity_after: "ok", unchanged_sha256: true });
  console.log(JSON.stringify(observations, null, 2));
}, 60_000);

Run the focused test with isolated configuration and a single worker:

work_dir="$(mktemp -d)"
mkdir -p "$work_dir/home" "$work_dir/config" "$work_dir/cache" "$work_dir/tmp"
HOME="$work_dir/home" DSH_HOME="$work_dir/home" \
XDG_CONFIG_HOME="$work_dir/config" XDG_CACHE_HOME="$work_dir/cache" \
TMPDIR="$work_dir/tmp" \
node node_modules/vitest/vitest.mjs run tests/acceptance/sqliteSelectionLimitRepro.test.ts --maxWorkers=1

The verification test passes by asserting the current defective output. It issues eight MCP calls: 2 and 2,000 duplicate selections succeed; 2,001 fails twice and 2,100 fails once; another 2-column selection and inventory succeed; a malformed-file control produces a genuine format error. Integrity checks pass before and after, and the database SHA-256 stays unchanged.

Expected behavior

The oversized query may be refused at a genuine engine limit. The refusal must identify the selected columns/query capacity as the failing boundary, preserve the SQLite reason, and avoid classifying the readable database path as malformed. No guarantee that every selection size succeeds is assumed. Existing input correction metadata identifies the argument that requires correction, and the error algebra distinguishes resource failures from malformed input. Preserve the legitimate format refusal for the malformed-file control.

The worker correction mapping states that each input rejection identifies the argument requiring correction. The shared error algebra distinguishes resource failure from malformed input, and the SQLite failure documentation promises meaningful reasons. The absence of a maximum array length is not the basis for this expectation.

Impact and scope

P2, supported: a bounded selection-limit failure has wrong machine-readable attribution and recovery. Clients are told that the database path/format is invalid rather than that the projection exceeds an engine limit. Removing redundant duplicates is a safe workaround when it preserves the requested facts; 2 and 2,000 repeats both succeeded with the expected row. This does not establish that all requested wide projections can be returned. No source mutation, lost data or broad availability failure was observed. Real-world frequency, CLI execution and other hosts/runtimes were not tested.

REA version

Source commit 4a4bbab3c3 (main)

Runtime and platform

Node.js v26.12.1; macOS 27.0.1 arm64; built-in SQLite 3.54.1. The repository pins Node 24.18.0; this run used the documented supported 26+ line.

Target and artifact context

Synthetic quiescent SQLite database with one ordinary three-column table, one short row, no generated expressions and no external providers. Every over-limit selection repeats the existing exact column name email. Source integrity and byte identity were checked before and after.

Interface

MCP

Earliest failure stage

Transport or result projection

Relevant output or evidence

All three over-limit MCP calls returned this subset; the selected path below is replaced with a generic artifact path:

{
  "error": {
    "code": "invalid_request",
    "category": "invalid_input",
    "retryable": false,
    "details": {
      "issues": [{
        "path": ["path"],
        "reason": "invalid_format",
        "message": "Selected SQLite database is invalid (too many columns in result set): /artifacts/selected.db"
      }]
    },
    "recovery": {
      "kind": "inspect_input_contract",
      "operation": "inspect_sqlite_database"
    }
  }
}

The 2,000-copy control returned one row with 2,000 selected values. The 2-copy controls also returned the expected sample value. Both PRAGMA integrity_check calls returned ok; the source digest did not change. Schema inventory succeeded after the failed selections. The malformed control returned path/invalid_format with file is not a database. The test passed once with maxWorkers=1 and all temporary fixtures were released. This is independent observed MCP evidence; the code explanation below is source-derived.

Typed outcome or recovery expected

A refusal identifying the columns/query capacity, with the engine limit as its meaningful reason. It may remain a failure; it must not tell the caller to repair or replace a healthy database path. Repeat the same over-limit requests to verify corrected typed attribution while preserving the successful controls and genuine malformed-file refusal. No larger engine allowance, partial success or discarded requested columns is required.

Analysis or suspected cause

Source-derived: selected-row preparation creates a result expression for each requested name. The ordinary-column probes do not identify an individually failing generated expression, so the original SQL error survives. The worker fallback maps unrecognized errors to format, which its parent projects into the malformed-path issue shown above. The reproduction establishes the public error independently of this diagnosis.

Additional context

Related #5719 and merged PR #5879 fix generated-expression dependency failures; this input contains no generated expressions and hits a result-column limit instead. Merged PR #5564 adds selected-path context for genuinely malformed files. #4184/#4216 concern unrelated catalog-name reply budgets, and #4021 concerns index table spelling. Three bounded searches across open/closed issues and PRs found no exact duplicate: "too many columns in result set", "Selected SQLite database is invalid", and SqliteDatabaseWorker SQLITE_ERROR. Fetched main did not change the cited SQLite implementation or documentation; its relevant integer-schema allocation change does not fix this mapping. That freshness check is source-only. No known-good older version or real-world frequency was established.

Originally created by @coygeek on GitHub (Oct 22, 2026). Original GitHub issue: https://github.com/morluto/rea/issues/6292 ### 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 MCP or agent integration ### Observed behavior One focused test run reproduced the failure in all three over-limit calls: 2,001 selected columns twice and 2,100 once. Each returned `invalid_request`, category `invalid_input`, an issue on `["path"]` with reason `invalid_format`, and `Selected SQLite database is invalid (too many columns in result set)`. Recovery told the caller to correct the listed fields. The healthy file passed both native integrity checks, retained its digest, and remained readable through smaller selections and inventory. A separate malformed file received the same structured path/format classification with the appropriate `file is not a database` reason. ### Reproduction With the source checkout and its locked dependencies installed, build once with `npm run build:cached`. Save the following test as `tests/acceptance/sqliteSelectionLimitRepro.test.ts`. This test body ran once; the presented filename is neutralized from the verifier run. It uses the production stdio MCP server, not a substituted provider. ```ts import { createHash } from "node:crypto"; import { readFile, writeFile } from "node:fs/promises"; import { join } from "node:path"; import { DatabaseSync } from "node:sqlite"; import { expect, it } from "vitest"; import { connectLocalToolsMcp } from "../fixtures/localToolsMcp.js"; import { parseMcpToolError } from "../fixtures/mcpToolError.js"; import { createTestTempDirectory } from "../fixtures/temporaryDirectory.js"; it("records selection-limit attribution with healthy and malformed controls", async () => { const root = await createTestTempDirectory("rea-selection-verifier-"); const path = join(root, "selected.db"); const db = new DatabaseSync(path); db.exec("CREATE TABLE records(id INTEGER, email TEXT, note TEXT); INSERT INTO records VALUES (1, 'sample', 'retained');"); expect(db.prepare("PRAGMA integrity_check").get()).toMatchObject({ integrity_check: "ok" }); const engine = db.prepare("SELECT sqlite_version() AS version").get(); db.close(); const hash = async () => createHash("sha256").update(await readFile(path)).digest("hex"); const before = await hash(); const environment = Object.fromEntries( ["HOME", "DSH_HOME", "XDG_CONFIG_HOME", "XDG_CACHE_HOME", "TMPDIR"].flatMap((key) => process.env[key] ? [[key, process.env[key]!]] : []), ); const { call } = await connectLocalToolsMcp(environment); const observations: unknown[] = [{ engine, integrity_before: "ok", sha256_before: before }]; for (const count of [2, 2000, 2001, 2001, 2100, 2]) { const response = await call("inspect_sqlite_database", { path, table: "records", columns: Array(count).fill("email"), row_limit: 1, }); if (count <= 2000) { expect(response.isError).not.toBe(true); const content = response.structuredContent as any; expect(content.normalized_result.rows.columns).toHaveLength(count); expect(content.normalized_result.rows.values[0]).toHaveLength(count); expect(content.normalized_result.rows.values[0][0]).toMatchObject({ type: "text", value: "sample" }); observations.push({ count, success: true, returned_rows: content.normalized_result.rows.returned_rows }); } else { const error = parseMcpToolError(response).error; expect(error).toMatchObject({ code: "invalid_request", details: { issues: [{ path: ["path"], reason: "invalid_format", message: expect.stringContaining("Selected SQLite database is invalid (too many columns in result set)") }] } }); observations.push({ count, error }); } } const inventory = await call("inspect_sqlite_database", { path }); expect(inventory.isError).not.toBe(true); observations.push({ inventory_success: true }); const malformedPath = join(root, "malformed.db"); await writeFile(malformedPath, "not a SQLite database".repeat(100)); const malformed = parseMcpToolError(await call("inspect_sqlite_database", { path: malformedPath })).error; expect(malformed).toMatchObject({ code: "invalid_request", details: { issues: [{ path: ["path"], reason: "invalid_format", message: expect.stringContaining("file is not a database") }] } }); const after = new DatabaseSync(path, { readOnly: true }); expect(after.prepare("PRAGMA integrity_check").get()).toMatchObject({ integrity_check: "ok" }); after.close(); expect(await hash()).toBe(before); observations.push({ malformed_control: malformed, integrity_after: "ok", unchanged_sha256: true }); console.log(JSON.stringify(observations, null, 2)); }, 60_000); ``` Run the focused test with isolated configuration and a single worker: ```sh work_dir="$(mktemp -d)" mkdir -p "$work_dir/home" "$work_dir/config" "$work_dir/cache" "$work_dir/tmp" HOME="$work_dir/home" DSH_HOME="$work_dir/home" \ XDG_CONFIG_HOME="$work_dir/config" XDG_CACHE_HOME="$work_dir/cache" \ TMPDIR="$work_dir/tmp" \ node node_modules/vitest/vitest.mjs run tests/acceptance/sqliteSelectionLimitRepro.test.ts --maxWorkers=1 ``` The verification test passes by asserting the current defective output. It issues eight MCP calls: 2 and 2,000 duplicate selections succeed; 2,001 fails twice and 2,100 fails once; another 2-column selection and inventory succeed; a malformed-file control produces a genuine format error. Integrity checks pass before and after, and the database SHA-256 stays unchanged. ### Expected behavior The oversized query may be refused at a genuine engine limit. The refusal must identify the selected columns/query capacity as the failing boundary, preserve the SQLite reason, and avoid classifying the readable database path as malformed. No guarantee that every selection size succeeds is assumed. Existing input correction metadata identifies the argument that requires correction, and the error algebra distinguishes resource failures from malformed input. Preserve the legitimate format refusal for the malformed-file control. The [worker correction mapping](https://github.com/morluto/rea/blob/4a4bbab3c337701febeb6b532ace49041c4876e0/src/sqlite/SqliteDatabaseWorkerExecution.ts#L69) states that each input rejection identifies the argument requiring correction. The [shared error algebra](https://github.com/morluto/rea/blob/4a4bbab3c337701febeb6b532ace49041c4876e0/src/domain/analysisErrorCore.ts#L24) distinguishes resource failure from malformed input, and the [SQLite failure documentation](https://github.com/morluto/rea/blob/4a4bbab3c337701febeb6b532ace49041c4876e0/docs/sqlite-databases.md#L162) promises meaningful reasons. The absence of a maximum array length is not the basis for this expectation. ### Impact and scope P2, supported: a bounded selection-limit failure has wrong machine-readable attribution and recovery. Clients are told that the database path/format is invalid rather than that the projection exceeds an engine limit. Removing redundant duplicates is a safe workaround when it preserves the requested facts; 2 and 2,000 repeats both succeeded with the expected row. This does not establish that all requested wide projections can be returned. No source mutation, lost data or broad availability failure was observed. Real-world frequency, CLI execution and other hosts/runtimes were not tested. ### REA version Source commit 4a4bbab3c337701febeb6b532ace49041c4876e0 (main) ### Runtime and platform Node.js v26.12.1; macOS 27.0.1 arm64; built-in SQLite 3.54.1. The repository pins Node 24.18.0; this run used the documented supported 26+ line. ### Target and artifact context Synthetic quiescent SQLite database with one ordinary three-column table, one short row, no generated expressions and no external providers. Every over-limit selection repeats the existing exact column name `email`. Source integrity and byte identity were checked before and after. ### Interface MCP ### Earliest failure stage Transport or result projection ### Relevant output or evidence All three over-limit MCP calls returned this subset; the selected path below is replaced with a generic artifact path: ```json { "error": { "code": "invalid_request", "category": "invalid_input", "retryable": false, "details": { "issues": [{ "path": ["path"], "reason": "invalid_format", "message": "Selected SQLite database is invalid (too many columns in result set): /artifacts/selected.db" }] }, "recovery": { "kind": "inspect_input_contract", "operation": "inspect_sqlite_database" } } } ``` The 2,000-copy control returned one row with 2,000 selected values. The 2-copy controls also returned the expected `sample` value. Both `PRAGMA integrity_check` calls returned `ok`; the source digest did not change. Schema inventory succeeded after the failed selections. The malformed control returned `path`/`invalid_format` with `file is not a database`. The test passed once with maxWorkers=1 and all temporary fixtures were released. This is independent observed MCP evidence; the code explanation below is source-derived. ### Typed outcome or recovery expected A refusal identifying the columns/query capacity, with the engine limit as its meaningful reason. It may remain a failure; it must not tell the caller to repair or replace a healthy database path. Repeat the same over-limit requests to verify corrected typed attribution while preserving the successful controls and genuine malformed-file refusal. No larger engine allowance, partial success or discarded requested columns is required. ### Analysis or suspected cause Source-derived: [selected-row preparation](https://github.com/morluto/rea/blob/4a4bbab3c337701febeb6b532ace49041c4876e0/src/sqlite/SqliteDatabaseInspection.ts#L580) creates a result expression for each requested name. The ordinary-column probes do not identify an individually failing generated expression, so the original SQL error survives. The [worker fallback](https://github.com/morluto/rea/blob/4a4bbab3c337701febeb6b532ace49041c4876e0/src/sqlite/SqliteDatabaseWorker.ts#L33) maps unrecognized errors to `format`, which its parent projects into the malformed-path issue shown above. The reproduction establishes the public error independently of this diagnosis. ### Additional context Related #5719 and merged PR #5879 fix generated-expression dependency failures; this input contains no generated expressions and hits a result-column limit instead. Merged PR #5564 adds selected-path context for genuinely malformed files. #4184/#4216 concern unrelated catalog-name reply budgets, and #4021 concerns index table spelling. Three bounded searches across open/closed issues and PRs found no exact duplicate: `"too many columns in result set"`, `"Selected SQLite database is invalid"`, and `SqliteDatabaseWorker SQLITE_ERROR`. Fetched main did not change the cited SQLite implementation or documentation; its relevant integer-schema allocation change does not fix this mapping. That freshness check is source-only. No known-good older version or real-world frequency was established.

bsakaguchi closed this issue

2026-10-31 00:44:15 +00:00

bsakaguchi commented

2026-10-31 00:44:16 +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, using a separate target-free stdio MCP server. This confirms the old version's behavior, not a request to reopen this resolved issue.

The synthetic file had an ordinary settings(key,value) table. Inventory and a small stored-column read succeeded. Requesting table:"settings", schema_scope:"inventory", columns:Array(2001).fill("value"), row_limit:1 returned invalid_request, path:["path"], reason:"invalid_format", with too many columns in result set. The byte-identical source remained readable afterward.

I verified merged #6327 is the capacity fix; closed-unmerged #6388 is not being treated as the carrier. Current-main source and published 6.8.0 source contain the capacity-specific path. I have not executed current main/6.8.0 and do not claim their runtime acceptance from that source comparison. The native limits capability matters here: the older default Node 22 could start REA but could not perform this SQLite inspection; the actual reproduction used the existing Node 26 binary.

@Xipong commented on GitHub (Oct 29, 2026): Independent corroboration on **installed rea-agents 6.7.0, Node 26.12.1, Linux x64/WSL**, using a separate target-free stdio MCP server. This confirms the old version's behavior, not a request to reopen this resolved issue. The synthetic file had an ordinary `settings(key,value)` table. Inventory and a small stored-column read succeeded. Requesting `table:"settings", schema_scope:"inventory", columns:Array(2001).fill("value"), row_limit:1` returned `invalid_request`, `path:["path"]`, `reason:"invalid_format"`, with `too many columns in result set`. The byte-identical source remained readable afterward. I verified merged #6327 is the capacity fix; closed-unmerged #6388 is not being treated as the carrier. Current-main source and published 6.8.0 source contain the capacity-specific path. I have **not** executed current main/6.8.0 and do not claim their runtime acceptance from that source comparison. The native limits capability matters here: the older default Node 22 could start REA but could not perform this SQLite inspection; the actual reproduction used the existing Node 26 binary.

bsakaguchi referenced this issue

2026-10-31 01:10:23 +00:00

[PR #3806] [MERGED] fix(mcp): normalize compact schemas for strict gateways #5912

bsakaguchi referenced this issue

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

[GH-ISSUE #5654] [Tracker] Open PRs that can't merge yet: conflicts and red CI (October 2026) #3425