David Wayne Birdsall created TRAFODION-3088:
-----------------------------------------------
Summary: Multi-value BETWEEN on interval key column gives wrong
result
Key: TRAFODION-3088
URL: https://issues.apache.org/jira/browse/TRAFODION-3088
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Affects Versions: 2.3
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall
The sqlci session below demonstrates the problem. When we do a multi-column
BETWEEN where one of the columns is an INTERVAL key column and the values in
the BETWEEN predicate for that column are equal, we get a data conversion error.
{quote}>>obey jira.sql;
>>?section setup
>>
>>drop table if exists t1;
--- SQL operation complete.
>>
>>create table t1 ( a interval second not null,
+> b int,
+> primary key (a) );
--- SQL operation complete.
>>
>>insert into table t1 values ( interval '41' second, 41 );
--- 1 row(s) inserted.
>>
>>?section testit
>>
>>select * from t1;
A B
---------- -----------
41.000000 41
--- 1 row(s) selected.
>>
>>select * from t1 where a = interval '41' second;
A B
---------- -----------
41.000000 41
--- 1 row(s) selected.
>>
>>-- fails with a data conversion error
>>select * from t1 where (a,b) between
+> (interval '41' second, 40) and (interval '41' second, 42);
*** ERROR[8413] The string argument contains characters that cannot be
converted. Source data(in hex): 494e
--- 0 row(s) selected.
>>
>>select * from t1 where a = interval '41' second
+> and b between 40 and 42;
A B
---------- -----------
41.000000 41
--- 1 row(s) selected.
>>
>>log;
{quote}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
