Обсуждение: Parser/planner and column aliases

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

> 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:

Hmm. Wildcarding like this works:

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

So my problems are maybe just within the parser. Will keep looking at
it...
                     - Thomas

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

Ha, got it:

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

so never mind...
                  - Thomas

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

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> 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:

> 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.00 rows=1000 width=8)

Man, that's weird-looking.  What happened to the table name in the
second Seq Scan line?  I think you must be passing a broken rangetable
list.

My guess is that expansion of "*" is somehow failing to recognize that
it should be using the same RTE for all columns, and is causing an
extra bogus RTE to get added to the list.  Put two RTEs in there and
you get a join...
        regards, tom lane

BTW, this example reminds me once again that un-pretty-printed
EXPLAIN VERBOSE output is damn near unreadable.  Would anyone object
if it got formatted the same as what goes to the postmaster log?
(It used to be unsafe to do that, but now that we can cope with
unlimited-length NOTICE messages I see no real good reason not to
format it more nicely.)