[
https://issues.apache.org/jira/browse/DERBY-6025?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13911037#comment-13911037
]
Mamta A. Satoor commented on DERBY-6025:
----------------------------------------
It appears that issue may be with the CAST node as shown in the script behavior
below
ij> connect
'jdbc:derby:memory:colldb;create=true;territory=no;collation=TERRITORY_BASED';
ij> create table t(x varchar(40));
insert into t values 'Stranda Idrottslag', 'Aalesunds Fotballklubb';
0 rows inserted/updated/deleted
ij> 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> select * from t where x in ('Aalesunds Fotballklubb', 'xyz');
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
ij> select * from t where x in ('Aalesunds Fotballklubb', 'xyz');
X
----------------------------------------
Aalesunds Fotballklubb
1 row selected
As seen in the query above, the in list query returns incorrect row when index
is created AND one of the elements in the in list is using CAST. Same query
without the CAST and with index returns the correct row.
> 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)
