Ah i see, i does make sense when you talk about FROM having access to relations that have been "created" in some way.
My current goal is to extract all (or as many as possible) external dependencies from an arbitrary query.
I've created a parser grammar using ANTLR4 and now i need to analyse the generated syntax trees and that involves this kind of scope and alias tracking.
Why doesn't the first one work when the second one works fine? In other words, why do subquery's traget list and range table list use different namespaces to resolve table references/aliases?
Is this an unintended behaviour, implementation detail or is there an actual reason for this?
Cannot speak to restrictions imposed by the SQL standard but the exhibited behavior seems logical given the nature of subqueries.
The relation named in the FROM clause must exist in the database schema or have been previously "created" using a CTE. Within a subquery all from clause entries behave the same but you can reference a column by name (with usually optional table and schema prefix) if it exists in the containing scope. Thus there is no need to complicate things by requiring (or allowing) the outer relation names to be targeted by a FROM clause in a subquery.
Correlated subqueries require target list resolution to behave in that manner. It doesn't seem useful to complicate range table resolution lacking a similar need.
