Thread

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

   Darren King <darrenk@insightdist.com> — 1998-01-28T14:02:34Z

   > 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
   
   

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

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-01-28T16:22:28Z

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