From richard@presenceweb.com Fri Sep 14 10:20:13 2001 Received: from presence1.observatoiredesmarques.fr (presence1.observatoiredesmarques.fr [212.73.199.68]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8EAKD700389 for ; Fri, 14 Sep 2001 06:20:13 -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 KAA30559 for ; Fri, 14 Sep 2001 10:21:10 GMT Message-ID: <3BA1D7DD.569B218C@presenceweb.com> Date: Fri, 14 Sep 2001 12:11:41 +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: pgsql-sql@postgresql.org Subject: SQL request change when upgrade from 7.0.2 to 7.1.3 Content-Type: multipart/alternative; boundary="------------A4989322FF2BF91F3036FD06" X-Archive-Number: 200109/73 X-Sequence-Number: 4719 --------------A4989322FF2BF91F3036FD06 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hello, Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. But, now, one request which was 'good' before, don't want to work any more now. It was : (pretty long) SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu, cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and aes.sect_id <> 9 and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56 UNION SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id, cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and aes.sect_id <> 9 and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and aes.ent_id not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and sect_id <> 3 and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type, e.nom Now, if I want that my request works well, I have to remove the order by statement. But, of course, it is not ordered any more. So how can I translate this request to one which can work with an order by statement ? Thanks. -- Richard NAGY Presenceweb --------------A4989322FF2BF91F3036FD06 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hello,

Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. But, now, one request which was 'good' before, don't want to work any more now.

It was : (pretty long)

SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu,
cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and aes.sect_id <> 9
and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56
UNION
SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id,
cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and aes.sect_id <> 9
and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and aes.ent_id
not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and sect_id <> 3
and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type, e.nom

Now, if I want that my request works well, I have to remove the order by statement. But, of course, it is not ordered any more.

So how can I translate this request to one which can work with an order by statement ?

Thanks.

--
Richard NAGY
Presenceweb
  --------------A4989322FF2BF91F3036FD06-- From josh@agliodbs.com Fri Sep 14 15:19:01 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8EFJ1789175 for ; Fri, 14 Sep 2001 11:19:01 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 119029; Fri, 14 Sep 2001 08:18:42 -0700 From: "Josh Berkus" Subject: Re: SQL request change when upgrade from 7.0.2 to 7.1.3 To: Richard NAGY Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Fri, 14 Sep 2001 08:18:42 -0700 Message-ID: In-Reply-To: <3BA1D7DD.569B218C@presenceweb.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===119029====davinci.ethosmedia.com===_" X-Archive-Number: 200109/86 X-Sequence-Number: 4745 This is a multi-part MIME message --_===119029====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Mr. Nagy, > Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. > But, > now, one request which was 'good' before, don't want to work any more > now. I'm not sure I understand your question. What do you mean "doesn't work"? Please give a detailed list of all steps taken, including any error messages received. > Now, if I want that my request works well, I have to remove the order > by > statement. But, of course, it is not ordered any more. Er, by "request" do you mean "query"? Using an ORDER BY statement as you appear to use it in that query is permitted and correct. I suspect that your problem is located somewhere else. For example, what interface tool are you using to send queries to the database? -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco --_===119029====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===119029====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===119029====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===119029====davinci.ethosmedia.com===_-- From A_Schnabel@t-online.de Fri Sep 14 15:31:01 2001 Received: from mailout06.sul.t-online.de (mailout06.sul.t-online.com [194.25.134.19]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8EFV1793030 for ; Fri, 14 Sep 2001 11:31:01 -0400 (EDT) (envelope-from A_Schnabel@t-online.de) Received: from fwd07.sul.t-online.de by mailout06.sul.t-online.de with smtp id 15huva-000303-0C; Fri, 14 Sep 2001 17:30:50 +0200 Received: from taska (520019441306-0001@[217.80.136.117]) by fwd07.sul.t-online.com with smtp id 15huvO-0rrNvkC; Fri, 14 Sep 2001 17:30:38 +0200 Message-ID: <001901c13d32$10498f80$0201a8c0@aschnabel.homeip.net> From: A_Schnabel@t-online.de (Andre Schnabel) To: "Richard NAGY" , References: <3BA1D7DD.569B218C@presenceweb.com> Subject: Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Date: Fri, 14 Sep 2001 17:29:36 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Sender: 520019441306-0001@t-dialin.net X-Archive-Number: 200109/80 X-Sequence-Number: 4733 Can you post the exact errormessage? ----- Original Message ----- From: "Richard NAGY" To: Sent: Friday, September 14, 2001 12:11 PM Subject: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3 > Hello, > > Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. But, > now, one request which was 'good' before, don't want to work any more > now. > > It was : (pretty long) > > SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56 > UNION > SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and > aes.ent_id > not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and > sect_id <> 3 > and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type, > e.nom > > Now, if I want that my request works well, I have to remove the order by > statement. But, of course, it is not ordered any more. > > So how can I translate this request to one which can work with an order > by statement ? > > Thanks. > > -- > Richard NAGY > Presenceweb > > > From richard@presenceweb.com Fri Sep 14 17:39:56 2001 Received: from presence1.observatoiredesmarques.fr (presence1.observatoiredesmarques.fr [212.73.199.68]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8EHdu733346 for ; Fri, 14 Sep 2001 13:39:56 -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 RAA07829; Fri, 14 Sep 2001 17:40:53 GMT Message-ID: <3BA23EEE.A5DA284B@presenceweb.com> Date: Fri, 14 Sep 2001 19:31:26 +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: Josh Berkus CC: pgsql-sql@postgresql.org Subject: Re: SQL request change when upgrade from 7.0.2 to 7.1.3 References: Content-Type: multipart/alternative; boundary="------------674646B581473C609A987801" X-Archive-Number: 200109/85 X-Sequence-Number: 4743 --------------674646B581473C609A987801 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Josh Berkus a écrit : > Mr. Nagy, > > > Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. > > But, > > now, one request which was 'good' before, don't want to work any more > > now. > > I'm not sure I understand your question. What do you mean "doesn't > work"? Please give a detailed list of all steps taken, including any > error messages received. > > > Now, if I want that my request works well, I have to remove the order > > by > > statement. But, of course, it is not ordered any more. > > Er, by "request" do you mean "query"? > > Using an ORDER BY statement as you appear to use it in that query is > permitted and correct. I suspect that your problem is located somewhere > else. For example, what interface tool are you using to send queries to > the database? > > -Josh > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > > Hello Josh BERKUS, Thanks for your answer and sorry for my english. It was a query and not a request! Well, the query works well on postgresql 7.0.2 but when I upgraded the RDBS to 7.1.3, it did not work any more. The error was : Relation e does not exist. But, in the 'order by' statement, I removed the 'e.'. Now, the query works well without any error. It is ordered but I think it is certainly due to the fact that the table was already ordered on disk. So, I have no more errors but I'm not sure that it is completely good. PS : The interface tool that I have used to send queries to the database was psql. Regards -- Richard NAGY Presenceweb --------------674646B581473C609A987801 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Josh Berkus a écrit :
Mr. Nagy,

> Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3.
> But,
> now, one request which was 'good' before, don't want to work any more
> now.

I'm not sure I understand your question.  What do you mean "doesn't
work"? Please give a detailed list of all steps taken, including any
error messages received.

> Now, if I want that my request works well, I have to remove the order
> by
> statement. But, of course, it is not ordered any more.

Er, by "request" do you mean "query"?

Using an ORDER BY statement as you appear to use it in that query is
permitted and correct.  I suspect that your problem is located somewhere
else.  For example, what interface tool are you using to send queries to
the database?

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco
 
 

Hello Josh BERKUS,

Thanks for your answer and sorry for my english. It was a query and not a request! Well, the query works well on postgresql 7.0.2 but when I upgraded the RDBS to 7.1.3, it did not work any more. The error was : Relation e does not exist.
But, in the 'order by' statement, I removed the 'e.'. Now, the query works well without any error. It is ordered but I think it is certainly due to the fact that the table was already ordered on disk. So, I have no more errors but I'm not sure that it is completely good.

PS : The interface tool that I have used to send queries to the database was psql.

Regards

--
Richard NAGY
Presenceweb
  --------------674646B581473C609A987801-- From josh@agliodbs.com Fri Sep 14 19:20:01 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8EJK1765450 for ; Fri, 14 Sep 2001 15:20:01 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 119209; Fri, 14 Sep 2001 12:19:58 -0700 From: "Josh Berkus" Subject: Re: SQL request change when upgrade from 7.0.2 to 7.1.3 To: Richard NAGY Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Fri, 14 Sep 2001 12:19:58 -0700 Message-ID: In-Reply-To: <3BA23EEE.A5DA284B@presenceweb.com> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200109/87 X-Sequence-Number: 4746 Richard, I'm curious now. What happens if you remove the table qualifications, e.g.: ORDER BY type, nom; -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco From tgl@sss.pgh.pa.us Sun Sep 16 19:11:55 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8GJBt720316 for ; Sun, 16 Sep 2001 15:11:55 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f8GJBVK18388; Sun, 16 Sep 2001 15:11:31 -0400 (EDT) To: Richard NAGY cc: pgsql-sql@postgresql.org Subject: Re: SQL request change when upgrade from 7.0.2 to 7.1.3 In-reply-to: <3BA1D7DD.569B218C@presenceweb.com> References: <3BA1D7DD.569B218C@presenceweb.com> Comments: In-reply-to Richard NAGY message dated "Fri, 14 Sep 2001 12:11:41 +0200" Date: Sun, 16 Sep 2001 15:11:31 -0400 Message-ID: <18385.1000667491@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200109/92 X-Sequence-Number: 4756 Richard NAGY writes: > SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56 > UNION > SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and > aes.ent_id > not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and > sect_id <> 3 > and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type, > e.nom ORDER BY applied to the result of a UNION has to be on the output columns of the UNION. "e.type" etc are names of input-table columns. Try just "type" and "nom", which will refer to the second and third output columns of the UNION. Pre-7.1 got this wrong (and would sometimes produce wrong output ordering or even a backend crash, if the arms of the UNION didn't all yield the same datatype). regards, tom lane From richard@presenceweb.com Mon Sep 17 08:20:06 2001 Received: from presence1.observatoiredesmarques.fr (presence1.observatoiredesmarques.fr [212.73.199.68]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8H8K6788083 for ; Mon, 17 Sep 2001 04:20:06 -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 HAA19823; Mon, 17 Sep 2001 07:55:05 GMT Message-ID: <3BA5AA17.2C6834B9@presenceweb.com> Date: Mon, 17 Sep 2001 09:45:27 +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: Josh Berkus CC: pgsql-sql@postgresql.org Subject: Re: SQL request change when upgrade from 7.0.2 to 7.1.3 References: Content-Type: multipart/alternative; boundary="------------0659DE52D6245E4AF9D96201" X-Archive-Number: 200109/98 X-Sequence-Number: 4762 --------------0659DE52D6245E4AF9D96201 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Josh Berkus a écrit : > Richard, > > I'm curious now. What happens if you remove the table qualifications, > e.g.: > > ORDER BY type, nom; > > -Josh > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco Hello Josh, Yes, it works! I don't know exactly why, but it works! Thanks a lot. -- Richard NAGY Presenceweb --------------0659DE52D6245E4AF9D96201 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Josh Berkus a écrit :
Richard,

I'm curious now.  What happens if you remove the table qualifications,
e.g.:

ORDER BY type, nom;

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Hello Josh,

Yes, it works! I don't know exactly why, but it works!
Thanks a lot.

--
Richard NAGY
Presenceweb
  --------------0659DE52D6245E4AF9D96201-- From richard@presenceweb.com Mon Sep 17 08:24:03 2001 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-- From richard@presenceweb.com Mon Sep 17 08:36:38 2001 Received: from presence1.observatoiredesmarques.fr (presence1.observatoiredesmarques.fr [212.73.199.68]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8H8ac792724 for ; Mon, 17 Sep 2001 04:36:38 -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 IAA20619; Mon, 17 Sep 2001 08:37:31 GMT Message-ID: <3BA5B409.B57A88D2@presenceweb.com> Date: Mon, 17 Sep 2001 10:27:53 +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: Tom Lane 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> <18385.1000667491@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------788CA54A1D49006E171C7FE2" X-Archive-Number: 200109/100 X-Sequence-Number: 4764 --------------788CA54A1D49006E171C7FE2 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Tom Lane a écrit : > Richard NAGY writes: > > SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu, > > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > > aes.sect_id <> 9 > > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56 > > UNION > > SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id, > > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > > aes.sect_id <> 9 > > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and > > aes.ent_id > > not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and > > sect_id <> 3 > > and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type, > > e.nom > > ORDER BY applied to the result of a UNION has to be on the output > columns of the UNION. "e.type" etc are names of input-table columns. > Try just "type" and "nom", which will refer to the second and third > output columns of the UNION. > > Pre-7.1 got this wrong (and would sometimes produce wrong output > ordering or even a backend crash, if the arms of the UNION didn't > all yield the same datatype). > > regards, tom lane Hello Tom, Yes, thanks a lot. It works! Regards. -- Richard NAGY Presenceweb --------------788CA54A1D49006E171C7FE2 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane a écrit :
Richard NAGY <richard@presenceweb.com> writes:
> SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu,
> cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
> entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
> aes.sect_id <> 9
> and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56
> UNION
> SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id,
> cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
> entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
> aes.sect_id <> 9
> and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and
> aes.ent_id
> not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and
> sect_id <> 3
> and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type,
> e.nom

ORDER BY applied to the result of a UNION has to be on the output
columns of the UNION.  "e.type" etc are names of input-table columns.
Try just "type" and "nom", which will refer to the second and third
output columns of the UNION.

Pre-7.1 got this wrong (and would sometimes produce wrong output
ordering or even a backend crash, if the arms of the UNION didn't
all yield the same datatype).

                        regards, tom lane

Hello Tom,

Yes, thanks a lot. It works!
Regards.

--
Richard NAGY
Presenceweb
  --------------788CA54A1D49006E171C7FE2--