From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | David Raymond <David(dot)Raymond(at)tomtom(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-bugs\(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: BUG #16031: Group by returns duplicate groups |
Date: | 2019-10-01 15:21:20 |
Message-ID: | 87sgoclcux.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
>>>>> "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)
From | Date | Subject | |
---|---|---|---|
Next Message | David Raymond | 2019-10-01 15:22:26 | RE: BUG #16031: Group by returns duplicate groups |
Previous Message | David Raymond | 2019-10-01 14:55:37 | RE: BUG #16031: Group by returns duplicate groups |