Repository navigation
sqlc hallucinates ambiguous column references when no ambiguity exists #2538
Description
Version
1.19.1
What happened?
Discovered while working on #2537, sqlc returns an ambiguous column reference error for some queries which run fine with psql. See examples below.
To fix we'll need to update findColumnForRef() in output_columns.go I believe.
Relevant log output
query.sql:9:1: column reference "name" is ambiguous: if you want to skip this validation, set 'strict_order_by' to falseDatabase schema
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
bio text
);SQL queries
SELECT a.name
FROM authors a JOIN authors b ON a.id = b.id
ORDER BY name;
SELECT a.name AS name
FROM authors a JOIN authors b ON a.id = b.id
ORDER BY name;Configuration
{
"version": "1",
"packages": [
{
"path": "db",
"engine": "postgresql",
"schema": "query.sql",
"queries": "query.sql"
}
]
}Playground URL
What operating system are you using?
Linux
What database engines are you using?
PostgreSQL
What type of code are you generating?
Go
Activity
[-]sqlc hallucinates ambiguous columns when no ambiguity exists [/-][+]sqlc hallucinates ambiguous column references when no ambiguity exists [/+]andrewmbenton commentedon Jul 28, 2023
The urgency on resolving this isn't super high. The obvious workaround is to just fully qualify the column names in your order by clauses. In both queries above, using
ORDER BY a.nameresolves the sqlc confusion about ambiguity.kyleconroy commentedon Oct 2, 2023
Fixed as of v1.22.0 https://play.sqlc.dev/p/7daff5ad5a1573f1f3c1d8604e804d83af316633f7ab1edcded7c57c9bd3240b
andrewmbenton commentedon Oct 2, 2023
Still broken if you use the postgresql or mysql engines: https://play.sqlc.dev/p/3d1c2485fb8a777557b4de50bb3f7d60e17ec1a8fdcbdb42d674a5b5eae967ad
test: Add case for #2538
test: Add more test cases for new analyzer (#2879)
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: Add more test cases for new analyzer (sqlc-dev#2879)