[
https://issues.apache.org/jira/browse/TRAFODION-2791?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16267184#comment-16267184
]
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_r153280569
--- Diff: core/sql/optimizer/OptRange.cpp ---
@@ -503,7 +503,13 @@ ConstValue* OptRangeSpec::getConstOperand(ItemExpr*
predExpr, Lng32 constInx)
// currently support. Predicates involving types not yet supported will
be
// treated as residual predicates.
if
(QRDescGenerator::typeSupported(static_cast<ConstValue*>(right)->getType()))
+ {
--- End diff --
This adds a case-insensitive flag to the type in the RangeSpec, but I don't
think RangeSpecs are written to handle case-insensitive comparisons. Take a
look at the methods that deal with comparisons when building RangeSpecs, in
file Range.cpp. So, I think you would have to do one of two things: a) disable
the RangeSpec transformation for case-insensitive comparison operators (the
easy way) or change the RangeSpec methods to handle case-insensitive
comparisons.
> '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)
