sqlc generate failed：column "new_streak" does not exist

[Flipped199]

Version

1.30.0

What happened?

Playground:

This issue occurs when using sqlc version 1.30 both in the local environment and in the Playground. I have confirmed that the same error is reproducible in both environments.

Relevant log output

sqlc generate failed.

Database schema

CREATE TABLE users
(
    id                  BIGSERIAL PRIMARY KEY,
    credit              BIGINT      DEFAULT 0           NOT NULL CHECK ( credit >= 0 ),
    last_checkin_day    DATE,
    checkin_streak      INT         DEFAULT 0           NOT NULL CHECK ( checkin_streak >= 0 )
);

CREATE TABLE credit_log
(
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT                    NOT NULL REFERENCES users (id),
    amount      INT                       NOT NULL,
    type        TEXT                      NOT NULL,
    description TEXT,
    created_at  timestamptz DEFAULT NOW() NOT NULL
);

SQL queries

-- name: CheckinUser :one
WITH target AS (
    SELECT id, last_checkin_day, checkin_streak
    FROM users
    WHERE users.id = $1
    FOR UPDATE
),
tz AS (
    SELECT (NOW() AT TIME ZONE 'Asia/Shanghai')::date AS today
),
abc (new_streak, id, applied) AS (
    SELECT id,
           (last_checkin_day IS NULL OR last_checkin_day <> (SELECT today FROM tz)) AS applied,
           CASE
               WHEN last_checkin_day = (SELECT today FROM tz) - INTERVAL '1 day' THEN checkin_streak + 1
               ELSE 1
               END AS new_streak
    FROM target
),
reward AS (
    SELECT id,
           applied,
           new_streak,
           CASE
               WHEN applied THEN $2 + ($2 * LEAST(GREATEST(new_streak - 1, 0), 5) / 5)
               ELSE 0
               END AS total_reward
    FROM abc 
),
updated AS (
    UPDATE users u
        SET last_checkin_day = CASE WHEN r.applied THEN (SELECT today FROM tz) ELSE u.last_checkin_day END,
            checkin_streak   = CASE WHEN r.applied THEN r.new_streak ELSE u.checkin_streak END,
            credit           = u.credit + r.total_reward
        FROM reward r
        WHERE u.id = r.id
        RETURNING r.applied, r.new_streak, r.total_reward, u.id, u.credit, u.last_checkin_day, u.checkin_streak
),
log AS (
    INSERT INTO credit_log (user_id, amount, type, description)
        SELECT $1, total_reward, 'checkin', $3
        FROM updated
        WHERE applied
        RETURNING id
)
SELECT applied::INT AS applied,
       new_streak,
       total_reward,
       id, credit, last_checkin_day, checkin_streak
FROM updated;

Configuration

Playground URL

https://play.sqlc.dev/p/20c431276d4f5fd75aaaa81d1c9930307498080a4e0c02e2664f833297e0df1e

What operating system are you using?

Windows11

What database engines are you using?

postgresql

What type of code are you generating?

go