Обсуждение: select DISTINCT not ordering the returned rows
Hi Everyone, I would like to ask for your help finding a temporary solution for my problem. I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of the returned rows. The following script is a simplification of my real case: create table tmp_1 (field1 integer, field2 integer); insert into tmp_1 values (1, 3); insert into tmp_1 values (1, 3); insert into tmp_1 values (1, 3); insert into tmp_1 values (1, 3); insert into tmp_1 values (1, 3); insert into tmp_1 values (1, 4); insert into tmp_1 values (1, 4); insert into tmp_1 values (1, 4); insert into tmp_1 values (1, 4); insert into tmp_1 values (1, 4); insert into tmp_1 values (1, 4); insert into tmp_1 values (1, 1029); insert into tmp_1 values (1, 1101); insert into tmp_1 values (13, 3); insert into tmp_1 values (13, 3); insert into tmp_1 values (13, 3); insert into tmp_1 values (13, 3); insert into tmp_1 values (13, 3); insert into tmp_1 values (13, 3); insert into tmp_1 values (13, 4); insert into tmp_1 values (13, 4); insert into tmp_1 values (13, 4); insert into tmp_1 values (13, 4); insert into tmp_1 values (13, 4); insert into tmp_1 values (13, 4); insert into tmp_1 values (13, 4); insert into tmp_1 values (13, 1029); insert into tmp_1 values (13, 1101); analyze tmp_1; SELECT distinct field2 FROM tmp_1 WHERE field1 = 13; The result in postgres 8.3 is as follows: 3 4 1029 1101 And it stays the same no matter what the physical order of the records is in the table. I can do random update and I getthe same results. It looks like the result is ordered by the distinct fields... The result in postgres 9.0 is as follows: 3 4 1101 1029 not ordered by the distinct fields nor physical order... I am wondering if there is a temporary solution (updates, indexes, ...) to order the result by field1 without changing thestatement... Thank you in advance, Ioana Danes
I found it: disabling enable_hashagg --- On Wed, 3/2/11, Ioana Danes <ioanasoftware@yahoo.ca> wrote: > From: Ioana Danes <ioanasoftware@yahoo.ca> > Subject: [GENERAL] select DISTINCT not ordering the returned rows > To: "PostgreSQL General" <pgsql-general@postgresql.org> > Received: Wednesday, March 2, 2011, 3:35 PM > Hi Everyone, > > I would like to ask for your help finding a temporary > solution for my problem. > I upgraded postgres from 8.3 to 9.0.3 and I have an issue > with the order of the returned rows. > > The following script is a simplification of my real case: > > create table tmp_1 (field1 integer, field2 integer); > insert into tmp_1 values (1, 3); > insert into tmp_1 values (1, 3); > insert into tmp_1 values (1, 3); > insert into tmp_1 values (1, 3); > insert into tmp_1 values (1, 3); > insert into tmp_1 values (1, 4); > insert into tmp_1 values (1, 4); > insert into tmp_1 values (1, 4); > insert into tmp_1 values (1, 4); > insert into tmp_1 values (1, 4); > insert into tmp_1 values (1, 4); > insert into tmp_1 values (1, 1029); > insert into tmp_1 values (1, 1101); > insert into tmp_1 values (13, 3); > insert into tmp_1 values (13, 3); > insert into tmp_1 values (13, 3); > insert into tmp_1 values (13, 3); > insert into tmp_1 values (13, 3); > insert into tmp_1 values (13, 3); > insert into tmp_1 values (13, 4); > insert into tmp_1 values (13, 4); > insert into tmp_1 values (13, 4); > insert into tmp_1 values (13, 4); > insert into tmp_1 values (13, 4); > insert into tmp_1 values (13, 4); > insert into tmp_1 values (13, 4); > insert into tmp_1 values (13, 1029); > insert into tmp_1 values (13, 1101); > analyze tmp_1; > SELECT distinct field2 FROM tmp_1 WHERE field1 = 13; > > The result in postgres 8.3 is as follows: > 3 > 4 > 1029 > 1101 > And it stays the same no matter what the physical order of > the records is in the table. I can do random update and I > get the same results. It looks like the result is ordered by > the distinct fields... > > The result in postgres 9.0 is as follows: > 3 > 4 > 1101 > 1029 > not ordered by the distinct fields nor physical order... > > I am wondering if there is a temporary solution (updates, > indexes, ...) to order the result by field1 without changing > the statement... > > Thank you in advance, > Ioana Danes > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Ioana Danes <ioanasoftware@yahoo.ca> writes:
> I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of the returned rows.
Your application is simply broken if it assumes that DISTINCT results in
ordering the rows. The only thing that guarantees that is an ORDER BY.
You could probably work around it for the short term by unsetting
enable_hashagg, but you really ought to fix the query instead.
regards, tom lane
Ioana Danes, 02.03.2011 21:35: > Hi Everyone, > > I would like to ask for your help finding a temporary solution for my problem. > I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of the returned rows. > The database is free to return rows in any order it thinks is most efficient and you may never rely on any implicit ordering. If you need your rows sorted in a specific way, you have to use an ORDER BY clause. Everything else is doomed to fail someday. Regards Thomas
I totally agree with you and the problem is gonna be fixed. I just needed a temporary solution until the patch goes out. Thank you, Ioana --- On Wed, 3/2/11, Tom Lane <tgl@sss.pgh.pa.us> wrote: > From: Tom Lane <tgl@sss.pgh.pa.us> > Subject: Re: [GENERAL] select DISTINCT not ordering the returned rows > To: "Ioana Danes" <ioanasoftware@yahoo.ca> > Cc: "PostgreSQL General" <pgsql-general@postgresql.org> > Received: Wednesday, March 2, 2011, 3:44 PM > Ioana Danes <ioanasoftware@yahoo.ca> > writes: > > I upgraded postgres from 8.3 to 9.0.3 and I have an > issue with the order of the returned rows. > > Your application is simply broken if it assumes that > DISTINCT results in > ordering the rows. The only thing that guarantees > that is an ORDER BY. > > You could probably work around it for the short term by > unsetting > enable_hashagg, but you really ought to fix the query > instead. > > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >