COALESCE returning the incorrect type

[Victugord]

Input:

SELECT t.id          taskID,
       t.created_at  created_at,
       t.updated_at  updated_at,
       t.amount      amount,
       u.id          creatorID,
       u.name        creatorName,

       c.id          clientID,
       c.name        clientName,
       p.id projectId,
       p.name projectName,
       COALESCE(
               NULLIF(p.id, 0), 0
           )  projectID,
       COALESCE(
               NULLIF(p.name, null), 'N/A'
           )  projectName
FROM tasks as t
         FULL OUTER JOIN projects as p on t.project_id = p.id AND COALESCE(p.id, 0) = p.id
         FULL OUTER JOIN users as u on t.user_id = u.id
         FULL OUTER JOIN users as c on t.client_id = c.id
where t.user_id = $1;

output:

SELECT t.id          taskID,
       t.created_at  created_at,
       t.updated_at  updated_at,
       t.amount      amount,
       u.id          creatorID,
       u.name        creatorName,

       c.id          clientID,
       c.name        clientName,
       COALESCE(
               NULLIF(p.id, 0), 0
           )  projectID,
       COALESCE(
               NULLIF(p.name, null), 'N/A'
           )  projectName
FROM tasks as t
         FULL OUTER JOIN projects as p on t.project_id = p.id AND COALESCE(p.id, 0) = p.id
         FULL OUTER JOIN users as u on t.user_id = u.id
         FULL OUTER JOIN users as c on t.client_id = c.id


type AdminGetTasksRow struct {
	Taskid      int32       `json:"taskid"`
	CreatedAt   time.Time   `json:"created_at"`
	UpdatedAt   time.Time   `json:"updated_at"`
	Amount      string      `json:"amount"`
	Creatorid   int32       `json:"creatorid"`
	Creatorname string      `json:"creatorname"`
	Clientid    int32       `json:"clientid"`
	Clientname  string      `json:"clientname"`
	Coalesce    interface{} `json:"coalesce"`
	Coalesce_2  interface{} `json:"coalesce_2"`
}

func (q *Queries) AdminGetTasks(ctx context.Context) ([]AdminGetTasksRow, error) {
	rows, err := q.query(ctx, q.adminGetTasksStmt, adminGetTasks)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []AdminGetTasksRow
	for rows.Next() {
		var i AdminGetTasksRow
		if err := rows.Scan(
			&i.Taskid,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.Amount,
			&i.Creatorid,
			&i.Creatorname,
			&i.Clientid,
			&i.Clientname,
			&i.Coalesce,
			&i.Coalesce_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
}

The alias is not being respected

[earthboundkid]

I have the same problem with this query:

-- name: ListPages :many
SELECT
  "id",
  "file_path",
  coalesce(frontmatter ->> 'internal-id', '') AS "internal_id",
  coalesce(frontmatter ->> 'title', '') AS "title",
  coalesce(frontmatter ->> 'description', '') AS "description",
  coalesce(frontmatter ->> 'blurb', '') AS "blurb",
  coalesce("url_path", ''),
  "last_published",
  "created_at",
  "updated_at",
  "schedule_for",
  to_timestamp(frontmatter ->> 'published',
    -- ISO date
    'YYYY-MM-DD"T"HH24:MI:SS"Z"')::timestamptz AS "published_at"
FROM
  page
WHERE
  "file_path" ILIKE $1
ORDER BY
  published_at DESC
LIMIT $2 OFFSET $3;

type ListPagesRow struct {
	ID            int64        `json:"id"`
	FilePath      string       `json:"file_path"`
	Coalesce      interface{}  `json:"coalesce"`
	Coalesce_2    interface{}  `json:"coalesce_2"`
	Coalesce_3    interface{}  `json:"coalesce_3"`
	Coalesce_4    interface{}  `json:"coalesce_4"`
	URLPath       string       `json:"url_path"`
	LastPublished sql.NullTime `json:"last_published"`
	CreatedAt     time.Time    `json:"created_at"`
	UpdatedAt     time.Time    `json:"updated_at"`
	ScheduleFor   sql.NullTime `json:"schedule_for"`
	PublishedAt   time.Time    `json:"published_at"`
}

It incorrectly omits the names of the named columns and incorrectly gives URLPath a name (it is unnamed in the query).

[markovichecha]

Bumping this one. One more example.
This SQL:

CREATE TABLE test (
  id INTEGER,
  text TEXT
);

-- name: GetTest :one
SELECT coalesce(json_build_array(json_build_object('id', id, 'text', text)), '[]'::json) AS json_arr FROM test;

Gets me:

func (q *Queries) GetTest(ctx context.Context) (interface{}, error) {
  row := q.db.QueryRowContext(ctx, getTest)
  var coalesce interface{}
  err := row.Scan(&coalesce)
  return coalesce, err
}

Tested with the latest main branch commit.

[earthboundkid]

You can do this as a workaround:

WITH temp_table AS (
    SELECT
        coalesce(json_build_array(json_build_object('id', id, 'text', text)), '[]'::json) AS json_arr
    FROM
        test
)
SELECT
    json_arr::json
FROM
    temp_table;

func (q *Queries) GetTest(ctx context.Context) (json.RawMessage, error) {
	row := q.db.QueryRowContext(ctx, getTest)
	var json_arr json.RawMessage
	err := row.Scan(&json_arr)
	return json_arr, err
}

[kyleconroy]

Reproduced here in v1.10.0: https://play.sqlc.dev/p/eb96c7d912914dff1e20bfc9b0d75a4b2e9bb7b3a8eb89117c226c5bc5442e04

[earthboundkid]

This is half-fixed by #1232. It still infers interface{}, but that's easy to fix by adding an explicit type.