Incorrect "ambiguous column reference" error

[farwydi]

Input: (PostgreSQL)

-- name: FetchContentLocalization :many
SELECT c.id, --- Error here
       tx1.value AS name,
       tx2.value AS description,
       tx3.value AS text1,
       tx4.value AS text2,
       tx5.value AS text3,
       tx6.value AS text4
FROM (
         SELECT c.*
         FROM contents c
                  INNER JOIN content_group_links cgl ON c.id = cgl.content_id
                  INNER JOIN group_category_links gcl ON cgl.group_id = gcl.group_id
                  INNER JOIN categories ct ON gcl.category_id = ct.id
                  INNER JOIN services s ON ct.service = s.id
         WHERE s.id = 1
         UNION
         SELECT c.*
         FROM contents c
                  INNER JOIN content_includes ci ON c.id = ci.content_id
                  INNER JOIN categories ct ON ci.category_id = ct.id
                  INNER JOIN services s ON ct.service = s.id
         WHERE s.id = 1
     ) AS c
         LEFT JOIN override_content_params ocp ON ocp.content_id = c.id
         LEFT JOIN i18n_texts tx1 ON (tx1.key = c.name_i AND tx1.lang_id = 1)
         LEFT JOIN i18n_texts tx2 ON (tx2.key = c.description_i AND tx2.lang_id = 1)
         LEFT JOIN i18n_texts tx3 ON (tx3.key = c.text_field_1_i AND tx3.lang_id = 1)
         LEFT JOIN i18n_texts tx4 ON (tx4.key = c.text_field_2_i AND tx4.lang_id = 1)
         LEFT JOIN i18n_texts tx5 ON (tx5.key = c.text_field_3_i AND tx5.lang_id = 1)
         LEFT JOIN i18n_texts tx6 ON (tx6.key = c.text_field_4_i AND tx6.lang_id = 1)
WHERE c.id NOT IN
      (
          SELECT content_id
          FROM content_excludes ce
                   INNER JOIN categories ct ON ce.category_id = ct.id
                   INNER JOIN services s ON ct.service = s.id
          WHERE s.id = 1
      )
  AND (TRUE OR COALESCE(ocp.enable, c.enable) IS TRUE)
ORDER BY c.id;

Got: localization.sql:2:8: column reference "id" is ambiguous

But is not true :)

Playground link

https://play.sqlc.dev/p/cfa1919d90d917b0bede05032cf03925c7e9bcd2906d66ad20118637f5c8ffd8

[kyleconroy]

@farwydi Any chance you could provide the schema for this query? There are a few tables that you're joining on which makes it difficult to debug without the structure of those tables.

[farwydi]

https://gist.github.com/7c7ebce2be7107e2ebd635c64001169b

[rf]

This is easy to test with a minimal testcase such as SELECT test.somefield FROM (select * from sometable) AS test, where sometable is a table with field somefield. I.e., any time an AS is used, sqlc cannot tell what table to validate against. Which makes sense as in order to validate this, it'd actually have to run the subquery. But it should just disable the field validation in this case.

[rf]

Interestingly if you convert my above example to use a CTE, it compiles fine. So that implies there's something wrong with the handling of subqueries.

[kyleconroy]

Here's the playground link for the failing test case from #393

[kyleconroy]

Fixed as of v1.22.0

-- Example queries for sqlc
CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

-- name: SelectSubquery :many
SELECT test.name FROM (select * from authors) AS test;

https://play.sqlc.dev/p/7ba83ad9bcdfd91310cf9950c684a0d4d0ddbbe6f4861f7a0261e18ab439938a