[ https://issues.apache.org/jira/browse/DERBY-6227?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14170372#comment-14170372 ]
ASF subversion and git services commented on DERBY-6227: -------------------------------------------------------- Commit 1631604 from [~mamtas] in branch 'code/branches/10.10' [ https://svn.apache.org/r1631604 ] DERBY-6227(Distinct aggregates don't work well with territory-based collation) Backporting to 10.10 Had to hand tweek the changes in GroupedAggregateResultSet.java but there weren't too many changes. derbyall and junit suite ran fine. > Distinct aggregates don't work well with territory-based collation > ------------------------------------------------------------------ > > Key: DERBY-6227 > URL: https://issues.apache.org/jira/browse/DERBY-6227 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2, 10.8.2.2, > 10.8.3.0, 10.9.1.0, 10.10.1.1 > Reporter: Knut Anders Hatlen > Assignee: Mamta A. Satoor > Labels: derby_triage10_11 > Fix For: 10.11.1.1 > > Attachments: d6227-1a.diff > > > When working on DERBY-5840, I noticed that GroupedAggregateResultSet would > do duplicate elimination by comparing the java.lang.String representation of > the values. With territory-based collation, it is possible that two values > that have different java.lang.String representation should be considered > duplicates, and this logic will produce incorrect results. > Example: > ij version 10.10 > ij> connect > 'jdbc:derby:memory:db;territory=en_US;collation=TERRITORY_BASED:PRIMARY;create=true'; > ij> create table t(i int, s varchar(10)); > 0 rows inserted/updated/deleted > ij> insert into t values (1, 'a'), (1, 'a'), (2, 'b'), (2, 'B'), (3, 'a'), > (3, 'A'), (3, 'b'), (3, 'B'), (3, 'c'); > 9 rows inserted/updated/deleted > ij> select distinct s from t; > S > ---------- > b > a > c > 3 rows selected > ij> select i, count(distinct s) from t group by i; > I |2 > ----------------------- > 1 |1 > 2 |2 > 3 |5 > 3 rows selected > I would have expected the last query to return > (1, 1) > (2, 1) > (3, 3) -- This message was sent by Atlassian JIRA (v6.3.4#6332)