pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: 7.0 weirdness
10+ messages / 6 participants
[nested] [flat]
* Re: 7.0 weirdness
@ 2000-05-30 06:46 omid omoomi <oomoomi@hotmail.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 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>
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 13:17 Patrick FICHE <pfiche@prologue-software.fr>
parent: 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 13:46 Jeff MacDonald <jeff@pgsql.com>
parent: Patrick FICHE <pfiche@prologue-software.fr>
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
* Re: 7.0 weirdness
@ 2000-05-30 14:10 Jeff MacDonald <jeff@pgsql.com>
parent: omid omoomi <oomoomi@hotmail.com>
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 14:31 Matthias Urlichs <smurf@noris.de>
parent: 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: 7.0 weirdness
@ 2000-05-30 14:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff MacDonald <jeff@pgsql.com>
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-31 06:45 Peter Vazsonyi <neko@kredit.sth.szif.hu>
parent: Jeff MacDonald <jeff@pgsql.com>
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: [HACKERS] Re: 7.0 weirdness
@ 2000-05-31 19:00 Jeff MacDonald <jeff@pgsql.com>
parent: Matthias Urlichs <smurf@noris.de>
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
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