[DERBY-6025] Wrong results with IN lists and indexes in territory based collation

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.5.1.1, 10.5.2.0, 10.5.3.0, 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.8.3.3, 10.9.2.2, 10.10.2.0, 10.11.1.1
• Component/s: SQL
• Labels: derby_triage10_11
• Urgency: Urgent
• Issue & fix info: High Value Fix, Repro attached
• Bug behavior facts: Wrong query result
• Assignee: Mamta A. Satoor
• Reporter: Knut Anders Hatlen
• Created: 21/Dec/12 11:00
• Updated: 11/Jun/14 08:04
• Resolved: 11/Jun/14 08:04

Description

The sequence below shows that a query returns 1 row when there is no index on the table, and it returns 0 rows when an index is created. It should return 1 row regardless of the index's presence.

ij version 10.9
ij> connect 'jdbc:derby:memory:colldb;create=true;territory=no;collation=TERRITORY_BASED';
ij> create table t(x varchar(40));
0 rows inserted/updated/deleted
ij> insert into t values 'Stranda Idrottslag', 'Aalesunds Fotballklubb';
2 rows inserted/updated/deleted
ij> select * from t where x in ('Aalesunds Fotballklubb', cast('xyz' as char(3)));
X
----------------------------------------
Aalesunds Fotballklubb

1 row selected
ij> create index i on t;
0 rows inserted/updated/deleted
ij> select * from t where x in ('Aalesunds Fotballklubb', cast('xyz' as char(3)));
X
----------------------------------------

0 rows selected