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