[GH-ISSUE #6855] [Bug] SQLite inventory bind-variable limits label a healthy database malformed #4195

New issue

Closed

opened 2026-10-31 00:47:36 +00:00 by bsakaguchi · 0 comments

bsakaguchi commented

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

Owner

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

Before submitting

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

Area

Evidence, artifacts, or reconstruction

Observed behavior

A healthy one-column SQLite file is reported as malformed when schema_scope: "inventory" and a large repeated column selection exhaust the metadata query's bind variables. The production service returns invalid_request, path / invalid_format, and Selected SQLite database is invalid (too many SQL variables). Changing only the scope to selected_table returns the correct resource_constraint / capacity refusal for the same oversized selection.

This is an attribution defect, not a request to support projections beyond SQLite's limits.

Reproduction

Run this source probe with locked dependencies installed. Save it outside the checkout as /tmp/repro-inventory-bind-limit.mts, then run:

REA_REPRO_REPO="$PWD" node --import "$PWD/node_modules/tsx/dist/loader.mjs" /tmp/repro-inventory-bind-limit.mts

import { DatabaseSync } from 'node:sqlite';
import { createHash } from 'node:crypto';
import { mkdtempSync, readFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
const repo = process.env.REA_REPRO_REPO ?? fileURLToPath(new URL('../rea/', import.meta.url));
const { SqliteDatabaseProvider } = await import(`${repo}/src/sqlite/SqliteDatabaseProvider.ts`);
const { SqliteDatabaseService } = await import(`${repo}/src/application/sqlite/SqliteDatabaseService.ts`);
const { spawnOwnedProviderProcess } = await import(`${repo}/src/process/ProviderProcess.ts`);
const { projectAnalysisError } = await import(`${repo}/src/domain/analysisErrorProjection.ts`);
const path = `${mkdtempSync(fileURLToPath(new URL('./bind-repro-', import.meta.url)))}/sample.db`;
const db = new DatabaseSync(path);
db.exec("CREATE TABLE records(value TEXT); INSERT INTO records VALUES('kept');");
console.log(JSON.stringify({runtime:process.version,platform:process.platform,arch:process.arch,sqlite:db.prepare('SELECT sqlite_version() AS version').get(),integrity:db.prepare('PRAGMA integrity_check').get()}));
db.close();
const hash = () => createHash('sha256').update(readFileSync(path)).digest('hex');
const before = hash();
// Use the real source worker; replace only its built .js entry with TS loading.
const provider = new SqliteDatabaseProvider(process.env, spawn => spawnOwnedProviderProcess({
  ...spawn, arguments:[spawn.arguments[0],'--import',`${repo}/node_modules/tsx/dist/loader.mjs`,`${repo}/src/sqlite/SqliteDatabaseWorker.ts`,spawn.arguments[2]],
}));
const service = new SqliteDatabaseService(provider);
try {
  for (const [schema_scope,count] of [['inventory',1],['inventory',32767],['selected_table',32767]] as const) {
    const result = await service.inspect({path,table:'records',schema_scope,columns:Array(count).fill('value'),row_limit:1});
    console.log(JSON.stringify({schema_scope,count,result:result.ok?{ok:true,rows:result.value.normalized_result.rows}:{ok:false,error:projectAnalysisError(result.error)}}));
  }
} finally { await service.close(); }
console.log(JSON.stringify({unchanged_sha256:hash()===before}));

The self-contained probe ran once, after an exploratory snapshot/service probe established the same behavior. The one-column inventory control returned kept; the large inventory selection failed as below; the large selected-table control returned capacity. Integrity was ok and the source SHA-256 was unchanged. All requests passed the input schema in the exploratory probe.

Expected behavior

Attribute metadata-query capacity exhaustion to the selection/resource boundary, preserving the engine reason, rather than diagnosing a corrupt database path. The existing result-column capacity handling and worker recovery already establish this distinction.

Impact and scope

P2: this bounded, oversized but schema-valid selection produces incorrect structured attribution and recovery. It does not demonstrate source damage or a normal-sized request failure. For the fixture, reducing redundant copies to one preserves the requested value and succeeds. Real-world frequency and other runtimes/platforms are unknown.

REA version

Package 6.7.0; exact audited main source 96ea32b7e4e28d3c9cd79a1de4b8e1ba3b4244e9.

Runtime and platform

Node.js v26.12.1, Linux x64, built-in SQLite 3.54.0, locked tsx 4.23.15. This uses the supported Node 26+ line; pinned Node 24.22.1 was not tested.

Target and artifact context

Synthetic quiescent 8 KiB database: one ordinary table, one text column, one row; no WAL, views, generated expressions, extensions, or external targets.

Interface

Other

Earliest failure stage

Transport or result projection

Relevant output or evidence

Production-service result subset, with a generic artifact path:

{
  "code": "invalid_request",
  "category": "invalid_input",
  "details": {
    "issues": [{
      "path": ["path"],
      "reason": "invalid_format",
      "message": "Selected SQLite database is invalid (too many SQL variables): /artifacts/sample.db"
    }]
  },
  "recovery": {
    "kind": "inspect_input_contract",
    "operation": "inspect_sqlite_database"
  }
}

The same 32,767-name selection in selected_table scope returned resource_constraint with resource: "capacity" and engine_result_column_limit: 2000. Native limits were variableNumber: 32766 and column: 2000; inventory's extra table-name parameter also consumes a variable.

The probe uses the real provider, snapshot acquisition, supervised worker, reply parser, application service and error projection. Only the launcher's module-loading arguments change to execute immutable TypeScript source without building. Packaged CLI/MCP transport execution is unverified; their shared service and input contract make the request reachable by source inspection.

Typed outcome or recovery expected

A non-retryable capacity refusal identifying the column selection and engine limit, with guidance to reduce/split redundant selection rather than repair the healthy artifact.

Analysis or suspected cause

Source-derived: inventory passes selected columns into metadata lookup, which adds one bound placeholder per name. This fails before the row-projection catch that attributes column capacity. The worker fallback maps the generic SQLite error to format, then the parent labels the selected path invalid.

Additional context

AI-assisted research using local synthetic fixtures. Related #6292 concerns result-column projection capacity; that handling works in the controls. This report concerns the earlier inventory metadata bind-variable path, which bypasses it. Open/closed searches for "too many SQL variables", SQLite bind, and SQLite inventory columns found no report for this path. No last-known-good revision was established. Verification finished before this draft was written; it will not be rerun or sent for a second review afterward.

Triage assessment

Impact level: P2
Assessment status: supported
Impact basis: Observed production-service evidence mislabels a healthy SQLite file malformed when inventory metadata exhausts bind-variable capacity, producing incorrect structured recovery.
Workaround status: available
Workaround basis: The one-column inventory control returned the same fixture value successfully; removing redundant repeated selections preserves that value. Selected-table scope also produces the correct typed refusal for the oversized selection.

Originally created by @coygeek on GitHub (Oct 23, 2026). Original GitHub issue: https://github.com/morluto/rea/issues/6855 ## 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 Evidence, artifacts, or reconstruction ## Observed behavior A healthy one-column SQLite file is reported as malformed when `schema_scope: "inventory"` and a large repeated column selection exhaust the metadata query's bind variables. The production service returns `invalid_request`, `path / invalid_format`, and `Selected SQLite database is invalid (too many SQL variables)`. Changing only the scope to `selected_table` returns the correct `resource_constraint / capacity` refusal for the same oversized selection. This is an attribution defect, not a request to support projections beyond SQLite's limits. ## Reproduction Run this source probe with locked dependencies installed. Save it outside the checkout as `/tmp/repro-inventory-bind-limit.mts`, then run: ```sh REA_REPRO_REPO="$PWD" node --import "$PWD/node_modules/tsx/dist/loader.mjs" /tmp/repro-inventory-bind-limit.mts ``` ```ts import { DatabaseSync } from 'node:sqlite'; import { createHash } from 'node:crypto'; import { mkdtempSync, readFileSync } from 'node:fs'; import { fileURLToPath } from 'node:url'; const repo = process.env.REA_REPRO_REPO ?? fileURLToPath(new URL('../rea/', import.meta.url)); const { SqliteDatabaseProvider } = await import(`${repo}/src/sqlite/SqliteDatabaseProvider.ts`); const { SqliteDatabaseService } = await import(`${repo}/src/application/sqlite/SqliteDatabaseService.ts`); const { spawnOwnedProviderProcess } = await import(`${repo}/src/process/ProviderProcess.ts`); const { projectAnalysisError } = await import(`${repo}/src/domain/analysisErrorProjection.ts`); const path = `${mkdtempSync(fileURLToPath(new URL('./bind-repro-', import.meta.url)))}/sample.db`; const db = new DatabaseSync(path); db.exec("CREATE TABLE records(value TEXT); INSERT INTO records VALUES('kept');"); console.log(JSON.stringify({runtime:process.version,platform:process.platform,arch:process.arch,sqlite:db.prepare('SELECT sqlite_version() AS version').get(),integrity:db.prepare('PRAGMA integrity_check').get()})); db.close(); const hash = () => createHash('sha256').update(readFileSync(path)).digest('hex'); const before = hash(); // Use the real source worker; replace only its built .js entry with TS loading. const provider = new SqliteDatabaseProvider(process.env, spawn => spawnOwnedProviderProcess({ ...spawn, arguments:[spawn.arguments[0],'--import',`${repo}/node_modules/tsx/dist/loader.mjs`,`${repo}/src/sqlite/SqliteDatabaseWorker.ts`,spawn.arguments[2]], })); const service = new SqliteDatabaseService(provider); try { for (const [schema_scope,count] of [['inventory',1],['inventory',32767],['selected_table',32767]] as const) { const result = await service.inspect({path,table:'records',schema_scope,columns:Array(count).fill('value'),row_limit:1}); console.log(JSON.stringify({schema_scope,count,result:result.ok?{ok:true,rows:result.value.normalized_result.rows}:{ok:false,error:projectAnalysisError(result.error)}})); } } finally { await service.close(); } console.log(JSON.stringify({unchanged_sha256:hash()===before})); ``` The self-contained probe ran once, after an exploratory snapshot/service probe established the same behavior. The one-column inventory control returned `kept`; the large inventory selection failed as below; the large selected-table control returned capacity. Integrity was `ok` and the source SHA-256 was unchanged. All requests passed the input schema in the exploratory probe. ## Expected behavior Attribute metadata-query capacity exhaustion to the selection/resource boundary, preserving the engine reason, rather than diagnosing a corrupt database path. The existing [result-column capacity handling](https://github.com/morluto/rea/blob/96ea32b7e4e28d3c9cd79a1de4b8e1ba3b4244e9/src/sqlite/SqliteDatabaseInspection.ts#L492) and [worker recovery](https://github.com/morluto/rea/blob/96ea32b7e4e28d3c9cd79a1de4b8e1ba3b4244e9/src/sqlite/SqliteDatabaseWorkerExecution.ts#L138) already establish this distinction. ## Impact and scope P2: this bounded, oversized but schema-valid selection produces incorrect structured attribution and recovery. It does not demonstrate source damage or a normal-sized request failure. For the fixture, reducing redundant copies to one preserves the requested value and succeeds. Real-world frequency and other runtimes/platforms are unknown. ## REA version Package 6.7.0; exact audited main source `96ea32b7e4e28d3c9cd79a1de4b8e1ba3b4244e9`. ## Runtime and platform Node.js v26.12.1, Linux x64, built-in SQLite 3.54.0, locked tsx 4.23.15. This uses the supported Node 26+ line; pinned Node 24.22.1 was not tested. ## Target and artifact context Synthetic quiescent 8 KiB database: one ordinary table, one text column, one row; no WAL, views, generated expressions, extensions, or external targets. ## Interface Other ## Earliest failure stage Transport or result projection ## Relevant output or evidence Production-service result subset, with a generic artifact path: ```json { "code": "invalid_request", "category": "invalid_input", "details": { "issues": [{ "path": ["path"], "reason": "invalid_format", "message": "Selected SQLite database is invalid (too many SQL variables): /artifacts/sample.db" }] }, "recovery": { "kind": "inspect_input_contract", "operation": "inspect_sqlite_database" } } ``` The same 32,767-name selection in `selected_table` scope returned `resource_constraint` with `resource: "capacity"` and `engine_result_column_limit: 2000`. Native limits were `variableNumber: 32766` and `column: 2000`; inventory's extra table-name parameter also consumes a variable. The probe uses the real provider, snapshot acquisition, supervised worker, reply parser, application service and error projection. Only the launcher's module-loading arguments change to execute immutable TypeScript source without building. Packaged CLI/MCP transport execution is unverified; their shared service and input contract make the request reachable by source inspection. ## Typed outcome or recovery expected A non-retryable capacity refusal identifying the column selection and engine limit, with guidance to reduce/split redundant selection rather than repair the healthy artifact. ## Analysis or suspected cause Source-derived: inventory [passes selected columns into metadata lookup](https://github.com/morluto/rea/blob/96ea32b7e4e28d3c9cd79a1de4b8e1ba3b4244e9/src/sqlite/SqliteDatabaseInspection.ts#L412), which [adds one bound placeholder per name](https://github.com/morluto/rea/blob/96ea32b7e4e28d3c9cd79a1de4b8e1ba3b4244e9/src/sqlite/SqliteDatabaseInspection.ts#L208). This fails before the row-projection catch that attributes column capacity. The [worker fallback maps the generic SQLite error to format](https://github.com/morluto/rea/blob/96ea32b7e4e28d3c9cd79a1de4b8e1ba3b4244e9/src/sqlite/SqliteDatabaseWorker.ts#L53), then the [parent labels the selected path invalid](https://github.com/morluto/rea/blob/96ea32b7e4e28d3c9cd79a1de4b8e1ba3b4244e9/src/sqlite/SqliteDatabaseWorkerExecution.ts#L153). ## Additional context AI-assisted research using local synthetic fixtures. Related [#6292](https://github.com/morluto/rea/issues/6292) concerns result-column projection capacity; that handling works in the controls. This report concerns the earlier inventory metadata bind-variable path, which bypasses it. Open/closed searches for `"too many SQL variables"`, `SQLite bind`, and `SQLite inventory columns` found no report for this path. No last-known-good revision was established. Verification finished before this draft was written; it will not be rerun or sent for a second review afterward. ## Triage assessment - Impact level: P2 - Assessment status: supported - Impact basis: Observed production-service evidence mislabels a healthy SQLite file malformed when inventory metadata exhausts bind-variable capacity, producing incorrect structured recovery. - Workaround status: available - Workaround basis: The one-column inventory control returned the same fixture value successfully; removing redundant repeated selections preserves that value. Selected-table scope also produces the correct typed refusal for the oversized selection.

bsakaguchi closed this issue

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

bsakaguchi referenced this issue

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

[PR #4288] [CLOSED] fix(native): match the arm64e.v1 dwarfdump UUID row #6163