JSONB "-- name:XXX :one" Get wrong type. many action is ok.

[piyongcai-liucai]

Version

1.27.0

What happened?

A bug happened!

1. database: postgres 16.x

2. custom type

type FundSummary struct {
	// ...
        X        string          `json:"bank"`         // 银行
	// ...
}

3. sqlc generate. GetFundSummary get wrong extend type.

const getFundSummary = `-- name: GetFundSummary :one
SELECT id,
       account,
       name,
       extent
  FROM auth_user
 WHERE id = $1
 LIMIT 1
`

type GetFundSummaryRow struct {
	ID      int32           `json:"id"`
	Account string          `json:"account"`
	Name    string          `json:"name"`
	Extent  json.RawMessage `json:"extent"`                    // <--------- WRONG TYPE
}

func (q *Queries) GetFundSummary(ctx context.Context, id int32) (*GetFundSummaryRow, error) {
	row := q.db.QueryRow(ctx, getFundSummary, id)
	var i GetFundSummaryRow
	err := row.Scan(
		&i.ID,
		&i.Account,
		&i.Name,
		&i.Extent,
	)
	return &i, err
}

const selectFundSummary = `-- name: SelectFundSummary :many
SELECT name,
       extent
  FROM auth_user
 WHERE user_type = 'manager'
  ORDER BY name
`

type SelectFundSummaryRow struct {
	Name   string             `json:"name"`
	Extent schema.FundSummary `json:"extent"`     // <---- BUT HERE GET CORRECT TYPE
}

func (q *Queries) SelectFundSummary(ctx context.Context) ([]*SelectFundSummaryRow, error) {
	rows, err := q.db.Query(ctx, selectFundSummary)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []*SelectFundSummaryRow
	for rows.Next() {
		var i SelectFundSummaryRow
		if err := rows.Scan(&i.Name, &i.Extent); err != nil {
			return nil, err
		}
		items = append(items, &i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

Relevant log output

const getFundSummary = `-- name: GetFundSummary :one
SELECT id,
       account,
       name,
       extent
  FROM auth_user
 WHERE id = $1
 LIMIT 1
`

type GetFundSummaryRow struct {
	ID      int32           `json:"id"`
	Account string          `json:"account"`
	Name    string          `json:"name"`
	Extent  json.RawMessage `json:"extent"`                    // <--------- WRONG TYPE
}

func (q *Queries) GetFundSummary(ctx context.Context, id int32) (*GetFundSummaryRow, error) {
	row := q.db.QueryRow(ctx, getFundSummary, id)
	var i GetFundSummaryRow
	err := row.Scan(
		&i.ID,
		&i.Account,
		&i.Name,
		&i.Extent,
	)
	return &i, err
}

const selectFundSummary = `-- name: SelectFundSummary :many
SELECT name,
       extent
  FROM auth_user
 WHERE user_type = 'manager'
  ORDER BY name
`

type SelectFundSummaryRow struct {
	Name   string             `json:"name"`
	Extent schema.FundSummary `json:"extent"`     // <---- BUT HERE GET CORRECT TYPE
}

func (q *Queries) SelectFundSummary(ctx context.Context) ([]*SelectFundSummaryRow, error) {
	rows, err := q.db.Query(ctx, selectFundSummary)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []*SelectFundSummaryRow
	for rows.Next() {
		var i SelectFundSummaryRow
		if err := rows.Scan(&i.Name, &i.Extent); err != nil {
			return nil, err
		}
		items = append(items, &i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

Database schema

CREATE TABLE IF NOT EXISTS auth_user
(
    id      SERIAL  PRIMARY KEY, -- ID
    name    VARCHAR NOT NULL,   
    user_type VARCHAR NOT NULL,
    extent  JSONB   NOT NULL DEFAULT '{}', --
    dept_id INTEGER              -- 部门ID
);

SQL queries

-- name: SelectFundSummary :many
SELECT name,
       extent
  FROM auth_user
 WHERE user_type = 'manager'
  ORDER BY name; 

-- name: GetFundSummary :one
SELECT id,
       account,
       name,
       extent
  FROM auth_user
 WHERE id = $1
 LIMIT 1;

Configuration

version: 2
sql:
  - queries: sql
    engine: postgresql
    database:
      uri: postgresql://postgres:123@localhost:5432/postgres?search_path=zjbmis
    schema: sql/schema
    gen:
      go:
        sql_package: pgx/v5
        emit_json_tags: true
        emit_db_tags: false
        emit_interface: false
        emit_result_struct_pointers: true
        emit_pointers_for_null_types: true
        emit_params_struct_pointers: true
        emit_enum_valid_method: true
        emit_all_enum_values: true
        package: crud
        out: crud
        overrides:
          - column: auth_user.extent
            go_type: liucai.com/zjbmis/schema.FundSummary

Playground URL

No response

What operating system are you using?

No response

What database engines are you using?

No response

What type of code are you generating?

No response

[swallowstalker]

hi, i'm curious and try to reproduce the issue, but it's working on my side. maybe there's some differences in config?

please take a look at https://github.com/swallowstalker/sqlc/tree/generate-one-custom-struct-col/internal/endtoend/testdata/aaaa/postgresql/pgx/v5

steps to build + generate the code

go build -o ~/go/bin/sqlc-dev ./cmd/sqlc
sqlc-dev generate -f internal/endtoend/testdata/aaaa/postgresql/pgx/v5/sqlc.yaml

this is the db schema

testaaaa=# CREATE TABLE IF NOT EXISTS auth_user
(
    id      SERIAL  PRIMARY KEY, -- ID
    name    VARCHAR NOT NULL,
    user_type VARCHAR NOT NULL,
    extent  JSONB   NOT NULL DEFAULT '{}', --
    dept_id INTEGER              -- 部门ID
);
CREATE TABLE
testaaaa=# \d auth_user
                                   Table "public.auth_user"
  Column   |       Type        | Collation | Nullable |                Default
-----------+-------------------+-----------+----------+---------------------------------------
 id        | integer           |           | not null | nextval('auth_user_id_seq'::regclass)
 name      | character varying |           | not null |
 user_type | character varying |           | not null |
 extent    | jsonb             |           | not null | '{}'::jsonb
 dept_id   | integer           |           |          |
Indexes:
    "auth_user_pkey" PRIMARY KEY, btree (id)

there's no account field though, so i have to delete that from your query.sql