🪲 Name clashes in JOINs result in files not being found

[joocer]

Describe the bug
A clear and concise description of what the bug is.

SQL statement
Please submit the SQL statement, or a representative example using the sample datasets.

SELECT P0.id, P1.ID, P2.ID
FROM $planets AS P0
JOIN (SELECT id AS ID FROM $planets) AS P1 ON P0.name = P1.name
JOIN (SELECT id AS ID FROM $planets) AS P2 ON P0.name = P2.name

Expected behaviour
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.

[joocer]

may be related to this failing:

SELECT P.* FROM (SELECT * FROM $planets) AS P

[joocer]

Nope - second example now working but first example still not working.

[joocer]

This works

SELECT P0.id
FROM $planets AS P0

but when this fails it complains about the P0 column in the join condition

SELECT P0.id, P1.ID
FROM $planets AS P0
INNER JOIN (SELECT id AS ID FROM $planets) AS P1 ON P0.name = P1.name

This should fail because the name field isn't output from the subquery. Which is the error on this query:

 SELECT P0.id, P1.ID
FROM $planets AS P0
INNER JOIN (SELECT id AS ID FROM $planets) AS P1 USING (name)

But this one works:

 SELECT P0.id, P1.ID
FROM $planets AS P0
INNER JOIN (SELECT name, id AS ID FROM $planets) AS P1 USING (name)

but this one complains about P0.name

SELECT P0.id, P1.ID
FROM $planets AS P0
INNER JOIN (SELECT id, name AS ID FROM $planets) AS P1 ON P0.name = P1.name

[joocer]

That query is illogical, it should have been:

SELECT P0.id, P1.ID
FROM $planets AS P0
INNER JOIN (SELECT id, name AS ID FROM $planets) AS P1 ON P0.name = P1.name

The problem is with P1, not P0 even though it's P0 in the error. It looked for the value in P1 (where it is), failed, then looked in P0, where it isn't.

but even though it was aliased, P1.name should have been found, the issue appears to be with the column alias at the same time as the relation alias.