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

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6025?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13910678#comment-13910678 ] 

Mamta A. Satoor commented on DERBY-6025:
----------------------------------------

I have debugged the code generation a little bit and it appears that we are generating collation specific data types for in list elements when working with territory based database as expected. So not yet sure why the row is not getting selected when there is an index defined on the table. I will continue to debug this more.

> Wrong results with IN lists and indexes in territory based collation
> --------------------------------------------------------------------
>
>                 Key: DERBY-6025
>                 URL: https://issues.apache.org/jira/browse/DERBY-6025
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 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
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>              Labels: derby_triage10_11
>
> 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(x);
> 0 rows inserted/updated/deleted
> ij> select * from t where x in ('Aalesunds Fotballklubb', cast('xyz' as char(3)));
> X                                       
> ----------------------------------------
> 0 rows selected



--
This message was sent by Atlassian JIRA
(v6.1.5#6160)