Repository navigation
Group by and left join using gives a "column reference is ambiguous #1425
Description
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 ambiguousDatabase 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
What operating system are you using?
No response
What database engines are you using?
PostgreSQL
What type of code are you generating?
No response
Reacted by Eric Bonet
Activity
sbres commentedon Mar 14, 2022
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
I've attached your setup in playground
https://play.sqlc.dev/p/aaaa171855439c5253e3c885154d0f6a23b2953bb1625fc9a02b07b2ffe941c4
Yamakaky commentedon Mar 14, 2022
I just tested this in postgresql, it works.
Updated the schema to add unique constraint.
Yamakaky commentedon Apr 6, 2022
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 commentedon Oct 29, 2022
I'm also running into this issue with
v1.15.0.If I use
v1.10.0then sqlc has no issues generating.any update?
Fix #1425
Fix #1425
kyleconroy commentedon Oct 24, 2023
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
test(analyzer): Add more database analyzer test cases (sqlc-dev#2854)