Thread

1. select a.name ... union select a.name ... order by a.name fails in 7.1

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-04-23T01:08:14Z

   Palle Girgensohn (girgen@partitur.se) reports a bug with a severity of 2
   The lower the number the more severe it is.
   
   Short Description
   select a.name ... union select a.name ... order by a.name fails in 7.1
   
   Long Description
   Hi!
   
   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
   
   This works fine in postgres 7.0.x
   
   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 :(
   
   Is it a bug, or was it a feature in 7.0?
   
   /Palle
   
   Sample Code
   fails on 7.1, work on 7.0.3:
   
   select a.name from users a 
   union
   select a.name from oldusers a
   order by a.userid;
   
   
   modifying last line ot look like 'order by userid' fixes the problem.
   
   No file was uploaded with this report
   
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-23T02:11:22Z

   girgen@partitur.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
   
   

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

   Palle Girgensohn <girgen@partitur.se> — 2001-04-23T02:25:10Z

   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@partitur.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