Re: BUG #16031: Group by returns duplicate groups

>>>>> "David" == David Raymond <David.Raymond@tomtom.com> writes:

 David>      c0      |   c1_lt    | c1_eq |   c1_gt    |   c2_lt    | c2_eq |   c2_gt
 David> -------------+------------+-------+------------+------------+-------+------------
 David>  108,565,086 | 27,900,023 |    25 | 80,665,038 | 27,900,023 |    25 | 80,665,038
 David> (1 row)

Well those counts look consistent to me.

But this is all kinds of messed up:

 David> testing=> with sd as (select name, row_number() over (order by name) rnum
 David> testing(>               from big_table)
 David> testing-> select name from sd
 David> testing-> where rnum >= (select min(rnum) from sd where name='DK')
 David> testing-> and rnum <= (select max(rnum) from sd where name='DK')
 David> testing-> and name <> 'DK';
 David>                name
 David> -----------------------------------
 David>  Dk'bus Marine
 David>  Dk's Auto's
 David>  Dk's Bar & Grill
 David>  Dk's Barbers & Stylist
 David>  Dk's Beach Boutique
 David>  Dk's Cabinets & Countertops
 David>  Dk's Cleaning Service
 David>  Dk's Clothing
 David>  Dk's Communications
 David>  Dk's Dancewear & Fitnesswear
 David>  Dk's Dancewear Boutique
 David>  Dk's Discount Dance & Fitnesswear
 David>  DK's Drywall Service
 David>  DK'S DUSTBUSTERS
 David>  Dk's Family Five Star Trophies
 David>  DK's Family Five Star Trophies
 David>  Dk's Food Mart
 David>  Dk'S Group Pte. Ltd.
 David>  Dk's Hair Designs
 David>  Dk's Hair Happenings
 David>  Dk's Hair Supply
 David>  Dk's Home Decor
 David>  DK's Informática
 David>  Dk's Janitorial
 David>  DK's Liquors
 David>  Dk's Market
 David>  Dk's Moda Masculina
 David>  Dk's Nails And Spa
 David>  DK's Pawn Shop
 David>  Dk's Pet Grooming
 David>  DK's Quality Service
 David>  DK's Restoration
 David>  Dk's Sports Center
 David>  Dk's Statuary
 David>  Dk's Style Hut
 David>  Dk's Temiskaming Shore Taxi
 David>  Dk's Towing
 David>  DK's Travel
 David>  Dk'Style
 David>  DK'Z Car Wash
 David>  Dk-
 David> (41 rows)

Let's see some more data from that. Do this query:

with sd as (select name, row_number() over (order by name) rnum
              from big_table)
select rnum, name, encode(convert_to(name, 'SQL_ASCII'),'escape'),
       name < 'DK' as lt, name = 'DK' as eq, name > 'DK' as gt
  from sd
 where rnum >= (select min(rnum) from sd where name='DK')
   and rnum <= (select max(rnum) from sd where name='DK');

--
Andrew (irc:RhodiumToad)