drizzle-team / drizzle-team/drizzle-orm
[BUG]: "undefined" column from a materialized view
- 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
Assessment
This issue has not been assessed yet.