Open
Description
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.
- 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