pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgroup by not returning sorted rows
4+ messages / 3 participants
[nested] [flat]
* group by not returning sorted rows
@ 2004-04-05 21:55 Bret Hughes <bhughes@elevating.com>
0 siblings, 2 replies; 4+ messages in thread
From: Bret Hughes @ 2004-04-05 21:55 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: group by not returning sorted rows
@ 2004-04-05 22:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bret Hughes <bhughes@elevating.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2004-04-05 22:34 UTC (permalink / raw)
To: Bret Hughes <bhughes@elevating.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: group by not returning sorted rows
@ 2004-04-05 22:54 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Bret Hughes <bhughes@elevating.com>
1 sibling, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2004-04-05 22:54 UTC (permalink / raw)
To: Bret Hughes <bhughes@elevating.com>; +Cc: pgsql-sql
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).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: group by not returning sorted rows
@ 2004-04-06 16:51 Bret Hughes <bhughes@elevating.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Bret Hughes @ 2004-04-06 16:51 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-04-06 16:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-05 21:55 group by not returning sorted rows Bret Hughes <bhughes@elevating.com>
2004-04-05 22:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-06 16:51 ` Bret Hughes <bhughes@elevating.com>
2004-04-05 22:54 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox