Bug: aliasing in ORDER BY when UNIONing

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