wolfenrain / wolfenrain/raindrop

Capped write renders LIMIT before RETURNING, which SQLite rejects

Open
#38 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Dart
Stars
7
Forks
0
Avg merge
16h 44m
Merged PRs (30d)
24

Description

What happens

On a SQLite build compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT, a capped write combined with .returning() renders the cap ahead of RETURNING:

DELETE FROM "users" WHERE "name" = $1 LIMIT 1 RETURNING "id", "name", ...

which fails to prepare:

SqliteException(1): while preparing statement, near "RETURNING": syntax error

SQLite's delete-stmt puts the returning-clause before ORDER BY/LIMIT. Checked against the library directly:

OK      : DELETE FROM t WHERE name = ? LIMIT 1
REJECTED: DELETE FROM t WHERE name = ? LIMIT 1 RETURNING id, name   -> near "RETURNING": syntax error
OK      : DELETE FROM t WHERE name = ? RETURNING id, name LIMIT 1

Why CI doesn't see it

LimitedWriteClause sits at the statement's where weight and emits the cap there; ReturningClause is at where + 5000, so the cap always lands first. That only matters in the bare-LIMIT form — the key-subquery form carries its cap inside the subquery, so ordering is moot.

Which form renders is decided by probeForLimitSupport, and the bundled SQLite in sqlite3 3.x does not enable SQLITE_ENABLE_UPDATE_DELETE_LIMIT, so CI always takes the subquery path. It reproduces on macOS, where sqlite3 2.x resolves to Apple's system SQLite (3.51.0, sourceId ...apl), which does enable the option — limited_write_test.dart's two returning cases fail there, and the WITHOUT ROWID test skips itself with "this library parses a bare DELETE ... LIMIT".

Affects

raindrop_sqlite, since #31. Both .limit().returning() on delete and on update.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with probeForLimitSupport, LimitedWriteClause, and ReturningClause to trace how capped writes are ordered. Run the two returning cases in limited_write_test.dart, including both delete and update paths. Done means bare LIMIT with RETURNING prepares successfully on SQLite builds that support it, while the subquery path remains correct.

Written by the indexing model from the issue text.

Assessment

Tech stack
dart
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
72/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.