Error "column reference 'city_id' is ambiguous" when generating SQL with sqlc

[theabner]

Version

1.28.0

What happened?

I am encountering an error when running sqlc generate on an SQL query that calculates city rankings based on ratings and survey responses. The error reported by sqlc is:

internal/infrastructure/database/sql/reports.sql:542:35: column reference "city_id" is ambiguous
make: *** [makefile:61: generate-sql] Error 1

The error occurs in the following SQL query:

WITH topic_score AS (
    SELECT
        t.id AS topic_id,
        a.city_id,
        ROUND(COALESCE(AVG(a.rating), 0), 2) AS avg_rating,
        COUNT(a.id) AS total_ratings
    FROM topics t
    LEFT JOIN assessments a ON a.topic_id = t.id
    GROUP BY t.id, a.city_id
), 
topic_questions_score AS (
    SELECT
        t.id AS topic_id,
        a.city_id,
        c.name AS city_name,
        COUNT(DISTINCT a.user_id) AS total_respondents,
        COUNT(CASE WHEN a.response = 'Yes' THEN a.id END) AS total_yes_responses,
        COUNT(CASE WHEN a.response = 'No' THEN a.id END) AS total_no_responses,
        COUNT(a.id) AS total_responses
    FROM topics t
    LEFT JOIN questions q ON t.id = q.topic_id
    LEFT JOIN answers a ON q.id = a.question_id
    LEFT JOIN cities c ON a.city_id = c.id
    GROUP BY t.id, a.city_id, c.name
), 
final_scores AS (
    SELECT
        ts.city_id,
        COALESCE(tqs.city_name, c.name) AS city_name,
        ROUND(
            (COALESCE(tqs.total_yes_responses, 0) * 10.0) /
            COALESCE(NULLIF(tqs.total_responses, 0), 1),
            2
        ) AS questions_score, 
        ROUND(
            (COALESCE(ts.avg_rating, 0) * 0.7) +
            (COALESCE(tqs.total_yes_responses, 0) * 10.0 / 
            COALESCE(NULLIF(tqs.total_responses, 0), 1) * 0.3),
            2
        ) AS topic_score_final
    FROM topic_score ts
    LEFT JOIN topic_questions_score tqs ON ts.topic_id = tqs.topic_id AND ts.city_id = tqs.city_id
    LEFT JOIN cities c ON ts.city_id = c.id
), 
ranked_cities AS (
    SELECT 
        city_id,
        city_name,
        AVG(topic_score_final) AS avg_score,
        DENSE_RANK() OVER (ORDER BY AVG(topic_score_final) DESC) AS ranking
    FROM final_scores
    GROUP BY city_id, city_name
)
SELECT * FROM ranked_cities WHERE city_id = sqlc.arg(cityId);

Details
The error message states that the column reference city_id is ambiguous, but the SQL query appears to be correct. The issue occurs in the ranked_cities CTE, and since city_id is used in multiple derived tables (CTEs), sqlc may not be able to resolve it correctly.

Steps to Reproduce:

• Create a query similar to the one above.
• Run sqlc generate.
• Observe the error: column reference "city_id" is ambiguous.

Has anyone encountered this issue before? Is there a recommended way to handle this in sqlc? Any insights would be greatly appreciated!

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

MySQL

What type of code are you generating?

Go

[jakoguta]

Please simplify the query to focus on city_id and complete the sections of the form missing. It will help with recreating the issue.