SQL engine: silent-wrong results (offset-past-end, date literals, ROW_NUMBER) + unsupported constructs 500 not 400

@kris

Summary

Three related SQL-engine correctness/observability defects, aggregated into one issue (the SQL engine is under active rewrite). All are silent-wrong-result or wrong-status behaviors in the AlaSQL-backed two-pass executor (dataLayer/SQLSearch.ts). All reproduce on both storage engines.

A. `LIMIT N OFFSET M` returns the ENTIRE table when `OFFSET ≥ rowcount` (silent-wrong + over-fetch)

A query with an out-of-range offset returns all rows (LIMIT silently dropped) instead of an empty page. Verified: offsets 5000/5250/6000/10000 on a 5000-row table all returned 5000 rows; in-range offsets are correct and disjoint.

SELECT * FROM data.T ORDER BY k LIMIT 50 OFFSET 1000000   -- on a small table -> returns the WHOLE table

Impact: breaks the universal "page until a short/empty result, then stop" idiom (a naive loop never terminates / reprocesses everything), and over-fetches the whole table into a response a client capped at N.
Root cause: the two-pass executor applies LIMIT/OFFSET in the first pass, then _finalSQL (≈L1206-1217) deletes statement.limit/statement.offset so they aren't re-applied in the merge pass. When the first pass matches nothing (offset past end), the final pass runs over __mergedData with no cap and re-emits everything.
Fix: preserve an empty result when the first pass yields zero rows due to an out-of-range offset (don't drop LIMIT on the re-emit), or re-apply LIMIT/OFFSET in the merge pass.

B. Date-column filtering by a literal silently returns `[]`

On a Date-typed column, SQL filtering by a date literal silently returns the empty set:

SELECT * FROM data.T WHERE when = '2026-06-18T12:00:00.000Z'   -- -> []  (rows exist)
SELECT * FROM data.T WHERE when < '2026-06-18T12:00:00.000Z'   -- -> []  (string literal)
SELECT * FROM data.T WHERE when < 1750000000000                 -- -> rows (numeric epoch works)

Only numeric-epoch bounds match; ISO-string and exact-epoch equality both yield []. REST FIQL (?when=ge=date:<ISO>) filters correctly, and ORDER BY on the column is correctly chronological — so the data/index are fine; it's the SQL literal→Date comparison path. A SQL user querying a date column by the obvious literal believes "no matching rows" when rows exist.

Fix: coerce SQL date literals (ISO string / epoch) to the stored Date representation before matching, or reject an un-coercible date predicate loudly.

C. `ROW_NUMBER() OVER (...)` silently ignores its OVER clause → wrong rankings; unsupported constructs return 500 not 400

SELECT name, ROW_NUMBER() OVER (PARTITION BY deptId ORDER BY salary DESC) AS rn
FROM data.Employee WHERE deptId=1
-- -> 200, but ranks assigned in physical/id-scan order, ignoring the OVER ORDER BY (silently wrong)

The column classifier keys only on aggregatorid/funcid (SQLSearch.ts ~L372-402), so ROW_NUMBER is treated as a plain scalar and its PARTITION/ORDER BY is dropped.

Companion observability gap (same engine): of 13 advanced constructs probed, only DISTINCT and HAVING are supported; the other 10 (correlated/uncorrelated subqueries, IN/NOT IN (SELECT), UNION/UNION ALL, RANK, CASE WHEN, derived table in FROM) return HTTP 500 with internal-sounding messages (Circular reference, unknown column …, schema not defined for table undefined) instead of a clean 400 "construct not supported". Source: bare-string / new Error(SEARCH_ERROR_MSG) throws (SQLSearch.ts L123-171/480-484, SelectValidator.ts L87/252/311) → ops API maps to 500. (Same systemic 4xx-as-500 pattern noted in #1299.) CASE WHEN being unsupported is itself notable.

Fix: reject window functions loudly (400) until OVER is honored (or honor it); map unsupported-construct/validator failures to 400 with a clear message.

Scope note (clean, for contrast)

JOINs (inner/left-null-fill/self/3-way/aggregate), numeric range/BETWEEN boundaries (Int/Long/Float, inclusive/exclusive, reversed→empty), and GROUP BY aggregates are all correct against hand-computed truth on both engines — the silent-wrong issues are specifically: out-of-range offset, date literals, and window functions.

Surfaced by the QA-explorer campaign against Harper 7aaa5a152. dataLayer/SQLSearch.ts is byte-identical to main @6797f091d, so all three reproduce on main. Ready-to-promote regression tests exist for each. Filed by Claude (Opus 4.8) for @kris.

@kris

Additional SQL-engine correctness defects (QA-explorer, aggregating here per the rewrite)

Continuing to fold SQL findings into this umbrella (SQL engine under active rewrite). All below are silent-wrong-result or wrong-status in the AlaSQL-backed executor, found by the QA-explorer campaign. (Per the note in the original description, dataLayer/SQLSearch.ts tracks main, so these are expected to reproduce on current main.)

D. `WHERE NOT (...)` predicate handling is broadly broken — HIGH

Nearly every parenthesized-NOT predicate form silently returns wrong or empty results (HTTP 200, no error). Full matrix: 16 of 24 forms fail, both engines identical, indexed == non-indexed (so it's WHERE-AST processing, not storage).

Broken (silent 200, wrong/empty):

NOT (col = v) → 0 rows (every type)
NOT (col != v) / NOT (col <> v) → wrong non-zero (complement-of-complement)
NOT (col > v), >=, <, <= → 0 rows (all four comparators)
NOT (a=1 AND b=2) → returns the AND-match rows, not the complement (e.g. 144 vs expected 636)
NOT (a=1 OR b=2) → wrong (NULL handling)
NOT (col IN (...)) → 0 rows
NOT (col LIKE 'a%') → leaks extra NULL rows

Correct (for contrast): plain col != v (never NOT-wrapped); NOT (NOT (col=v)) (double-negation cancels); native col NOT IN (...) (separate parser path); NOT (col IS NULL) / NOT (col IS NOT NULL).

Root cause: the index pre-filter (_extractWhere, ≈SQLSearch.ts:232) descends into the inner predicate and builds a candidate set from it, but never accounts for the enclosing NOT — the negation is applied to an already-narrowed pool. Fix: add NOT to the construct-ignore/total-scan guard so a negated predicate forces a full candidate set (until handled properly).

E. `LIMIT 0` returns the entire table

Companion to section A (offset-past-end). SELECT ... LIMIT 0 returns all rows instead of an empty page — same root family (LIMIT dropped on the merge-pass re-emit). Both engines.

F. `CASE WHEN ... ELSE ...` omits the aliased column for ELSE-branch rows

When a CASE expression executes (200), rows that hit the WHEN branch get the right value, but rows hitting the ELSE branch have the aliased column entirely absent from the result row — not the ELSE value, not null. Silent-wrong in a common construct. (Distinct from the "CASE WHEN unsupported → 500" note in section C; here it parses and runs but drops the ELSE projection.)

G. String/numeric function gaps

SUBSTRING uses UTF-16-unit offsets and splits surrogate pairs: SUBSTRING('😀hello',1,1) → lone high surrogate \uD83D (malformed string).
NULL mishandled vs SQL-standard passthrough: UPPER(NULL) → the string "NULL"; REPLACE(NULL,…) → ""; SUBSTRING(NULL,…) / TRIM(NULL) → 500. Unary numeric fns ABS/CEIL/FLOOR/POWER/SQRT(NULL) → 0 (not null; ROUND(NULL) correctly returns null). MOD() and the POW() alias → 500 (unsupported; POWER works).

H. `LIKE ... ESCAPE` is a silent no-op

LIKE '50\%' ESCAPE '\' is accepted (200) but the escape char is ignored, so a literal %/_ can never be matched → wrong (usually empty) results, no error.

Adjacent (cross-referenced from other issues)

SQL INSERT duplicate-PK under concurrency = silent last-write-wins (30/30 concurrent same-key inserts all return 200, last wins, no conflict detection). The cross-surface create-if-absent gap is tracked on Optimistic concurrency (ifVersion) conflict-detection leaks under concurrent same-key writes (LMDB severe, RocksDB mild) #1288; noting the SQL leg here.
SQL SUM over Long accumulates in a JS double and drifts past 2^53 (e.g. 200 × MAX_SAFE_INTEGER → result off by −100). The column-bounds root is tracked on addTo/subtractFrom accumulation bypasses declared column type bounds (Int 32-bit, Long ±2^53) at commit-merge #1372; noting the SQL-accumulator leg here.

Repros (gitignored qa-scratch): qa290-* (NOT matrix), qa291-* (LIKE), qa281/284-* (string/numeric fns), qa245-* (LIMIT), qa251-* (CASE).

QA-explorer campaign. Filed for @kris.

— Claude (Opus 4.8, 1M)

@heskew

Additional evidence on A (LIMIT N OFFSET M returning the entire table when OFFSET >= rowcount): still reproduces, and the new SQL engine does not close it.

Reproduced on harperfast/harper-pro 5.1.14, 5.1.23, and 5.2.0-beta.2. On a 300-row table:

SELECT id FROM t.r LIMIT 50 OFFSET 250   ->  50 rows   ok
SELECT id FROM t.r LIMIT 50 OFFSET 299   ->   1 row    ok
SELECT id FROM t.r LIMIT 50 OFFSET 300   -> 300 rows   <- entire table
SELECT id FROM t.r LIMIT 50 OFFSET 301   -> 300 rows
SELECT id FROM t.r LIMIT 50 OFFSET 5000  -> 300 rows

Identical with and without ORDER BY, so it is not the index-order path.

Two things worth adding for whoever picks this up:

The phase-5 cutover does not fix it. sqlEngine/config.ts defaults engine: 'auto' as of 8b43d13, and that default is present in 5.2.0-beta.2 — yet the behaviour persists, so either the new engine falls back to legacy for this shape or it shares the defect. Anyone assuming the rewrite supersedes the legacy SQL bugs should know at least this one survives the flip.
main behaves the same as beta.2. git diff v5.2.0-beta.2..origin/main -- sqlEngine/ dataLayer/SQLSearch.ts is empty.

Practical impact, in case it helps prioritisation: this bit a migration-verification harness that paged a table to completion with LIMIT/OFFSET. The loop never terminated — the past-the-end page returned the full table, so the batch length never fell below the page size and the "page until short/empty" terminator never fired. Worked around by bounding the walk to a known row count and never issuing an out-of-range OFFSET. On a large table that same page also attempts to return the entire table in one response.

🤖 Posted by Claude Opus 5 on behalf of @heskew

From dispatch finding (task qa-wave-2026072115):

SQL UNION ALL returns HTTP 200 but silently DROPS the second branch's row — a silent wrong result on a clean dataset with default config, independent of Secondary-index reads return partial/empty results for the second table accessed in a single request (5.1.19 and 5.1.22) #1881; standing policy routes SQL findings to umbrella SQL engine: silent-wrong results (offset-past-end, date literals, ROW_NUMBER) + unsupported constructs 500 not 400 #1397 rather than a standalone issue, so treat this as a comment on that umbrella unless you want it tracked separately

Navigation Menu

Repository navigation

SQL engine: silent-wrong results (offset-past-end, date literals, ROW_NUMBER) + unsupported constructs 500 not 400 #1397

Summary

A. `LIMIT N OFFSET M` returns the ENTIRE table when `OFFSET ≥ rowcount` (silent-wrong + over-fetch)

B. Date-column filtering by a literal silently returns `[]`

C. `ROW_NUMBER() OVER (...)` silently ignores its OVER clause → wrong rankings; unsupported constructs return 500 not 400

Scope note (clean, for contrast)

Additional SQL-engine correctness defects (QA-explorer, aggregating here per the rewrite)

D. `WHERE NOT (...)` predicate handling is broadly broken — HIGH

E. `LIMIT 0` returns the entire table

F. `CASE WHEN ... ELSE ...` omits the aliased column for ELSE-branch rows

G. String/numeric function gaps

H. `LIKE ... ESCAPE` is a silent no-op

Adjacent (cross-referenced from other issues)

Metadata

Assignees

Labels

Type

Fields

Projects

Milestone

Relationships

Development

SQL engine: silent-wrong results (offset-past-end, date literals, ROW_NUMBER) + unsupported constructs 500 not 400 #1397

Description

Summary

A. LIMIT N OFFSET M returns the ENTIRE table when OFFSET ≥ rowcount (silent-wrong + over-fetch)

B. Date-column filtering by a literal silently returns []

C. ROW_NUMBER() OVER (...) silently ignores its OVER clause → wrong rankings; unsupported constructs return 500 not 400

Scope note (clean, for contrast)

Activity

kriszyp commented on Jun 24, 2026

Additional SQL-engine correctness defects (QA-explorer, aggregating here per the rewrite)

D. WHERE NOT (...) predicate handling is broadly broken — HIGH

E. LIMIT 0 returns the entire table

F. CASE WHEN ... ELSE ... omits the aliased column for ELSE-branch rows

G. String/numeric function gaps

H. LIKE ... ESCAPE is a silent no-op

Adjacent (cross-referenced from other issues)

heskew commented on Jul 30, 2026

kriszyp commented on Aug 26, 2026

Metadata

Metadata

Assignees

Labels

Type

Fields

Projects

Milestone

Relationships

Development

Issue actions

A. `LIMIT N OFFSET M` returns the ENTIRE table when `OFFSET ≥ rowcount` (silent-wrong + over-fetch)

B. Date-column filtering by a literal silently returns `[]`

C. `ROW_NUMBER() OVER (...)` silently ignores its OVER clause → wrong rankings; unsupported constructs return 500 not 400

D. `WHERE NOT (...)` predicate handling is broadly broken — HIGH

E. `LIMIT 0` returns the entire table

F. `CASE WHEN ... ELSE ...` omits the aliased column for ELSE-branch rows

H. `LIKE ... ESCAPE` is a silent no-op