wolfenrain / wolfenrain/raindrop
Capped write renders LIMIT before RETURNING, which SQLite rejects
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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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