[jira] [Created] (TRAFODION-2791) 'Not casespecific' column comparison returns wrong results

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

David Wayne Birdsall created TRAFODION-2791:
-----------------------------------------------

             Summary: '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)