Multiple CTE Update

[DarkHeros09]

Version

1.15.0

What happened?

Hi

i Have This issue with the following query, i tested the query itself, and it works fine, but sqlc is giving me the below error.

Relevant log output

# package db
db/query/user_address.sql:88:5: column "default_address" does not exist

Database schema

CREATE TABLE "user" (
  "id" bigserial PRIMARY KEY NOT NULL,
  "username" varchar NOT NULL,
  "email" varchar UNIQUE NOT NULL,
  "password" varchar NOT NULL,
  "telephone" int NOT NULL DEFAULT 0,
  "default_payment" bigint,
  "created_at" timestamptz NOT NULL DEFAULT (now()),
  "updated_at" timestamptz NOT NULL DEFAULT '0001-01-01 00:00:00Z'
);

CREATE TABLE "address" (
  "id" bigserial PRIMARY KEY NOT NULL,
  "address_line" varchar NOT NULL,
  "region" varchar NOT NULL,
  "city" varchar NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now()),
  "updated_at" timestamptz NOT NULL DEFAULT '0001-01-01 00:00:00Z'
);

CREATE TABLE "user_address" (
  "user_id" bigint NOT NULL,
  "address_id" bigint UNIQUE NOT NULL,
  "default_address" bigint,
  "created_at" timestamptz NOT NULL DEFAULT (now()),
  "updated_at" timestamptz NOT NULL DEFAULT '0001-01-01 00:00:00Z'
);

SQL queries

-- name: UpdateUserAddressWithAddress :one
WITH t1 AS (
    UPDATE "address" as a
    SET
    address_line = COALESCE(sqlc.narg(address_line),address_line), 
    region = COALESCE(sqlc.narg(region),region), 
    city= COALESCE(sqlc.narg(city),city)
    WHERE id = COALESCE(sqlc.arg(id),id)
    RETURNING a.id, a.address_line, a.region, a.city
   ),
   
    t2 AS (
    UPDATE "user_address"
    SET
    default_address = COALESCE(sqlc.narg(default_address),default_address)
    WHERE
    user_id = COALESCE(sqlc.arg(user_id),user_id)
    AND address_id = COALESCE(sqlc.arg(address_id),address_id)
    RETURNING user_id, address_id, default_address
	)
	
SELECT 
user_id,
address_id,
default_address,
address_line,
region,
city From t1,t2;

Configuration

version: 1
packages:
  - name: "db"
    path: "./db/sqlc"
    queries: "./db/query/"
    schema: "./db/migration/"
    engine: "postgresql"
    emit_json_tags: true
    emit_prepared_queries: false
    emit_interface: true
    emit_exact_table_names: false
    emit_empty_slices: true

Playground URL

https://play.sqlc.dev/p/17faf82c923406a0c8272595305c3262ea1e0e74c744a0d3744e3ebb23554cc9

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[oliverpool]

The error message looks similar to the one I reported in #1912

[DarkHeros09]

The error message looks similar to the one I reported in #1912

I believe this is also true for #1515

[DarkHeros09]

Any update on this issue?

[kyleconroy]

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground