Re: BUG #16031: Group by returns duplicate groups

>>>>> "David" == David Raymond <David(dot)Raymond(at)tomtom(dot)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)