Open
Description
Version
1.22.0
What happened?
When performing a LEFT JOIN, it is expected that any column from the joined table can potentially be NULL. As a result, we expect the generated XXXRow struct to represent such columns as NullXXX or use pointers.
However, under specific conditions:
- Utilizing LEFT JOIN.
- Using a subquery within the LEFT JOIN clause.
- Having a column in the joined table with a user-defined type.
When these conditions are met, columns with user-defined types are not represented as NullXXX or similar. If the join results in a NULL for such columns, an error related to destination occurs.
It's worth noting that this issue can be circumvented by using the WITH clause; doing so avoids the problem entirely.
Relevant log output
No response
Database schema
CREATE TABLE authors (
id UUID PRIMARY KEY,
name text NOT NULL,
bio text
);
CREATE TABLE books (
id UUID PRIMARY KEY,
authors_id UUID,
name text NOT NULL,
foreign key (authors_id) REFERENCES authors (id)
);
CREATE TYPE channel_type as ENUM ('online', 'offline');
CREATE TABLE purchase_event (
id UUID PRIMARY KEY,
books_id UUID,
channel_type channel_type NOT NULL,
created_at timestamp,
foreign key (books_id) references books (id)
);SQL queries
-- name: SubQueryVer :many
SELECT
a.id AS author_id,
a.name AS author_name,
b.id AS book_id,
b.name AS book_name,
pe.channel_type,
pe.created_at AS last_purchase_date
FROM
authors a
LEFT JOIN
books b ON a.id = b.authors_id
LEFT JOIN (
SELECT
books_id,
channel_type,
created_at
FROM
purchase_event p1
WHERE
created_at = (
SELECT MAX(p2.created_at)
FROM purchase_event p2
WHERE p2.books_id = p1.books_id
)
) AS pe ON b.id = pe.books_id
ORDER BY
a.id;
-- name: WithClauseVer :many
WITH RankedPurchases AS (
SELECT
p.books_id,
p.channel_type,
p.created_at,
ROW_NUMBER() OVER (PARTITION BY b.authors_id ORDER BY p.created_at DESC) AS rn
FROM
purchase_event p
JOIN
books b ON p.books_id = b.id
)
SELECT
a.id AS author_id,
a.name AS author_name,
b.id AS book_id,
b.name AS book_name,
rp.channel_type,
rp.created_at AS last_purchase_date
FROM
authors a
LEFT JOIN
books b ON a.id = b.authors_id
LEFT JOIN
RankedPurchases rp ON b.id = rp.books_id
WHERE
rp.rn = 1 OR rp.rn IS NULL
ORDER BY
a.id;Configuration
{
"version": "1",
"packages": [
{
"path": "db",
"engine": "postgresql",
"schema": "query.sql",
"queries": "query.sql"
}
]
}Playground URL
https://play.sqlc.dev/p/e48a310e311252ac06c0967ff335d9277912dd5b2d75ba5b8f2e6bb0505985f0
What operating system are you using?
No response
What database engines are you using?
PostgreSQL
What type of code are you generating?
Go