hi folks,

this query works fine in 6.5 but screwie in 7.0

7.0 

gm=> SELECT DISTINCT gid FROM members
gm-> WHERE active = 't'
gm-> AND  (gender = 0
gm->         AND  (wantrstypemale LIKE '%Short Term%'
gm->                 OR wantrstypemale like '%Marriage%'
gm->                 OR wantrstypemale like '%Long Term%'
gm->                 OR wantrstypemale like '%Penpal%'
gm->                 OR wantrstypemale like '%Activity Partner%')
gm-> )  order by created desc;
ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list
gm=> 


any idea's ?

jeff




Tue, 30 May 2000 09:28:11 -0300 (ADT)
Jeff MacDonald <jeff@pgsql.com>
7.0 weirdness
It seems to me that it was lack of control in 6.5 version...
For one "gid", you may have several "created" values, so Postgres is not
able to decide which value must be taken and ordered

Simple example
gid         created
1            1
1            3
2            2

In which order is Postgres supposed to give the data???


Patrick Fiche
-----Message d'origine-----
De : pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]De la part
de Jeff MacDonald
Envoyï¿½ : mardi 30 mai 2000 14:28
ï¿½ : pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org
Objet : [SQL] 7.0 weirdness


hi folks,

this query works fine in 6.5 but screwie in 7.0

7.0

gm=> SELECT DISTINCT gid FROM members
gm-> WHERE active = 't'
gm-> AND  (gender = 0
gm->         AND  (wantrstypemale LIKE '%Short Term%'
gm->                 OR wantrstypemale like '%Marriage%'
gm->                 OR wantrstypemale like '%Long Term%'
gm->                 OR wantrstypemale like '%Penpal%'
gm->                 OR wantrstypemale like '%Activity Partner%')
gm-> )  order by created desc;
ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list
gm=>


any idea's ?

jeff





Tue, 30 May 2000 15:17:38 +0200
"Patrick FICHE" <pfiche@prologue-software.fr>
RE: 7.0 weirdness
gid is unique..

jeff

On Tue, 30 May 2000, Patrick FICHE wrote:

> It seems to me that it was lack of control in 6.5 version...
> For one "gid", you may have several "created" values, so Postgres is not
> able to decide which value must be taken and ordered
> 
> Simple example
> gid         created
> 1            1
> 1            3
> 2            2
> 
> In which order is Postgres supposed to give the data???
> 
> 
> Patrick Fiche
> -----Message d'origine-----
> De : pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]De la part
> de Jeff MacDonald
> Envoy� : mardi 30 mai 2000 14:28
> � : pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org
> Objet : [SQL] 7.0 weirdness
> 
> 
> hi folks,
> 
> this query works fine in 6.5 but screwie in 7.0
> 
> 7.0
> 
> gm=> SELECT DISTINCT gid FROM members
> gm-> WHERE active = 't'
> gm-> AND  (gender = 0
> gm->         AND  (wantrstypemale LIKE '%Short Term%'
> gm->                 OR wantrstypemale like '%Marriage%'
> gm->                 OR wantrstypemale like '%Long Term%'
> gm->                 OR wantrstypemale like '%Penpal%'
> gm->                 OR wantrstypemale like '%Activity Partner%')
> gm-> )  order by created desc;
> ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list
> gm=>
> 
> 
> any idea's ?
> 
> jeff
> 
> 
> 
> 


Tue, 30 May 2000 10:46:30 -0300 (ADT)
Jeff MacDonald <jeff@pgsql.com>
RE: 7.0 weirdness