Repository navigation
classify() routes WITH...SELECT and INSERT...RETURNING to execute(): both fail, and RETURNING double-inserts on retry #38
Description
classify() routes query-vs-execute by first keyword, so two ordinary SQL forms never run at all — and one of them writes its row before failing, making a retry double-insert.
What fails
Verified against the real Turso backend, via ePHPm's ephpm_db_* bridge (and it affects the wire path identically):
| Statement | Result |
|---|---|
WITH … SELECT … |
routed to execute(), engine rejects it — never runs |
INSERT … RETURNING … |
routed to execute(), engine rejects it — never runs |
Root cause: classify() decides query vs execute from the statement's first keyword. WITH is not SELECT, so a CTE goes down the execute path. INSERT … RETURNING produces a rowset but starts with INSERT, so it goes down the execute path too.
The data-integrity part
INSERT … RETURNING writes its row before failing. The caller sees an error, retries, and inserts a second row. Any adapter with retry-on-error will silently double-insert.
Why it surfaced
Found while implementing ePHPm's has-rowset signal (ephpm/ephpm#263) — the goal there was to stop adapters from sniffing SQL keywords to decide whether a statement returns rows. The bridge now reads has-rowset from the executed result rather than the SQL text, but that only helps statements that execute; these two are rejected before they get that far.
Suggested direction
Route on what the statement actually produces rather than its leading keyword — either prepare-then-inspect (does the prepared statement declare result columns?), or extend the classifier to recognise WITH and a trailing RETURNING. The prepare-based approach is the durable one; keyword matching will keep losing to new syntax.
Downstream state
ePHPm has tests written to fail once this is fixed, so the fix will be noticed there:
WITH … SELECTandINSERT … RETURNINGare pinned as expected-failing- Documented as a known limitation in the db-bridge docs
Activity