drizzle-team / drizzle-team/drizzle-orm

Set-operation orderBy() ignores casing config (pg + sqlite), producing a non-existent column reference

Open Beginner friendly
#5,987 1 comment 0 reactions 0 assignees View on GitHub
bug/fixed-in-beta
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Description
With `casing: 'snake_case'` (columns declared without an explicit DB name), ordering a set operation (`union`/`intersect`/`except`/…) by a column emits the JS property name in `ORDER BY` instead of the cased DB column name. The SELECT list is cased correctly, so `ORDER BY` references a column that isn't in the result set — the query fails at runtime (Postgres: `column "firstName" does not exist`). Affects **pg** (bare and `asc()`/`desc()`-wrapped) and **sqlite** (bare-column path). mysql/singlestore are unaffected.

### Steps to reproduce
```ts
import { pgTable, text, PgDialect, QueryBuilder } from 'drizzle-orm/pg-core';
import { asc, union } from 'drizzle-orm';

const users = pgTable('users', { firstName: text() }); // no explicit db name
const dialect = new PgDialect({ casing: 'snake_case' });
const qb = new QueryBuilder(dialect);

union(qb.select().from(users), qb.select().from(users)).orderBy(asc(users.firstName));
```
Generated SQL:
```
observed: (select "first_name" from "users") union (select "first_name" from "users") order by "firstName" asc
expected: (select "first_name" from "users") union (select "first_name" from "users") order by "first_name" asc
```
A plain `select().orderBy(asc(users.firstName))` is correct (`order by "users"."first_name" asc`); only set-operation ordering is affected.

### Root cause
`buildSetOperationQuery` emits the raw column `.name` instead of `casing.getColumnCasing(...)`:
- `drizzle-orm/src/pg-core/dialect.ts:483` (bare column) and `:489` (sql-wrapped chunk)
- `drizzle-orm/src/sqlite-core/dialect.ts:478` (bare column)

The correct pattern is already used in `mysql-core/dialect.ts:528,536`, `singlestore-core/dialect.ts`, and `sqlite-core/dialect.ts:485`.

### Suggested fix
Replace those three sites with `this.casing.getColumnCasing()` (the column object, matching mysql). After the change, all set-operation `ORDER BY` clauses emit `"first_name"`, and plain selects are unchanged.

Contributor guide

Open the contributing guide

Research direction

Start at buildSetOperationQuery in drizzle-orm/src/pg-core/dialect.ts (lines 483 and 489) and drizzle-orm/src/sqlite-core/dialect.ts (line 478), comparing the existing casing calls in mysql-core/dialect.ts and sqlite-core/dialect.ts. Verify set-operation ORDER BY output for snake_case columns matches the expected first_name SQL for bare and wrapped ordering, while plain selects remain unchanged.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, sqlite, typescript
Domain
databases
Issue type
Bug
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
82/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.