Thread: 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
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
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.. 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 > > > >
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 >
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
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 createdX 1Y 2X 3 Now, should your query's result be gidXY or should it be gidYX ? 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 aggregateFROM members --... etcGROUP 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: > 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 >