Generating with columns that don't exist

[danthegoodman1]

Version

1.19.1

What happened?

It allowed query generation on columns that did not exist.

Relevant log output

None, it worked when it shouldn't

Database schema

CREATE TABLE public.interactions_v2 (
	id STRING NOT NULL,
	creator_id STRING NOT NULL DEFAULT '':::STRING,
	price_cents INT8 NOT NULL,
	enabled BOOL NOT NULL DEFAULT false,
	deleted BOOL NOT NULL DEFAULT false,
	created_at TIMESTAMPTZ NOT NULL DEFAULT now():::TIMESTAMPTZ,
	updated_at TIMESTAMPTZ NOT NULL DEFAULT now():::TIMESTAMPTZ ON UPDATE now():::TIMESTAMPTZ,
	action_id STRING NOT NULL,
	account_id STRING NULL,
	integration_id STRING NOT NULL,
	recommended BOOL NOT NULL DEFAULT false,
	creator_vars JSONB NOT NULL DEFAULT '{}':::JSONB,
	channel_points_reward_id STRING NULL,
	profiles STRING[] NOT NULL DEFAULT ARRAY[]:::STRING[],
	bits_price INT8 NOT NULL DEFAULT 100:::INT8,
	twitch_enabled BOOL NOT NULL DEFAULT true,
	transcription STRING NOT NULL DEFAULT '':::STRING,
	no_manual_delete BOOL NOT NULL DEFAULT false,
	owning_entity STRING NULL,
	share_state STRING NULL,
	sharer_user_id STRING NULL,
	sharer_interaction_id STRING NULL,
	sharer_name STRING NULL,
	hidden BOOL NOT NULL DEFAULT false,
	CONSTRAINT interactions_v2_pkey PRIMARY KEY (creator_id ASC, id ASC),
	CONSTRAINT action_def_fk FOREIGN KEY (action_id, integration_id) REFERENCES public.action_definitions(id, integration_id) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT accounts_fk FOREIGN KEY (creator_id, integration_id, account_id) REFERENCES public.integration_accounts(creator_id, integration_id, id) ON UPDATE CASCADE,
	UNIQUE INDEX ia_by_reward_id (channel_points_reward_id ASC),
	INDEX ia_deleted (deleted ASC, creator_id ASC),
	INDEX idx_ias_per_action (integration_id ASC, action_id ASC, deleted ASC),
	INDEX ia_by_owning_entity (owning_entity ASC) WHERE owning_entity IS NOT NULL,
	INDEX shared_interactions (share_state ASC, deleted ASC) WHERE share_state IS NOT NULL,
	INDEX shared_interactions_by_sharer (sharer_user_id ASC) WHERE sharer_user_id IS NOT NULL,
	INDEX interactions_by_action_idx (action_id ASC, deleted ASC)
)

SQL queries

update interactions_v2
set deleted = TRUE
where creator in ($1::TEXT[]) -- this column doesn't exist
and (sharer_interaction_id = 'iv2_6eZ76PNFiCCIDR1OjqLMAi' or sharer_interaction_id = 'iv2_OsoBRusS35FjD5tvUg68vh')

Configuration

version: 2
sql:
  - engine: "postgresql"
    schema: "schema.sql"
    queries: "./queries/"
    gen:
      go:
        sql_package: "pgx/v5"
        out: "query"
        package: "query"
        overrides:
          # for some reason we sometimes need the catalog prefix and sometimes not although docs say we should always use it
          - db_type: "pg_catalog.bool"
            go_type: "database/sql.NullBool"
            nullable: true
          - db_type: "bool"
            go_type: "database/sql.NullBool"
            nullable: true

          - db_type: "int8"
            go_type: "database/sql.NullInt64"
            nullable: true
          - db_type: "pg_catalog.int8"
            go_type: "database/sql.NullInt64"
            nullable: true

          - db_type: "int2"
            go_type: "database/sql.NullInt16"
            nullable: true
          - db_type: "pg_catalog.int2"
            go_type: "database/sql.NullInt16"
            nullable: true

          - db_type: "float4"
            go_type: "database/sql.NullFloat64"
            nullable: true

          - db_type: "float8"
            go_type: "database/sql.NullFloat64"
            nullable: true

          - db_type: "text"
            go_type: "database/sql.NullString"
            nullable: true

          - db_type: "timestamptz"
            go_type: "database/sql.NullTime"
            nullable: true

          - db_type: "timestamptz"
            go_type: "time.Time"
            nullable: false

          - db_type: "timestamptz"
            go_type: "database/sql.NullTime"
            nullable: false

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[orisano]

Does this issue occur with the latest version of sqlc? You can test it in the playground: https://play.sqlc.dev/

[danthegoodman1]

@orisano seems to generate, yes the issue still occurs https://play.sqlc.dev/p/dd69b4316ab216b0fe98203b5c3d60d0146810d02edfc0e4ecdc4b4e2bb48ff1

[danthegoodman1]

@orisano thoughts?

Edit: definitely breaking of the guarantees of sqlc that we have been leaning on.