Re: [BUGS] select a.name ... union select a.name ... order by a.name fails in 7.1
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: > > [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] select a.name ... union select a.name ... order by a.name fails in 7.1
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 -- Partitur Informationsteknik AB Wenner-Gren Center +46 8 566 280 02 113 46 Stockholm +46 70 785 86 02 Sweden [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] select a.name ... union select a.name ... order by a.name fails in 7.1
[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] select a.name ... union select a.name ... order by a.name fails in 7.1
Palle Girgensohn ([EMAIL PROTECTED]) 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]