sqlc hallucinates ambiguous column references when no ambiguity exists

[andrewmbenton]

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 false

Database 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

https://play.sqlc.dev/p/8e9c3fa8f50201433c489b118a731130a7011574ec53d27bbe460eb1978f2f13

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[andrewmbenton]

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.name resolves the sqlc confusion about ambiguity.

[kyleconroy]

Fixed as of v1.22.0 https://play.sqlc.dev/p/7daff5ad5a1573f1f3c1d8604e804d83af316633f7ab1edcded7c57c9bd3240b

[andrewmbenton]

Still broken if you use the postgresql or mysql engines: https://play.sqlc.dev/p/3d1c2485fb8a777557b4de50bb3f7d60e17ec1a8fdcbdb42d674a5b5eae967ad

[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