Open
Description
Version
1.28.0
What happened?
I need to include an array of subobjects as children of a parent object. So i followed this stackoverflow answer and created a view that joins the elements and puts them together using JSON_ARRAYAGG. Although configured differently (engine: "mysql"), sqlc still introduces a pq.Array in line 13 in db/query.sql.go.
This fails and the result can not be unmarshalled correctly so an empty array is returned. I think this is because mysql (or mariadb in my case) return the element as string.
I think this is a bug and wanted to clarify that and also ask if someone knows a workaround for this.
Thanks in advance
Relevant log output
...
func (q *Queries) GetTest(ctx context.Context, testID int32) (TestV, error) {
row := q.db.QueryRowContext(ctx, getTest, testID)
var i TestV
err := row.Scan(&i.TestID, &i.Name, pq.Array(&i.Actions))
return i, err
}
...Database schema
CREATE TABLE test (
id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE test_a (
id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
test_id BIGINT NOT NULL
);
CREATE VIEW test_v AS
SELECT
p.id AS test_id,
p.name,
COALESCE(
JSON_ARRAYAGG(
JSON_OBJECT(
'action_id', a.id
)
), '[]'
) AS actions
FROM test p
LEFT JOIN test_a a ON a.test_id = p.id;
INSERT INTO test (id, name) VALUES (1, 'Test Process');SQL queries
-- name: GetTest :one
SELECT test_id, name, actions FROM test_v WHERE test_id = ?;Configuration
{
"version": "2",
"sql": [{
"schema": "schema.sql",
"queries": "query.sql",
"engine": "mysql",
"gen": {
"go": {
"out": "db",
"overrides": [{
"column": "test_v.actions",
"go_type": {
"type": "test_a",
"slice": true
}
}]
}
}
}]
}Playground URL
https://play.sqlc.dev/p/13ac0aed510fd4f5df0b56830f4e8fcfda3c1223aba0139453df688ddc11ed6c
What operating system are you using?
Linux
What database engines are you using?
MySQL
What type of code are you generating?
Go