Re: BUG #16031: Group by returns duplicate groups

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

 David> Looking possibly like indexing is part of the issue at the
 David> moment.

Your original EXPLAIN didn't show any index scans being used...?

I can think of a possible explanation if there's some other value in the
big table which, due to some collation bug, is not consistently being
compared as < 'DK' or > 'DK'.

Unfortunately, we have two code paths for comparison, and one of them
can't easily be exercised directly from SQL, since it is only used for
sorts (and therefore also index builds).

Can you try these queries:

set enable_indexscan=off;
set enable_bitmapscan=off;

select count(*) c0,
       count(*) filter (where bttextcmp(name,'DK') < 0) c1_lt,
       count(*) filter (where bttextcmp(name,'DK') = 0) c1_eq,
       count(*) filter (where bttextcmp(name,'DK') > 0) c1_gt,
       count(*) filter (where bttextcmp('DK',name) > 0) c2_lt,
       count(*) filter (where bttextcmp('DK',name) = 0) c2_eq,
       count(*) filter (where bttextcmp('DK',name) < 0) c2_gt
  from big_table;

with sd as (select name, row_number() over (order by name) rnum
              from big_table)
select name from sd
 where rnum >= (select min(rnum) from sd where name='DK')
   and rnum <= (select max(rnum) from sd where name='DK')
   and name <> 'DK';

-- 
Andrew (irc:RhodiumToad)