Re: pg_dump and join aliases (was Re: [BUGS] ERROR: cannot handle unplanned sub-select)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org, Merlin Moncure <mmoncure(at)gmail(dot)com>
Subject: Re: pg_dump and join aliases (was Re: [BUGS] ERROR: cannot handle unplanned sub-select)
Date: 2010-07-07 23:43:44
Message-ID: 6742.1278546224@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I'm lost. What's a join alias var?

Suppose we have t1 with columns a,b,c and t2 with columns d,e,f, then
consider

select a from t1 join t2 on (whatever)
select t1.a from t1 join t2 on (whatever)

In the first case the parser generates a Var that references a column of
the unnamed join's RTE; in the second case you get a Var that references
t1 directly. These particular cases are semantically equivalent, but
there are lots of other cases where it's important to draw the
distinction. One interesting example is

select x from (t1 join t2 on (whatever)) as j(x,y,z,xx,yy,zz)

where per SQL spec it'd actually be illegal to write a (or t1.a) because
the named join hides its components. But I think what forced us to have
different representations is FULL JOIN USING. If you have

select id from taba full join tabb using (id)

then taba.id and tabb.id and the join's output variable id are all
semantically different and *must* be given different representations at
the Var level.

Anyway, the way it works is that the parser generates "alias Vars" that
refer to the join RTE, mainly because this makes life simpler for
ruleutils. But the planner prefers to work with the "real" underlying
columns whenever those are semantically equivalent, so it has a pass
that does the replacement, and that's what's broken ...

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Robert Haas 2010-07-08 01:04:18 Re: pg_dump and join aliases (was Re: [BUGS] ERROR: cannot handle unplanned sub-select)
Previous Message Robert Haas 2010-07-07 23:04:31 Re: pg_dump and join aliases (was Re: [BUGS] ERROR: cannot handle unplanned sub-select)

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2010-07-08 00:29:43 Re: Bug? Concurrent COMMENT ON and DROP object
Previous Message Robert Haas 2010-07-07 23:09:43 Re: Reviewfest 2010-06 Plans and Call for Reviewers