Thread
-
Re: 7.0 weirdness
omid omoomi <oomoomi@hotmail.com> — 2000-05-30T06:46:45Z
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
-
Re: 7.0 weirdness
Jeff MacDonald <jeff@pgsql.com> — 2000-05-30T14:10:24Z
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 >
-
Re: [HACKERS] Re: 7.0 weirdness
Matthias Urlichs <smurf@noris.de> — 2000-05-30T14:31:35Z
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
-
Re: 7.0 weirdness
Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-30T14:44:59Z
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
-
Re: 7.0 weirdness
Peter Vazsonyi <neko@kredit.sth.szif.hu> — 2000-05-31T06:45:08Z
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;(
-
Re: [HACKERS] Re: 7.0 weirdness
Jeff MacDonald <jeff@pgsql.com> — 2000-05-31T19:00:55Z
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 >