`Group by` and `left join using` gives a "column reference is ambiguous

[Yamakaky]

Version

1.12.0

What happened?

When using join ... using (key) group by key, sqlc seems to think key references the columns in the joined tables instead of the synthetic key column generated by using. where and having doesn't seem to have this problem, they work as expected.

This seem to be a regression since I have no problem with this kind of query in the 1.10.0 I use.

Relevant log output

sqlc generate failed.

# package db
query.sql:7:46: column reference "fk" is ambiguous

Database schema

create table t1 (
        fk integer not null unique
);
create table t2 (
        fk integer not null references t1(fk)
);

SQL queries

select * from t1 join t2 using (fk) group by fk;

Configuration

No response

Playground URL

https://play.sqlc.dev/p/f9717de599d83359e7f1fdf15bbe7e3ed9cb57ce7c6b4c9dd6576a6445b140cd

What operating system are you using?

No response

What database engines are you using?

PostgreSQL

What type of code are you generating?

No response

[sbres]

Your problem comes from your query, It's not valid.

The SQLengine does not know what table you want to use for the group by.

I was able to run your example by specifying the table form where to group by

select * from t1 join t2 using (fk) group by t1.fk;

I've attached your setup in playground https://play.sqlc.dev/p/aaaa171855439c5253e3c885154d0f6a23b2953bb1625fc9a02b07b2ffe941c4

[Yamakaky]

I just tested this in postgresql, it works.

Updated the schema to add unique constraint.

[Yamakaky]

Any news on that ?

From postgresql doc: "Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2."

[BaronBonet]

I'm also running into this issue with v1.15.0.

If I use v1.10.0 then sqlc has no issues generating.

any update?

[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