Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] No: implied sort with group by

From: "Thomas G(dot) Lockhart" <lockhart(at)alumni(dot)caltech(dot)edu>
To: Darren King <darrenk(at)insightdist(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] No: implied sort with group by
Date: 1998-01-28 16:22:28
Message-ID: 34CF5B44.2519DCDD@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
> > Not necessarily true; as I said, I get the same result as above (with the 980112
> > source tree; have things changed since??). Perhaps you are running into the sorting
> > problem which seemed to be present on larger tables only?
> >
> > postgres=> select b,c,sum(a) from t1 group by b,c;
> > b|c|sum
> > -+-+---
> >  |x|  5
> >  |z|  3
> > (2 rows)
> >
> > postgres=> select * from t1;
> > a|b|c
> > -+-+-
> > 1| |x
> > 2| |x
> > 2| |x
> > 3| |z
> > (4 rows)
>
> Hmmm...I have a snapshot from about ten days ago

> Is the order from the second query the order that the rows were inserted?
>
> Do you get the same results if you insert the (3,null,'z') second or third so
> the rows are stored out of order?  I was getting my bad results with this same
> data, only four rows.

OUCH! You are right, there is a problem with this simple test case:

postgres=> select b,c,sum(a) from t1 group by b,c;
b|c|sum
-+-+---
 |x|  5
 |z|  3
 |x|  0
(3 rows)

postgres=> select * from t1;
a|b|c
-+-+-
1| |x
2| |x
2| |x
3| |z
0| |x
(5 rows)

I just inserted a single out-of-order row at the end of the table which, since the
integer value is zero, should have not affected the result. Sorry I didn't understand
the nature of the test case.

                                                    - Tom


In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 1998-01-28 16:34:23
Subject: Re: [HACKERS] functions with same name, different args
Previous:From: Darren KingDate: 1998-01-28 14:02:34
Subject: Re: [HACKERS] No: implied sort with group by

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group