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

> 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?
>
>                                                     - Tom
>
> 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, I'll get something newer and
try this again.  I've been putting off getting a new one until I get the block
size patch done.  Annoying to put the changes back into a new src copy (but not
as annoying as dealing with #(*&^! insurance companies claims departments).

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.  I do have a problem with large groupings on two or more
columns running out of memory, but not the problem that linux users are seeing.

darrenk

> > 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