[
https://issues.apache.org/jira/browse/TRAFODION-2791?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16260609#comment-16260609
]
ASF GitHub Bot commented on TRAFODION-2791:
-------------------------------------------
GitHub user duoluodexiaokeke opened a pull request:
https://github.com/apache/incubator-trafodion/pull/1307
[TRAFODION-2791] 'Not casespecific' column comparison returns wrong
'Not casespecific' column comparison returns wrongresult, now like and
t.c1=t.c2 not support
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/duoluodexiaokeke/incubator-trafodion
TRAFODION-2791
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/1307.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1307
----
commit aef11afd66979eb047ada233a756ce71cae17f13
Author: liul <lei....@esgyn.cn>
Date: 2017-11-21T11:19:12Z
[TRAFODION-2791] 'Not casespecific' column comparison returns wrong
results. now like and t.c1=t.c2 not support
----
> 'Not casespecific' column comparison returns wrong results
> ----------------------------------------------------------
>
> Key: TRAFODION-2791
> URL: https://issues.apache.org/jira/browse/TRAFODION-2791
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.3-incubating
> Environment: All
> Reporter: David Wayne Birdsall
>
> The current support of 'not casespecific' columns seems to be a bit
> erroneous. As shown below, c1 is a 'not casespecific' char column with a
> value of a upper case 'A', and c2 a 'not casespecific' char column with a
> value of a lower case 'a'.
> The following predicates seem to work:
> t.c1='a' (not casespecific)
> t.c1<='a' (not casespecific)
> t.c1 in ('a' (not casespecific))
> But the following predicates don't seem to work. They don't find the match
> while they should have in a case insensitive comparison.
> t.c1<>'a' (not casespecific)
> t.c1>='a' (not casespecific)
> t.c1 between 'a' (not casespecific) and 'b' (not casespecific)
> t.c1 like '%a%' (not casespecific)
> t.c1=t.c2
> For example:
> >>drop table if exists t;
> --- SQL operation complete.
> >>
> >>create table t (c1 char not casespecific, c2 char not casespecific);
> --- SQL operation complete.
> >>insert into t values ('A', 'a');
> --- 1 row(s) inserted.
> >>select * from t;
> C1 C2
> -- --
> A a
> --- 1 row(s) selected.
> >>select * from t where t.c1='a' (not casespecific);
> C1 C2
> -- --
> a a
> --- 1 row(s) selected.
> >>select * from t where t.c1<='a' (not casespecific);
> C1 C2
> -- --
> A a
> --- 1 row(s) selected.
> >>select * from t where t.c1<>'a' (not casespecific);
> C1 C2
> -- --
> A a
> --- 1 row(s) selected.
> >>select * from t where t.c1>='a' (not casespecific);
> --- 0 row(s) selected.
> >>select * from t where t.c1 between 'a' (not casespecific) and 'b' (not
> >>casespecific);
> --- 0 row(s) selected.
> >>select * from t where t.c1 in ('a' (not casespecific));
> C1 C2
> -- --
> a a
> --- 1 row(s) selected.
> >>select * from t where t.c1 like '%a%' (not casespecific);
> --- 0 row(s) selected.
> >>select * from t where t.c1=t.c2;
> --- 0 row(s) selected.
> >>
> >>drop table t;
> --- SQL operation complete.
> To reproduce, use the following script:
> drop table if exists t;
> create table t (c1 char not casespecific, c2 char not casespecific);
> insert into t values ('A', 'a');
> select * from t;
> select * from t where t.c1='a' (not casespecific);
> select * from t where t.c1<='a' (not casespecific);
> select * from t where t.c1<>'a' (not casespecific);
> select * from t where t.c1>='a' (not casespecific);
> select * from t where t.c1 between 'a' (not casespecific) and 'b' (not
> casespecific);
> select * from t where t.c1 in ('a' (not casespecific));
> select * from t where t.c1 like '%a%' (not casespecific);
> select * from t where t.c1=t.c2;
> drop table t;
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
