Closed
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.