Parser/planner and column aliases - Mailing list pgsql-hackers

OK, on the road toward "outer join syntax"...

I'm implementing the "column alias" features of SQL92, as in

postgres=# select b, c from t2 ty (b, c);b | c 
---+---1 | 11 | 22 | 2
(3 rows)

where the t2 columns are labeled "j, k" when created.

I'm running across the behavior that an explicit select as above
works, but if I try a wildcard expansion (select *...) instead of the
explicit column listing the planner decides it needs to do some wild
nested join stuff:

postgres=# select * from t2 ty (b, c);b | c 
---+---1 | 11 | 22 | 21 | 11 | 22 | 21 | 11 | 22 | 2
(9 rows)

(Darn!)

Explain shows the following for the two cases:

postgres=# explain verbose select b, c from t2 ty (b, c);
NOTICE:  QUERY DUMP:

{ SEQSCAN :cost 43 :rows 1000 :width 8 :state <> :qptargetlist ({
TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resname b :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 
:varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 23 :restypmod -1 :resname c :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 2}}) :qpqual <> :lefttree <> :righttree <> :extprm ()
:locprm () :initplan <> :nprm 0  :scanrelid 1 }
NOTICE:  QUERY PLAN:

Seq Scan on t2 ty  (cost=43.00 rows=1000 width=8)

EXPLAIN
postgres=# explain verbose select * from t2 ty (b, c);
NOTICE:  QUERY DUMP:

{ NESTLOOP :cost 43043 :rows 1000000 :width 12 :state <> :qptargetlist
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resname b :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 65000 :varattno 1 :vartype 23 :vartypmod -1 
:varlevelsup 0 :varnoold 0 :varoattno 1}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 23 :restypmod -1 :resname c :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno
65000 :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold
0 :varoattno 2}}) :qpqual <> :lefttree { SEQSCAN :cost 43 :rows 1000
:width 4 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM
:resno 1 :restype 26 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno -2
:vartype 26 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno -2}})
:qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan
<> :nprm 0  :scanrelid 1 } :righttree { SEQSCAN :cost 43 :rows 1000
:width 8 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM
:resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 1
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 0 :varoattno 1}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1
:resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 0 :varattno 2 :vartype 23 :vartypmod -1 
:varlevelsup 0 :varnoold 0 :varoattno 2}}) :qpqual <> :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 0
} :extprm () :locprm () :initplan <> :nprm 0 }
NOTICE:  QUERY PLAN:

Nested Loop  (cost=43043.00 rows=1000000 width=12) ->  Seq Scan on t2 ty  (cost=43.00 rows=1000 width=4) ->  Seq Scan
(cost=43.00rows=1000 width=8)
 

EXPLAIN


I *think* that the transformed parts of the query tree looks similar
for the two cases coming out of the parser, but clearly something is
different. Does anyone (Tom Lane??) know if the planner reaches back
into the untransformed nodes of the parse tree to get info? The resdom
nodes in the transformed target list look the same for the two cases,
but the planner is generating a bunch of new ones sometime later.

Hints would be appreciated, though I'm pretty sure I'll be able to
track it down even without ;)
                   - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California