Re: 7.0 weirdness
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
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:
Show quoted text
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 OmoomiFrom: 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
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
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
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;(
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:
Show quoted text
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 3Now, should your query's result be
gid
X
Yor 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