Repository navigation
LEFT JOIN can result in unsupported Scan #374
Description
When using LEFT JOINs with, for example, header/detail tables, the absence of any detail rows can cause the following:
sql: Scan error on column index 3, name \"purchase_order_id\": unsupported Scan, storing driver.Value type <nil> into type *string
Given the following schema...
CREATE TABLE purchase_order (
id TEXT NOT NULL PRIMARY KEY,
created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);
CREATE TABLE purchase_order_transaction (
purchase_order_id TEXT NOT NULL REFERENCES purchase_order(id),
description TEXT NOT NULL,
invoice_total INTEGER NOT NULL,
created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);and the following query...
-- name: SelectPurchaseOrder :many
SELECT po.*, tx.*
FROM purchase_order AS po
LEFT JOIN purchase_order_transaction AS tx
ON tx.purchase_order_id = po.id
WHERE po.id = $1
ORDER BY tx.created;sqlc produces the following code:
const selectPurchaseOrder = `-- name: SelectPurchaseOrder :many
SELECT po.id, po.created, po.updated, tx.purchase_order_id, tx.description, tx.invoice_total, tx.created
FROM purchase_order AS po
LEFT JOIN purchase_order_transaction AS tx
ON tx.purchase_order_id = po.id
WHERE po.id = $1
ORDER BY tx.created
`
type SelectPurchaseOrderRow struct {
ID string
Created time.Time
Updated time.Time
PurchaseOrderID string
Description string
InvoiceTotal int32
Created_2 time.Time
}
func (q *Queries) SelectPurchaseOrder(ctx context.Context, id string) ([]SelectPurchaseOrderRow, error) {
rows, err := q.db.QueryContext(ctx, selectPurchaseOrder, id)
if err != nil {
return nil, err
}
defer rows.Close()
var items []SelectPurchaseOrderRow
for rows.Next() {
var i SelectPurchaseOrderRow
if err := rows.Scan(
&i.ID,
&i.Created,
&i.Updated,
&i.PurchaseOrderID,
&i.Description,
&i.InvoiceTotal,
&i.Created_2,
); err != nil {
return nil, err
}
items = append(items, i)
}
if err := rows.Close(); err != nil {
return nil, err
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}It is my thinking that the PurchaseOrderID, Description, InvoiceTotal, and Created_2 fields on SelectPurchaseOrderRow should be nullable to account for the absence of any purchase_order_transaction rows.
Reacted by tadasv, Erik Lott, David Herrmann, Ryad-Marcel Lindner, Rayne, Yoenn Burban, Chris Bridgett, Philip Scott, Said Saifi, Joe Wilner and 1 more
Activity
kyleconroy commentedon Mar 4, 2020
@Oneymus thanks for the report. I reported this in #140 but forgot to open an issue for it.
davherrmann commentedon Oct 11, 2020
Just ran into this when doing a
FULL JOIN.INNER JOINmight be the only join not affected by this bug?randomswdev commentedon Mar 11, 2021
Is there any workaround for this issue?
johanbrandhorst commentedon Aug 16, 2021
Did #983 fix this?
johanbrandhorst commentedon Aug 16, 2021
In my brief testing of
v1.9.0this is not fixed. I have a query that currently usesCOALESCE(some_joined_field, '')to avoid errors when scanning, and updating tov1.9.0and removing theCOALESCEdid not correctly mark the field as nullable (and change the scanned variable type).kyleconroy commentedon Aug 28, 2021
@Etomyutikos this has been fixed on
main(and maybe in v1.9.0). sqlc now generats the following struct:Which seems to be exactly what you asked for.
@johanbrandhorst Can you please create a new issue for your bug report? Thanks
johanbrandhorst commentedon Aug 29, 2021
I've raised #1157, it seems my issue is specific to enums.