[DERBY-6227] Distinct aggregates don't work well with territory-based collation

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 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
• Fix Version/s: 10.10.2.1, 10.11.1.1
• Component/s: SQL
• Labels: derby_triage10_11
• Urgency: Urgent
• Bug behavior facts: Wrong query result
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Created: 24/May/13 11:26
• Updated: 09/Sep/16 09:52
• Resolved: 14/Oct/14 02:07

Description

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)

Issue Links

• relates to: DERBY-6759 Derby 10.10 backport issue (fall 2014)