Re: Roadmap for FE/BE protocol redesign

"Dave Page" <dpage(at)vale-housing(dot)co(dot)uk> writes:
>> What about:
>>
>> * Joins. Does an aliased JOIN output variable get resolved
>> to the underlying table variable? What about common columns
>> in USING/NATURAL joins?

> Sorry, not sure what you mean - can you give me an example?

Sure, consider table1 with columns a,b,c and table2 with a,x,y:

SELECT * FROM table1 NATURAL JOIN table2

has output columns a,b,c,x,y (notice only one column a). How do you
expect these to be labeled? Does the answer change if the join is
OUTER? For example, in a LEFT JOIN x and y might return NULLs that
have nothing to do with any table2 row; is it still appropriate to
label them as table2 outputs? And what do we do with column a in
each outer-join case?

Also, how about an aliased join

SELECT * FROM (table1 NATURAL JOIN table2) AS q(r,s,t,u,v)

In this case the output columns are named q.r, q.s, q.t, q.u, q.v as
far as the SELECT list can tell --- it's actually illegal to refer to
table1.* or table2.* in the SELECT list, IIRC. Do we drill down into
the JOIN? (Probably so, if you expect the thing to drill down into
sub-SELECTs, but I want clarity ...)

>> * Sub-selects. Are you expecting
>> SELECT x FROM (SELECT x FROM foo) ss
>> to produce a reference to foo.x? (If so, why is this
>> different from the case where ss is a view?)

> Yes, I would expect that reference. I don't think it's different from a
> view - we are trying to deciper the SQL itself, not what a view actually
> represents so I would expect to see references to whatever relation is
> in the original SQL, whether that relation is the result of a subselect
> from another table/view, or a view.

Okay, that clarifies the intent quite a bit. So we need to do the labeling
processing before any view rewriting occurs. That probably means
sticking the info into Resdom nodes during the parser, rather than
extracting it on-the-fly from the planned query.

regards, tom lane