Received: from presence1.observatoiredesmarques.fr (presence1.observatoiredesmarques.fr [212.73.199.68]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8H8O2788799 for ; Mon, 17 Sep 2001 04:24:03 -0400 (EDT) (envelope-from richard@presenceweb.com) Received: from presenceweb.com (gatei.observatoiredesmarques.fr [213.56.32.22]) by presence1.observatoiredesmarques.fr (8.9.1/_ODM_070798) with ESMTP id IAA20381; Mon, 17 Sep 2001 08:25:01 GMT Message-ID: <3BA5B118.D4751A2D@presenceweb.com> Date: Mon, 17 Sep 2001 10:15:20 +0200 From: Richard NAGY Organization: Presenceweb X-Mailer: Mozilla 4.72 [fr] (X11; U; Linux 2.2.14-6.1.1smp i686) X-Accept-Language: fr, en MIME-Version: 1.0 To: Andre Schnabel CC: pgsql-sql@postgresql.org Subject: Re: SQL request change when upgrade from 7.0.2 to 7.1.3 References: <3BA1D7DD.569B218C@presenceweb.com> <001901c13d32$10498f80$0201a8c0@aschnabel.homeip.net> <3BA24029.BE1B71E1@presenceweb.com> <001c01c13d56$09c260a0$0201a8c0@aschnabel.homeip.net> Content-Type: multipart/alternative; boundary="------------058425B57F34644F606567F1" X-Archive-Number: 200109/99 X-Sequence-Number: 4763 --------------058425B57F34644F606567F1 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Andre Schnabel a écrit : > Hello Richard, > > I did some testing and after all your query should be ordered right. > The test's I have done: > > Test=# select t.foo1 from testtable t > Test-# union > Test-# select t.foo2 from testtable t > Test-# order by t.foo1; > ERROR: Relation 't' does not exist > > -------- Same error as you get ---- > > Test=# select t.foo1 from testtable t > Test-# union > Test-# select t.foo2 from testtable t > Test-# order by foo1; > foo1 > ------ > abc > cdef > (2 rows) > > -------- Ordered Ascending (maybe by chance?) --- > > Test=# select t.foo1 from testtable t > Test-# union > Test-# select t.foo2 from testtable t > Test-# order by foo1 DESC; > foo1 > ------ > cdef > abc > (2 rows) > > ---- Ordered descending (ordering works!) --- > > I guess, the table-alias is not known to the order-clause. Maybe the > execution (or parsing) order of the UNION changed from 7.0 to 7.1. Hello Andre, Thanks very much for having tested. Yes, it works well. -- Richard NAGY Presenceweb --------------058425B57F34644F606567F1 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Andre Schnabel a écrit :
Hello Richard,

I did some testing and after all your query should be ordered right.
The test's I have done:

Test=# select t.foo1 from testtable t
Test-# union
Test-# select t.foo2 from testtable t
Test-# order by t.foo1;
ERROR:  Relation 't' does not exist

-------- Same error as you get ----

Test=# select t.foo1 from testtable t
Test-# union
Test-# select t.foo2 from testtable t
Test-# order by foo1;
 foo1
------
 abc
 cdef
(2 rows)

-------- Ordered Ascending (maybe by chance?) ---

Test=# select t.foo1 from testtable t
Test-# union
Test-# select t.foo2 from testtable t
Test-# order by foo1 DESC;
 foo1
------
 cdef
 abc
(2 rows)

---- Ordered descending (ordering works!) ---

I guess, the table-alias is not known to the order-clause. Maybe the
execution (or parsing) order of the UNION changed from 7.0 to 7.1.

Hello Andre,

Thanks very much for having tested. Yes, it works well.

--
Richard NAGY
Presenceweb
  --------------058425B57F34644F606567F1--