drizzle-team / drizzle-team/drizzle-orm

[BUG]: "undefined" column from a materialized view

Open
#3,856 5 comments 9 reactions 0 assignees View on GitHub
bug bug/fixed-in-beta db/postgres priority
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Report hasn't been filed before.

- [X] I have verified that the bug I'm about to report hasn't been filed before.

### What version of `drizzle-orm` are you using?

0.38.2

### What version of `drizzle-kit` are you using?

0.30.1

### Other packages

_No response_

### Describe the Bug

**What is the undesired behavior?**
The query to select from a materialized view won't be generated properly.

**What are the steps to reproduce it?**
This materialized view is generated properly as I could confirmed in raw PostgreSQL.

```
export const filteredItems = pgMaterializedView("filtered_items").as((qb) =>
qb
.select({
id: items.id,
name: items.name,
status: items.status,
currentPrice: items.currentPrice,
originalPrice: items.originalPrice,
itemUrl: items.itemUrl,
sharedLink: items.sharedLink,
title: titles.title,
})
.from(items)
.leftJoin(titles, eq(titles.id, items.titleId))
.where(eq(items.hasImg, true))
.orderBy(desc(items.createdAt)),
);
```

However, when querying a simple selection as below, it won't work because `title` column gets undefined in the generated query when either of `id`, `name` or `status` is selected with it.

```
const query = db
.select({
id: filteredItems.id,
name: filteredItems.name,
status: filteredItems.status,
title: filteredItems.title,
})
.from(filteredItems)
.limit(1);
console.log(query.toSQL());
```

Here's the generated query.

```
{
sql: 'select "id", "name", "status", "undefined" from "filtered_items" limit $1',
params: [ 1 ]
}
```

But `title` can be back when removing those three columns.

```
{ sql: 'select "title" from "filtered_items" limit $1', params: [ 1 ] }
```

The current workaround is replace `title: filteredItems.title` with `` title: sql`title` ``.

**What is the desired result?**
All columns are to be selected at once without workaround.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.