Обсуждение: 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