Complex subquery confuses output

[tphoney]

Version

1.25.0

What happened?

Summary

adding a subquery with jsonb_each affects the generated golang type.

For postgres we are wanting to filter on a jsonb column. there are 2 queries one to calculate a count. And a second query to return the data.

we used to have a simple part to the where clause on a json field.

(
        tags @> @tags
        OR @tags IS NULL
    )

but it has now moved to be a more complex subquery using jsonb_each

 (
            SELECT jsonb_object_agg(
                    key,
                    COALESCE(
                        value->'userTagValue'->>'value',
                        value->'autoTagValue'->>'value'
                    )
                )
            FROM jsonb_each(enriched_tags->'tagValue')
        ) @> @tags
        OR @tags IS NULL

This has affected the generated golang types substantially.

1. Data types have changed for the ListChanges query

Original Data structure looked like

type ListChangesParams struct {
	Limit       int64    `json:"limit"`
	Offset      int64    `json:"offset"`
	AccountName string   `json:"account_name"`
	Author      []string `json:"author"`
	Repo        []string `json:"repo"`
	Status      []int32  `json:"status"`
	Risks       []int32  `json:"risks"`
	Tags        []byte   `json:"tags"`
	OrderBy     int32    `json:"order_by"`
}

with the more complex filter, we get the following data structure.

type ListChangesParams struct {
	AccountName pgtype.Text `json:"account_name"`
	Author      []string    `json:"author"`
	Repo        []string    `json:"repo"`
	Status      []int32     `json:"status"`
	Risks       []int32     `json:"risks"`
	Tags        []byte      `json:"tags"`
	OrderBy     pgtype.Int4 `json:"order_by"`
	RowOffset   pgtype.Int8 `json:"row_offset"`
	RowLimit    pgtype.Int8 `json:"row_limit"`
}

Things have change to using pgtype.* for certain fields.

Note i had to change the limit and offset to be named inputs.

LIMIT $1 OFFSET $2; to LIMIT sqlc.arg(row_limit) OFFSET sqlc.arg(row_offset); this is related to #3840 i think

NB

the playground wont render my example at all, so i have provided 2 playground links. a working one has a hardcoded value instead of the named parameter @tags with doesnt work

Relevant log output

There is no log output, just an error in the generated go code.

Database schema

CREATE TABLE public.changes (
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    external_id uuid DEFAULT gen_random_uuid() NOT NULL,
    account_name text NOT NULL,
    status integer DEFAULT 0 NOT NULL,
    title text NOT NULL,
    description text NOT NULL,
    ticket_link text,
    owner text,
    cc_emails text,
    system_before_snapshot_uuid uuid,
    system_after_snapshot_uuid uuid,
    blast_radius_snapshot_uuid uuid,
    changing_items_bookmark_uuid uuid,
    creator_name text NOT NULL,
    num_affected_items integer DEFAULT 0 NOT NULL,
    is_example boolean DEFAULT false NOT NULL,
    num_affected_edges integer DEFAULT 0 NOT NULL,
    num_unchanged_items integer DEFAULT 0 NOT NULL,
    num_created_items integer DEFAULT 0 NOT NULL,
    num_updated_items integer DEFAULT 0 NOT NULL,
    num_deleted_items integer DEFAULT 0 NOT NULL,
    unknown_health_added integer DEFAULT 0 NOT NULL,
    unknown_health_removed integer DEFAULT 0 NOT NULL,
    unknown_health_final_total integer DEFAULT 0 NOT NULL,
    ok_health_added integer DEFAULT 0 NOT NULL,
    ok_health_removed integer DEFAULT 0 NOT NULL,
    ok_health_final_total integer DEFAULT 0 NOT NULL,
    warning_health_added integer DEFAULT 0 NOT NULL,
    warning_health_removed integer DEFAULT 0 NOT NULL,
    warning_health_final_total integer DEFAULT 0 NOT NULL,
    error_health_added integer DEFAULT 0 NOT NULL,
    error_health_removed integer DEFAULT 0 NOT NULL,
    error_health_final_total integer DEFAULT 0 NOT NULL,
    pending_health_added integer DEFAULT 0 NOT NULL,
    pending_health_removed integer DEFAULT 0 NOT NULL,
    pending_health_final_total integer DEFAULT 0 NOT NULL,
    num_replaced_items integer DEFAULT 0 NOT NULL,
    planned_changes_stored boolean DEFAULT false NOT NULL,
    raw_plan text,
    code_changes text,
    num_low_risks integer DEFAULT 0 NOT NULL,
    num_medium_risks integer DEFAULT 0 NOT NULL,
    num_high_risks integer DEFAULT 0 NOT NULL,
    risk_progress_status smallint[] DEFAULT '{0}'::smallint[] NOT NULL,
    risk_progress_messages text[] DEFAULT '{"Submit a plan to calculate blast radius and risks"}'::text[] NOT NULL,
    is_billed boolean DEFAULT false NOT NULL,
    creator_email text DEFAULT ''::text NOT NULL,
    repo text DEFAULT ''::text NOT NULL,
    tags jsonb DEFAULT '{}'::jsonb,
    enriched_tags jsonb DEFAULT '{}'::jsonb,
    skipped_auto_tags jsonb DEFAULT '{}'::jsonb,
    auto_tagging_rule_source integer DEFAULT 0 NOT NULL,
    CONSTRAINT changes_check_risk_progress_exists CHECK (((array_length(risk_progress_status, 1) > 0) AND (array_length(risk_progress_messages, 1) > 0))),
    CONSTRAINT changes_check_risk_progress_length CHECK ((array_length(risk_progress_status, 1) = array_length(risk_progress_messages, 1)))
);

SQL queries

-- name: CountChanges :one
SELECT count(*)
FROM changes
WHERE account_name = @account_name
    AND (
        creator_name = ANY(@author::text [])
        OR @author IS NULL
    )
    AND (
        repo = ANY(@repo::text [])
        OR @repo IS NULL
    )
    AND (
        status = ANY(@status::int [])
        OR @status IS NULL
    )
    AND (
        (
            1 = ANY(@risks::int [])
            AND num_low_risks > 0
        )
        OR (
            2 = ANY(@risks::int [])
            AND num_medium_risks > 0
        )
        OR (
            3 = ANY(@risks::int [])
            AND num_high_risks > 0
        )
        OR ARRAY_LENGTH(@risks, 1) IS NULL
    )
    AND (
        (
            SELECT jsonb_object_agg(
                    key,
                    COALESCE(
                        value->'userTagValue'->>'value',
                        value->'autoTagValue'->>'value'
                    )
                )
            FROM jsonb_each(enriched_tags->'tagValue')
        ) @> '{"security": null}'
        OR @tags IS NULL
    );
-- name: ListChanges :many
SELECT *
FROM changes
WHERE account_name = @account_name
    AND (
        creator_name = ANY(@author::text [])
        OR @author IS NULL
    )
    AND (
        repo = ANY(@repo::text [])
        OR @repo IS NULL
    )
    AND (
        status = ANY(@status::int [])
        OR @status IS NULL
    )
    AND (
        (
            1 = ANY(@risks::int [])
            AND num_low_risks > 0
        )
        OR (
            2 = ANY(@risks::int [])
            AND num_medium_risks > 0
        )
        OR (
            3 = ANY(@risks::int [])
            AND num_high_risks > 0
        )
        OR ARRAY_LENGTH(@risks, 1) IS NULL
    )
    AND (
        (
            SELECT jsonb_object_agg(
                    key,
                    COALESCE(
                        value->'userTagValue'->>'value',
                        value->'autoTagValue'->>'value'
                    )
                )
            FROM jsonb_each(enriched_tags->'tagValue')
        ) @>  '{"security": null}'
        OR @tags IS NULL
    )
ORDER BY CASE
        WHEN @order_by::int = 0 THEN title
    END ASC,
    CASE
        WHEN @order_by = 1 THEN title
    END DESC,
    CASE
        WHEN @order_by = 2 THEN created_at
    END ASC,
    CASE
        WHEN @order_by = 3 THEN created_at
    END DESC
LIMIT sqlc.arg(row_limit) OFFSET sqlc.arg(row_offset);

Configuration

Playground URL

working with workaround ) @> '{"security": null}'
https://play.sqlc.dev/p/a67edcd2f245f6bfeda821c3ebca935fb253669807f1fd036c88c69f0a5025f1

failing with named parameter @tag
https://play.sqlc.dev/p/2bc14cfff5e578faa111eecae99407dfaa94b55b4b4a28b2366e720f703ae9e9

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go