[
https://issues.apache.org/jira/browse/TRAFODION-3088?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16494218#comment-16494218
]
David Wayne Birdsall commented on TRAFODION-3088:
-------------------------------------------------
Another example where this particular data conversion error arises is the
following:
CQD QUERY_CACHE '0';
prepare s1 from select * from t1 where a = interval '41' second;
When query caching is in effect (which is the default behavior), the interval
constant in this example is parameterized, and that code path handles the
literal correctly. When we turn off query caching (which is what CQD
QUERY_CACHE '0' does), the constant is not parameterized and we do not handle
it correctly. We get the same data conversion error as reported above.
The fix given in the pull request fixes this case also.
> 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
> Priority: Major
>
> 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)
