pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: 7.0 weirdness
10+ messages / 6 participants
[nested] [flat]

* Re: 7.0 weirdness
@ 2000-05-30 06:46 omid omoomi <oomoomi@hotmail.com>
  2000-05-30 14:10 ` Re: 7.0 weirdness Jeff MacDonald <jeff@pgsql.com>
  0 siblings, 1 reply; 10+ messages in thread

From: omid omoomi @ 2000-05-30 06:46 UTC (permalink / raw)
  To: jeff@pgsql.com; pgsql-sql; pgsql-hackers@postgresql.org

Hi jeff,
I'm not sure but may be that's because you are using select distinct and so 
there would be a few rows with same "gid" but different "created" fields in 
your table . And PG does not know which one to select and compare for ORDER 
BY clause. If that ,you would need to change the table structure to a better 
normal form.
Regards ,
Omid Omoomi


>From: Jeff MacDonald <jeff@pgsql.com>
>Reply-To: Jeff MacDonald <jeff@pgsql.com>
>To: pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org
>Subject: [SQL] 7.0 weirdness
>Date: Tue, 30 May 2000 09:28:11 -0300 (ADT)
>
>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
>
>
>

________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: 7.0 weirdness
  2000-05-30 06:46 Re: 7.0 weirdness omid omoomi <oomoomi@hotmail.com>
@ 2000-05-30 14:10 ` Jeff MacDonald <jeff@pgsql.com>
  2000-05-30 14:31   ` Re: [HACKERS] Re: 7.0 weirdness Matthias Urlichs <smurf@noris.de>
  2000-05-30 14:44   ` Re: 7.0 weirdness Tom Lane <tgl@sss.pgh.pa.us>
  2000-05-31 06:45   ` Re: 7.0 weirdness Peter Vazsonyi <neko@kredit.sth.szif.hu>
  0 siblings, 3 replies; 10+ messages in thread

From: Jeff MacDonald @ 2000-05-30 14:10 UTC (permalink / raw)
  To: omid omoomi <oomoomi@hotmail.com>; +Cc: jeff@pgsql.com; pgsql-sql; pgsql-hackers@postgresql.org

gid is unique.. it's a serial..

funny thing is tho this worked on 6.5
oh well thanks for the info.


jeff

On Tue, 30 May 2000, omid omoomi wrote:

> Hi jeff,
> I'm not sure but may be that's because you are using select distinct and so 
> there would be a few rows with same "gid" but different "created" fields in 
> your table . And PG does not know which one to select and compare for ORDER 
> BY clause. If that ,you would need to change the table structure to a better 
> normal form.
> Regards ,
> Omid Omoomi
> 
> 
> >From: Jeff MacDonald <jeff@pgsql.com>
> >Reply-To: Jeff MacDonald <jeff@pgsql.com>
> >To: pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org
> >Subject: [SQL] 7.0 weirdness
> >Date: Tue, 30 May 2000 09:28:11 -0300 (ADT)
> >
> >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
> >
> >
> >
> 
> ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> 




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [HACKERS] Re: 7.0 weirdness
  2000-05-30 06:46 Re: 7.0 weirdness omid omoomi <oomoomi@hotmail.com>
  2000-05-30 14:10 ` Re: 7.0 weirdness Jeff MacDonald <jeff@pgsql.com>
@ 2000-05-30 14:31   ` Matthias Urlichs <smurf@noris.de>
  2000-05-31 19:00     ` Re: [HACKERS] Re: 7.0 weirdness Jeff MacDonald <jeff@pgsql.com>
  2 siblings, 1 reply; 10+ messages in thread

From: Matthias Urlichs @ 2000-05-30 14:31 UTC (permalink / raw)
  To: Jeff MacDonald <jeff@pgsql.com>; +Cc: omid omoomi <oomoomi@hotmail.com>; pgsql-sql; pgsql-hackers@postgresql.org

Hi,

Jeff MacDonald:
> gid is unique.. it's a serial..
> 
Then there is no point in using "DISTINCT" in the first place, is there?

> funny thing is tho this worked on 6.5

It happened to work because your gid is unique. But in the general case,
it can't work. Consider this table:

gid  created
 X      1
 Y      2
 X      3

Now, should your query's result be

gid
 X
 Y

or should it be

gid
 Y
 X

? And since the typical implementation throws away non-selected-for
columns before UNIQUEing, how should it be able to sort anything?

-- 
Matthias Urlichs  |  noris network GmbH   |   smurf@noris.de  |  ICQ: 20193661
The quote was selected randomly. Really.       |        http://smurf.noris.de/
-- 
Problem mit cookie: File exists 



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [HACKERS] Re: 7.0 weirdness
  2000-05-30 06:46 Re: 7.0 weirdness omid omoomi <oomoomi@hotmail.com>
  2000-05-30 14:10 ` Re: 7.0 weirdness Jeff MacDonald <jeff@pgsql.com>
  2000-05-30 14:31   ` Re: [HACKERS] Re: 7.0 weirdness Matthias Urlichs <smurf@noris.de>
@ 2000-05-31 19:00     ` Jeff MacDonald <jeff@pgsql.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Jeff MacDonald @ 2000-05-31 19:00 UTC (permalink / raw)
  To: Matthias Urlichs <smurf@noris.de>; +Cc: Jeff MacDonald <jeff@pgsql.com>; omid omoomi <oomoomi@hotmail.com>; pgsql-sql; pgsql-hackers@postgresql.org

thanks for the hlep guys..

for those that are curious, the distinct is tehr cause it's
someone elses code that i'm workig on .. :) have to kick
out the bug's//

jeff

On Tue, 30 May 2000, Matthias Urlichs wrote:

> Hi,
> 
> Jeff MacDonald:
> > gid is unique.. it's a serial..
> > 
> Then there is no point in using "DISTINCT" in the first place, is there?
> 
> > funny thing is tho this worked on 6.5
> 
> It happened to work because your gid is unique. But in the general case,
> it can't work. Consider this table:
> 
> gid  created
>  X      1
>  Y      2
>  X      3
> 
> Now, should your query's result be
> 
> gid
>  X
>  Y
> 
> or should it be
> 
> gid
>  Y
>  X
> 
> ? And since the typical implementation throws away non-selected-for
> columns before UNIQUEing, how should it be able to sort anything?
> 
> -- 
> Matthias Urlichs  |  noris network GmbH   |   smurf@noris.de  |  ICQ: 20193661
> The quote was selected randomly. Really.       |        http://smurf.noris.de/
> -- 
> Problem mit cookie: File exists 
> 




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: 7.0 weirdness
  2000-05-30 06:46 Re: 7.0 weirdness omid omoomi <oomoomi@hotmail.com>
  2000-05-30 14:10 ` Re: 7.0 weirdness Jeff MacDonald <jeff@pgsql.com>
@ 2000-05-30 14:44   ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 10+ messages in thread

From: Tom Lane @ 2000-05-30 14:44 UTC (permalink / raw)
  To: Jeff MacDonald <jeff@pgsql.com>; +Cc: omid omoomi <oomoomi@hotmail.com>; pgsql-sql; pgsql-hackers@postgresql.org

Jeff MacDonald <jeff@pgsql.com> writes:
> gid is unique.. it's a serial..

Mph.  If you assume that gid is unique then the query would give
well-defined results, but if you know it's unique then why don't
you just leave off the DISTINCT?

> funny thing is tho this worked on 6.5

No, 6.5 merely failed to notice that it was giving you undefined
results.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: 7.0 weirdness
  2000-05-30 06:46 Re: 7.0 weirdness omid omoomi <oomoomi@hotmail.com>
  2000-05-30 14:10 ` Re: 7.0 weirdness Jeff MacDonald <jeff@pgsql.com>
@ 2000-05-31 06:45   ` Peter Vazsonyi <neko@kredit.sth.szif.hu>
  2 siblings, 0 replies; 10+ messages in thread

From: Peter Vazsonyi @ 2000-05-31 06:45 UTC (permalink / raw)
  To: Jeff MacDonald <jeff@pgsql.com>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

Hi Jeff!

I think you need a solution, and not explains...
Tom, and the others told the truth. You missed this query.

> gid is unique.. it's a serial..
I give you two ways:

1) gid __realy__ unique  ->  DISTINCT is unnecessary.
 SELECT gid FROM members -- ... etc 

2) gid not unique  ->  DISTINCT is not enough. ;(
 SELECT gid,MAX(created) -- or MIN or AVG ... any aggregate
	FROM members -- ... etc
 GROUP BY gid ORDER BY 2; -- second colunm

> > >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 

best regards
--
 nek;(




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* RE: 7.0 weirdness
@ 2000-05-30 07:13 omid omoomi <oomoomi@hotmail.com>
  0 siblings, 0 replies; 10+ messages in thread

From: omid omoomi @ 2000-05-30 07:13 UTC (permalink / raw)
  To: jeff@pgsql.com; pfiche@prologue-software.fr; +Cc: pgsql-sql

If so then why do you need to use "select distinct " ?!
BTW, I just tested on my 6.5.2, This query :
"Select distinct f1 from table1 order by f2 "
works the same as this one:
"select distinct f1,f2 from table1"


>From: Jeff MacDonald <jeff@pgsql.com>
>Reply-To: Jeff MacDonald <jeff@pgsql.com>
>To: Patrick FICHE <pfiche@prologue-software.fr>
>CC: Jeff MacDonald <jeff@pgsql.com>, Pgsql-Sql <pgsql-sql@postgresql.org>
>Subject: RE: [SQL] 7.0 weirdness
>Date: Tue, 30 May 2000 10:46:30 -0300 (ADT)
>
>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
> >
> >
> >
> >
>

________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* 7.0 weirdness
@ 2000-05-30 12:28 Jeff MacDonald <jeff@pgsql.com>
  2000-05-30 13:17 ` RE: 7.0 weirdness Patrick FICHE <pfiche@prologue-software.fr>
  0 siblings, 1 reply; 10+ messages in thread

From: Jeff MacDonald @ 2000-05-30 12:28 UTC (permalink / raw)
  To: pgsql-sql; pgsql-hackers@postgresql.org

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






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* RE: 7.0 weirdness
  2000-05-30 12:28 7.0 weirdness Jeff MacDonald <jeff@pgsql.com>
@ 2000-05-30 13:17 ` Patrick FICHE <pfiche@prologue-software.fr>
  2000-05-30 13:46   ` RE: 7.0 weirdness Jeff MacDonald <jeff@pgsql.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Patrick FICHE @ 2000-05-30 13:17 UTC (permalink / raw)
  To: Jeff MacDonald <jeff@pgsql.com>; +Cc: pgsql-sql

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







^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* RE: 7.0 weirdness
  2000-05-30 12:28 7.0 weirdness Jeff MacDonald <jeff@pgsql.com>
  2000-05-30 13:17 ` RE: 7.0 weirdness Patrick FICHE <pfiche@prologue-software.fr>
@ 2000-05-30 13:46   ` Jeff MacDonald <jeff@pgsql.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Jeff MacDonald @ 2000-05-30 13:46 UTC (permalink / raw)
  To: Patrick FICHE <pfiche@prologue-software.fr>; +Cc: Jeff MacDonald <jeff@pgsql.com>; pgsql-sql

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
> 
> 
> 
> 




^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2000-05-31 19:00 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-30 06:46 Re: 7.0 weirdness omid omoomi <oomoomi@hotmail.com>
2000-05-30 14:10 ` Jeff MacDonald <jeff@pgsql.com>
2000-05-30 14:31   ` Matthias Urlichs <smurf@noris.de>
2000-05-31 19:00     ` Jeff MacDonald <jeff@pgsql.com>
2000-05-30 14:44   ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-31 06:45   ` Peter Vazsonyi <neko@kredit.sth.szif.hu>
2000-05-30 07:13 RE: 7.0 weirdness omid omoomi <oomoomi@hotmail.com>
2000-05-30 12:28 7.0 weirdness Jeff MacDonald <jeff@pgsql.com>
2000-05-30 13:17 ` Patrick FICHE <pfiche@prologue-software.fr>
2000-05-30 13:46   ` Jeff MacDonald <jeff@pgsql.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox