Thread: group by not returning sorted rows

I have a query:



select cities.name as city, buildings.name as building, pagename, log_date , sum(exhibition_count) as tot 
from logrecords join cities on (logrecords.city=cities.num) join buildings on (logrecords.building=buildings.num) 
where     advertiser = 'Nielsens' and log_date >= '01/01/2004' and log_date <= '01/31/2004' 
group by cities.name, buildings.name,pagename,log_date ;

I have migrated a database from a redhat 7.3 box running 
[bhughes@compaq3 reports]$ rpm -q postgresql
postgresql-7.2.3-5.73


To a redhat 9 box running :
[bhughes@compaq2 reports]$ rpm -q postgresql
postgresql-7.4.2-1PGDG

and the rows resulting from the query are no longer sorted by log date. 
Is this a change since 7.2x?

I can achieve the results I need by adding an order by clause identical
to the group by but this seems counter intuitive since the rows have to
be ordered anyway.

Any tips appreciated.

Bret

On Mon, 5 Apr 2004, Bret Hughes wrote:

> select cities.name as city, buildings.name as building,
>     pagename,
>     log_date ,
>     sum(exhibition_count) as tot
> from logrecords
>     join cities on (logrecords.city=cities.num)
>     join buildings on (logrecords.building=buildings.num)
> where     advertiser = 'Nielsens' and
>     log_date >= '01/01/2004' and
>     log_date <= '01/31/2004'
> group by cities.name, buildings.name,pagename,log_date ;
>
> I have migrated a database from a redhat 7.3 box running
> [bhughes@compaq3 reports]$ rpm -q postgresql
> postgresql-7.2.3-5.73
>
> To a redhat 9 box running :
> [bhughes@compaq2 reports]$ rpm -q postgresql
> postgresql-7.4.2-1PGDG
>
> and the rows resulting from the query are no longer sorted by log date.
> Is this a change since 7.2x?

Yes.

> I can achieve the results I need by adding an order by clause identical
> to the group by but this seems counter intuitive since the rows have to
> be ordered anyway.

They no longer need to always be pre-ordered in order to do the group by
(this depends on plan).

Bret Hughes <bhughes@elevating.com> writes:
> and the rows resulting from the query are no longer sorted by log date. 
> Is this a change since 7.2x?

Yes.  7.4 can use hashing instead of sorting to bring grouped rows
together.

> I can achieve the results I need by adding an order by clause identical
> to the group by but this seems counter intuitive since the rows have to
> be ordered anyway.

No they don't; you're making an assumption about the implementation that
is no longer warranted.  The SQL spec doesn't require it either ...
output ordering is only guaranteed if you specify ORDER BY, per spec.
        regards, tom lane

On Mon, 2004-04-05 at 17:34, Tom Lane wrote:
> Bret Hughes <bhughes@elevating.com> writes:
> > and the rows resulting from the query are no longer sorted by log date. 
> > Is this a change since 7.2x?
> 
> Yes.  7.4 can use hashing instead of sorting to bring grouped rows
> together.
> 
> > I can achieve the results I need by adding an order by clause identical
> > to the group by but this seems counter intuitive since the rows have to
> > be ordered anyway.
> 
> No they don't; you're making an assumption about the implementation that
> is no longer warranted.  The SQL spec doesn't require it either ...
> output ordering is only guaranteed if you specify ORDER BY, per spec.
> 
>             regards, tom lane

Thanks, 

I replied to Tom directly by accident and did not want the list to think
I was ungrateful for the quick reply :)

Bret