[TRAFODION-3088] Multi-value BETWEEN on interval key column gives wrong result

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.3
• Fix Version/s: 2.3
• Component/s: sql-cmp
• Assignee: Dave Birdsall
• Reporter: Dave Birdsall
• Created: 29/May/18 16:40
• Updated: 13/Aug/18 22:36
• Resolved: 30/May/18 22:46

Description

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.

>>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;

Issue Links

• links to: GitHub Pull Request #1584