Repository navigation
incorrect ambiguous reference check #2398
Description
Version
1.18.0
What happened?
There seems to be a minor bug in checking ambiguous references when joning tables while using alias.
To reproduce, change the sqlc/internal/endtoend/testdata/order_by_non_existing_column/postgresql to
-- Example queries for sqlc
CREATE TABLE authors (
id INT
);
CREATE TABLE books (
id INT,
author_id INT,
price INT
);
-- name: ListAuthors :many
SELECT id FROM authors
ORDER BY adfadsf;
-- name: ListAuthorsByCheapestBook :many
SELECT
author_id, min(b.price) as min_price
From books b inner join authors a on a.id = b.author_id
GROUP BY b.author_id
ORDER BY min_price;
The column of min_price is not ambiguous but sqlc reports:
# package
query.sql:13:1: column reference "adfadsf" not found: if you want to skip this validation, set 'strict_order_by' to false
query.sql:17:1: column reference "min_price" is ambiguous: if you want to skip this validation, set 'strict_order_by' to false
Relevant log output
query.sql:13:1: column reference "adfadsf" not found: if you want to skip this validation, set 'strict_order_by' to false
query.sql:17:1: column reference "min_price" is ambiguous: if you want to skip this validation, set 'strict_order_by' to falseDatabase schema
CREATE TABLE authors (
id INT
);
CREATE TABLE books (
id INT,
author_id INT,
price INT
);SQL queries
-- name: ListAuthorsByCheapestBook :many
SELECT
author_id, min(b.price) as min_price
From books b inner join authors a on a.id = b.author_id
GROUP BY b.author_id
ORDER BY min_price;Configuration
No response
Playground URL
No response
What operating system are you using?
Linux
What database engines are you using?
PostgreSQL
What type of code are you generating?
Go
Reacted by Sjors Gielen
Activity
fix: count columns aliases only once
fix: count columns aliases only once
akutschera commentedon Jul 1, 2023
That's an interesting one. I believe this has been in the code for a while.
What happens seems to be this:
For each column name in a GROUP BY or ORDER BY clause, we check, if we have it in a table definition.
When we cannot find it, we check the select statement. When we find it there (e.g. in an alias) we say we found it.
Because we iterate over the number of tables in the statement, we will find it twice. That causes this problem.
The PR above should fix this (and also #1886 which seems to be very similar to this one here).
fix(compiler): correctly validate alias in order/group by clauses for…
fix(compiler): correctly validate alias in order/group by clauses for…
Stumble commentedon Aug 29, 2023
Sorry for late reply, thanks for the fix!
fix(compiler): correctly validate alias in order/group by clauses for…