[
https://issues.apache.org/jira/browse/TRAFODION-2791?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16267183#comment-16267183
]
ASF GitHub Bot commented on TRAFODION-2791:
-------------------------------------------
Github user zellerh commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/1307#discussion_r153273845
--- Diff: core/sql/optimizer/ItemColRef.h ---
@@ -359,6 +367,16 @@ class ConstValue : public ItemExpr
enum CharInfo::CharSet
strLitPrefixCharSet=CharInfo::UnknownCharSet
);
+ ConstValue(const NAWString& strval,
--- End diff --
Same comment here.
> '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)
