[
https://issues.apache.org/jira/browse/DERBY-6025?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13911362#comment-13911362
]
Mamta A. Satoor commented on DERBY-6025:
----------------------------------------
I am continuing to debug the difference between
select * from t where x in ('Aalesunds Fotballklubb', cast('xyz' as char(3)));
and
select * from t where x in ('Aalesunds Fotballklubb', 'xyz');
The second query only has constant nodes in the in list. But the first query
has a constant node and a cast node. In list is able to sort the members in the
in list at compile time when it is dealing with just constants. But in case of
the cast node, the sorting can not be done at compile time and hence it is done
at execution time. I think, we are running into a bug when the sorting of the
in list elements happen at run time. I will update the jira as and when I find
more about what exactly might be going wrong in case of cast node in the in
list parameters.
> 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)
