fix(db): recognise count-less FETCH, FETCH ... WITH TIES, and preceding OFFSET in the query limiter - #1995

Description

In src/lib/db/utils/query-limiter.ts, the FETCH probe in analyzeUnderGrammar previously only matched FETCH {FIRST|NEXT} <count> ROW[S] ONLY. As a result:

1. Count-less standard SQL FETCH {FIRST|NEXT} ROW[S] ONLY statements were reported as unbounded (hasLimit: false), causing applyQueryLimit (used by SqlBaseProvider and CouchbaseProvider) to append LIMIT 500 and OracleProvider, Db2Provider, and MSSQLProvider prepareQuery paths to inject a second FETCH FIRST or TOP bound.
2. FETCH {FIRST|NEXT} [n] ROW[S] WITH TIES statements were reported as unbounded (hasLimit: false) and received a conflicting second bound.
3. Statements with a preceding OFFSET m [ROW|ROWS] before FETCH ... (such as OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY) reported hasOffset: false and existingOffset: undefined (appliedOffset: 0).

Per @cevheri's scope on #1930:

• Updated the end-anchored FETCH probe in analyzeUnderGrammar to match \b(?:OFFSET\s+(${value})\s+(?:ROWS?\s+)?)?FETCH\s+(?:FIRST|NEXT)\s+(?:(${value})\s+)?ROWS?\s+(?:ONLY|WITH\s+TIES)\s*$:
  • FETCH {FIRST|NEXT} ROW[S] {ONLY|WITH TIES} (omitted count) sets hasLimit: true and existingLimit: 1.
  • FETCH {FIRST|NEXT} <count> ROW[S] {ONLY|WITH TIES} sets hasLimit: true and existingLimit: boundCount(count).
  • A preceding OFFSET <m> [ROW|ROWS] sets hasOffset: true and existingOffset: boundOffset(m) (reported as appliedOffset: m by applyQueryLimit).
  • Matches case-insensitively and across arbitrary whitespace at the end of the outermost statement only (FETCH inside a subquery, string literal, or comment is not counted as the outer statement's bound).
• Left forceLimit untouched as requested.

Proof: Reproduction Script (Before vs. After)

import { analyzeQuery, applyQueryLimit } from "./src/lib/db/utils/query-limiter";

for (const sql of [
  "SELECT * FROM users FETCH FIRST ROW ONLY",
  "SELECT * FROM users FETCH NEXT ROW ONLY",
  "SELECT * FROM users FETCH NEXT ROWS ONLY",
  "SELECT * FROM users ORDER BY score DESC FETCH FIRST 10 ROWS WITH TIES",
  "SELECT * FROM users ORDER BY id FETCH FIRST ROW WITH TIES",
  "SELECT * FROM users OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY",
]) {
  console.log(sql);
  console.log("  analyzeQuery:", analyzeQuery(sql, "postgres"));
  console.log("  applyQueryLimit:", applyQueryLimit(sql, 500, 0, {}, "postgres"));
}

Before (main @ fb54531d)

SELECT * FROM users FETCH FIRST ROW ONLY
  analyzeQuery: { type: "SELECT", hasLimit: false, existingLimit: undefined, hasOffset: false, existingOffset: undefined, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users FETCH FIRST ROW ONLY LIMIT 500", wasLimited: true, originalLimit: undefined, appliedLimit: 500, appliedOffset: 0 }

SELECT * FROM users FETCH NEXT ROW ONLY
  analyzeQuery: { type: "SELECT", hasLimit: false, existingLimit: undefined, hasOffset: false, existingOffset: undefined, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users FETCH NEXT ROW ONLY LIMIT 500", wasLimited: true, originalLimit: undefined, appliedLimit: 500, appliedOffset: 0 }

SELECT * FROM users FETCH NEXT ROWS ONLY
  analyzeQuery: { type: "SELECT", hasLimit: false, existingLimit: undefined, hasOffset: false, existingOffset: undefined, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users FETCH NEXT ROWS ONLY LIMIT 500", wasLimited: true, originalLimit: undefined, appliedLimit: 500, appliedOffset: 0 }

SELECT * FROM users ORDER BY score DESC FETCH FIRST 10 ROWS WITH TIES
  analyzeQuery: { type: "SELECT", hasLimit: false, existingLimit: undefined, hasOffset: false, existingOffset: undefined, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users ORDER BY score DESC FETCH FIRST 10 ROWS WITH TIES LIMIT 500", wasLimited: true, originalLimit: undefined, appliedLimit: 500, appliedOffset: 0 }

SELECT * FROM users ORDER BY id FETCH FIRST ROW WITH TIES
  analyzeQuery: { type: "SELECT", hasLimit: false, existingLimit: undefined, hasOffset: false, existingOffset: undefined, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users ORDER BY id FETCH FIRST ROW WITH TIES LIMIT 500", wasLimited: true, originalLimit: undefined, appliedLimit: 500, appliedOffset: 0 }

SELECT * FROM users OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY
  analyzeQuery: { type: "SELECT", hasLimit: true, existingLimit: 20, hasOffset: false, existingOffset: undefined, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY", wasLimited: false, originalLimit: 20, appliedLimit: 20, appliedOffset: 0 }

After (this PR)

SELECT * FROM users FETCH FIRST ROW ONLY
  analyzeQuery: { type: "SELECT", hasLimit: true, existingLimit: 1, hasOffset: false, existingOffset: undefined, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users FETCH FIRST ROW ONLY", wasLimited: false, originalLimit: 1, appliedLimit: 1, appliedOffset: 0 }

SELECT * FROM users FETCH NEXT ROW ONLY
  analyzeQuery: { type: "SELECT", hasLimit: true, existingLimit: 1, hasOffset: false, existingOffset: undefined, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users FETCH NEXT ROW ONLY", wasLimited: false, originalLimit: 1, appliedLimit: 1, appliedOffset: 0 }

SELECT * FROM users FETCH NEXT ROWS ONLY
  analyzeQuery: { type: "SELECT", hasLimit: true, existingLimit: 1, hasOffset: false, existingOffset: undefined, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users FETCH NEXT ROWS ONLY", wasLimited: false, originalLimit: 1, appliedLimit: 1, appliedOffset: 0 }

SELECT * FROM users ORDER BY score DESC FETCH FIRST 10 ROWS WITH TIES
  analyzeQuery: { type: "SELECT", hasLimit: true, existingLimit: 10, hasOffset: false, existingOffset: undefined, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users ORDER BY score DESC FETCH FIRST 10 ROWS WITH TIES", wasLimited: false, originalLimit: 10, appliedLimit: 10, appliedOffset: 0 }

SELECT * FROM users ORDER BY id FETCH FIRST ROW WITH TIES
  analyzeQuery: { type: "SELECT", hasLimit: true, existingLimit: 1, hasOffset: false, existingOffset: undefined, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users ORDER BY id FETCH FIRST ROW WITH TIES", wasLimited: false, originalLimit: 1, appliedLimit: 1, appliedOffset: 0 }

SELECT * FROM users OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY
  analyzeQuery: { type: "SELECT", hasLimit: true, existingLimit: 20, hasOffset: true, existingOffset: 10, isUnion: false, hasCTE: false, hasSubquery: false }
  applyQueryLimit: { sql: "SELECT * FROM users OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY", wasLimited: false, originalLimit: 20, appliedLimit: 20, appliedOffset: 10 }

Type of Change

• Bug fix (non-breaking change which fixes an issue)
• New feature (non-breaking change which adds functionality)
• Breaking change (fix or feature that would cause existing functionality to not work as expected)
• Documentation update
• Code refactoring
• Performance improvement
• Test addition or update

Related Issue

Closes #1930

Changes Made

• src/lib/db/utils/query-limiter.ts: Update analyzeUnderGrammar's FETCH probe to recognise optional preceding OFFSET m [ROW|ROWS], omitted row counts (existingLimit = 1), and WITH TIES alongside ONLY.
• tests/unit/db/query-limiter.test.ts: Add unit tests covering each row of the issue table across [undefined, "postgres", "duckdb", "oracle", "db2", "mssql"], case and spacing variations, and negative cases (FETCH inside a subquery, string literal, line comment, or block comment) for both analyzeQuery and applyQueryLimit.
• tests/integration/db/oracle-provider.test.ts: Add prepareQuery tests verifying that none of the FETCH / OFFSET ... FETCH variants receive a second FETCH FIRST bound, while FETCH inside a subquery, string literal, or comment still gets bounded.
• tests/integration/db/db2-provider.test.ts: Add prepareQuery tests verifying the same positive and negative cases for Db2.
• tests/integration/db/mssql-provider.test.ts: Add prepareQuery tests verifying that none of the FETCH / OFFSET ... FETCH variants have TOP spliced into them, while FETCH inside a subquery, string literal, or comment still gets TOP injected.

Testing

• I have tested this locally

• I have added/updated tests

• All existing tests pass

• bun test tests/unit/db/query-limiter.test.ts tests/integration/db/oracle-provider.test.ts tests/integration/db/db2-provider.test.ts tests/integration/db/mssql-provider.test.ts: 1,062 pass, 0 fail (3,580 expect() calls), 100.00% line coverage on src/lib/db/utils/query-limiter.ts.

• Verified live against PostgreSQL (SELECT * FROM users FETCH FIRST ROW ONLY;) in LibreDB Studio before and after the fix.

Test Environment

• LibreDB Studio Version: 1.1.0 (main @ fb54531d)
• Browser: Chromium (Desktop)
• OS: Windows 11 / Ubuntu 24.04 LTS (WSL2)
• Node.js/Bun Version: Bun 1.4.2 / Node.js v22.22.2
• Database Type: PostgreSQL 16.15 (live), plus unit/integration suites for PostgreSQL, DuckDB, Oracle, Db2, and SQL Server

Screenshots (if applicable)

Before (SELECT * FROM users FETCH FIRST ROW ONLY; in Studio against real PostgreSQL)

Studio appends LIMIT 501 after FETCH FIRST ROW ONLY, and PostgreSQL rejects the query with syntax error at or near "LIMIT":

After (SELECT * FROM users FETCH FIRST ROW ONLY; in Studio against real PostgreSQL)

Studio recognises FETCH FIRST ROW ONLY as already bounded (existingLimit: 1) and executes the query unchanged, returning 1 row:

Checklist

• My code follows the project's code style guidelines
• I have performed a self-review of my code
• I have commented my code, particularly in hard-to-understand areas
• I have updated the documentation accordingly
• My changes generate no new warnings
• I have added tests that prove my fix is effective or that my feature works
• New and existing unit tests pass locally with my changes
• The required CI test job passes the 100% line-coverage gate (bun run test:coverage and bun run coverage:check)
• If I changed src/lib/db/providers/, I updated the matching docs/providers/ documentation and tests/integration/db/ tests in the same PR (provider triad)
• If the type-id has a non-null AGENT_STATEMENT_LANGUAGE row, I kept its agent leg in step: the engine overlay under src/lib/agent/languages/<statement-language>/engines/, the Agent language pack subsection of its provider document, and tests/unit/lib/agent/engine-docs.test.ts
• Any dependent changes have been merged and published

Additional Notes

• forceLimit behavior in applyQueryLimit is intentionally left unchanged per maintainer scope on [BUG] Query limiter corrupts count-less FETCH FIRST ROW ONLY, FETCH ... WITH TIES, and forceLimit on FETCH clauses into invalid SQL #1930.