Skip site navigation (1) Skip section navigation (2)

Re: select a.name ... union select a.name ... order by a.name fails in 7.1

From: Palle Girgensohn <girgen(at)partitur(dot)se>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: select a.name ... union select a.name ... order by a.name fails in 7.1
Date: 2001-04-23 02:25:10
Message-ID: 3AE39286.9D4C8A39@partitur.se (view raw or flat)
Thread:
Lists: pgsql-bugs
Tom,

thanks for your input. Now I have something to bash my
programmers over the head with ;-)

No, just kidding... But as you say, it is plain bad SQL, I
realize this fully now. Oddly enough, I have a SQL book that
has one or two examples with order by a.foobar. Oh well...
Thanks for the prompt answer!

/Palle

Tom Lane wrote:
> 
> girgen(at)partitur(dot)se writes:
> > I'm not certain this is correct SQL, and I know the rewrite is
> > easy, but here is a difference I found between 7.0.3 and 7.1:
> 
> > select a.name from users a
> > union
> > select a.name from oldusers a
> > order by a.userid;
> 
> > ERROR:  Relation 'a' does not exist
> 
> It's not correct SQL, and Postgres doesn't support it.
> 
> > This works fine in postgres 7.0.x
> 
> No it doesn't.  Consider the following example (done with 7.0.*):
> 
> play=> create table foo (f1 int, f2 int);
> CREATE
> play=> insert into foo values (1,10);
> INSERT 1021258 1
> play=> insert into foo values (2,9);
> INSERT 1021259 1
> play=> insert into foo values (3,8);
> INSERT 1021260 1
> play=> select a.f1 from foo a union select a.f1 from foo a;
>  f1
> ----
>   1
>   2
>   3
> (3 rows)
> 
> -- so far so good, but:
> 
> play=> select a.f1 from foo a union select a.f1 from foo a
> play-> order by a.f2;
>  f1
> ----
>   3
>   2
>   1
>   2
>   3
> (5 rows)
> 
> -- wow, ORDER BY produces a different resultset!
> 
> While this is obviously broken, the more fundamental point is that
> the only sensible ordering of a UNION result is on one of the result
> columns.  Otherwise the ordering isn't well-defined: if the UNION merges
> equal values of "name" from the two SELECTs, which SELECT's "userid"
> will get used for the sort?  So the SQL standard mandates ORDER BY
> only on output column names or numbers, and we enforce that in 7.1.
> Prior versions failed to notice that there was a problem, and would
> do something fairly random instead :-(
> 
> > the simple rewrite is of course 'order by userid', but it is
> > tedious to find all places in our system where this happens. It
> > seems some programmers have used the above syntax a lot :(
> 
> They haven't thought about whether the query is reasonable.
> I'd also say that they never looked closely to see if the output
> they were getting was reasonable ...
> 
>                         regards, tom lane

In response to

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2001-04-23 10:37:24
Subject: Compile file if using readline-4.2 (PostGres 7.1)
Previous:From: pgsql-bugsDate: 2001-04-23 02:18:00
Subject: jdbc2 fails to getDate

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group