Aliasing a subquery fails to compile

[kyleconroy]

The following SQL used to cause sqlc to panic. That was fixed in #158. Sadly, sqlc still can't handle the aliasing of a subquery. When trying to compile the following SQL:

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

-- name: SubAuthors :many
SELECT sub.name FROM (SELECT * FROM authors) sub

sqlc returns the following error:

query.sql:x:y: column "name" does not exist

cc @arddor

[tgascoigne]

Hi,
I'm seeing similar errors with a few variations on this join. Is this a related issue, or shall I raise a seperate one?

SELECT f, b
FROM foo f
JOIN bar b ON b.id = f.id
WHERE f.id = $1;

query.sql:6:8: column "f" does not exist

SELECT foo, bar
FROM foo
JOIN bar ON bar.id = foo.id
WHERE foo.id = $1;

query.sql:6:8: column "foo" does not exist

SELECT f.id, b.title
FROM foo f
JOIN bar b ON b.id = f.id
WHERE f.id = $1;
                
query.sql:6:8: column "id" does not exist

[kyleconroy]

@tgascoigne I think this is different. I've moved your report to issue #213

[dharmjit]

Hi Kyle, This issue has impacted me as well. Any update on this.

[mmcloughlin]

I think I have an instance of this issue:

SELECT
    chg.*,
    c.sha AS commit_sha,
    SPLIT_PART(c.message, E'\n', 1)::TEXT AS commit_subject,

    b.*,
    pkg.relative_path,
    mod.path,
    mod.version
FROM
    (
        SELECT
            *,
            (ROW_NUMBER() OVER (PARTITION BY commit_index ORDER BY effect_size DESC)) AS ranking
        FROM
            changes
        WHERE 1=1
            AND ABS(chg.effect_size) > sqlc.arg(effect_size_min)
            AND commit_index BETWEEN sqlc.arg(commit_index_min) AND sqlc.arg(commit_index_max)
    ) AS chg
    INNER JOIN commit_positions AS p
        ON chg.commit_index=p.index
    INNER JOIN commits AS c
        ON p.sha=c.sha
    INNER JOIN benchmarks AS b
        ON chg.benchmark_uuid=b.uuid
    INNER JOIN packages AS pkg
        ON b.package_uuid=pkg.uuid
    INNER JOIN modules AS mod
        ON pkg.module_uuid=mod.uuid
WHERE 1=1
    AND chg.ranking <= sqlc.arg(num_per_commit)
ORDER BY
    chg.commit_index DESC
;

Error:

query/changes.sql:39:9: column "ranking" does not exist

[maxhawkins]

I believe this is fixed by #489