Thread: Bug: aliasing in ORDER BY when UNIONing

What works:

# select o.id from op o order by o.id;
# select o.id from op o union all SELECT -1 order by id;

Does not work:

# select o.id from op o union all SELECT -1 order by o.id;
ERROR:  Relation 'o' does not exist
# select o.id from op o union all SELECT -1 from op o order by o.id;
ERROR:  Relation 'o' does not exist


Running today's CVS.  (I finally converted my main workstation
to 7.1...)

-- 
marko

Marko Kreen <marko@l-t.ee> writes:
> What works:
> # select o.id from op o union all SELECT -1 order by id;

This is valid SQL.

> # select o.id from op o union all SELECT -1 order by o.id;
> ERROR:  Relation 'o' does not exist

This is not valid SQL.  For one thing, the table alias "o" is not
visible outside the first component SELECT.

Yes, I know 7.0 took it... but its handling of ORDER BY on UNION
was pretty darn broken.
        regards, tom lane

On Sun, Feb 18, 2001 at 08:24:20PM -0500, Tom Lane wrote:
> Marko Kreen <marko@l-t.ee> writes:
> > What works:
> > # select o.id from op o union all SELECT -1 order by id;
> 
> This is valid SQL.
> 
> > # select o.id from op o union all SELECT -1 order by o.id;
> > ERROR:  Relation 'o' does not exist
> 
> This is not valid SQL.  For one thing, the table alias "o" is not
> visible outside the first component SELECT.
> 
> Yes, I know 7.0 took it... but its handling of ORDER BY on UNION
> was pretty darn broken.

Doh.  But if I have several tables with a field 'id'?  Then only
way is to use the column number?  But the query is big and composed
of several sources, fields and other stuff is separated - oh
well...  Thankfully the field is not 'id' so maybe its not that
bad.

Anyway such stuff should be documented I guess.  From current
docs I read that it should work.  I would have expected that one
of the select's aliases would be transferred to ORDER BY but its
not possible?

-- 
marko

Marko Kreen <marko@l-t.ee> writes:
> # select o.id from op o union all SELECT -1 order by o.id;
> ERROR:  Relation 'o' does not exist
>> 
>> This is not valid SQL.  For one thing, the table alias "o" is not
>> visible outside the first component SELECT.
>> 
>> Yes, I know 7.0 took it... but its handling of ORDER BY on UNION
>> was pretty darn broken.

> Doh.  But if I have several tables with a field 'id'?  Then only
> way is to use the column number?

You could assign column names:

SELECT o.id as id1, p.id as id2, ... UNION ... ORDER BY id1, id2;

> Anyway such stuff should be documented I guess.  From current
> docs I read that it should work.

Where?

> I would have expected that one
> of the select's aliases would be transferred to ORDER BY but its
> not possible?

The first subselect's column names are transferred to ORDER BY.
        regards, tom lane

On Mon, Feb 19, 2001 at 12:26:44AM -0500, Tom Lane wrote:
> Marko Kreen <marko@l-t.ee> writes:
> > Anyway such stuff should be documented I guess.  From current
> > docs I read that it should work.
> 
> Where?

And ofcourse, you are right :)  I was confused of result columns
vs. table columns but in the ORDER BY desc there is even
explicitly said result columns.

Thanks.

-- 
marko