MYSQL Driver wrongly handles JSON_ARRAYAGG in database view with pq.Array

[StillWaters77]

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.

Playground demonstration

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

[kyleconroy]

Most certainly a bug! My guess is that we hardcode the return type for JSON_AGG. Still, we should never generate a pq.Array type when using MySQL.

[edvinerikson]

Ran into this issue as well. Seems its hardcoded here at least: https://github.com/sqlc-dev/sqlc/blob/047d587dd7ec9ccd04c8a9930c2a599b607ac18f/internal/codegen/golang/imports.go#L401-L403