SELECT WITH where inner query has columns with same name ignores table aliases

[gsora]

Version

1.10.0

What happened?

Given the following query:

WITH
	q
		AS (
			SELECT
				authors.name, authors.bio
			FROM
				authors
				LEFT JOIN fake ON authors.name = fake.name
		)
SELECT
	*
FROM
	q AS c1
WHERE
-- the following conditions have issues:
c1.name = $1;
-- this condition doesn't have issues:
-- c1.name = 'lol';
-- nor this one:
-- authors.name = $1;

sqlc generate fails to generate a query, because the name specifier is ambiguous.

Relevant log output

query.sql:29:1: column reference "name" is ambiguous

Database schema

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

CREATE TABLE fake (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

SQL queries

WITH
	q
		AS (
			SELECT
				authors.name, authors.bio
			FROM
				authors
				LEFT JOIN fake ON authors.name = fake.name
		)
SELECT
	*
FROM
	q AS c1
WHERE
-- the following conditions have issues:
c1.name = $1;
-- this condition doesn't have issues:
-- c1.name = 'lol';
-- nor this one:
-- authors.name = $1;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "postgresql",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/31d37017dbab42342784faecfab1f0b4a76b48bce3dbd5b5cb433fea68d878ea

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kishankishore2908]

Is there a workaround to this ? Facing the same issue.

[Davincible]

Same here. I'd consider creating a PR for this but I'm not sure where to start looking

[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