[
https://issues.apache.org/jira/browse/TRAFODION-3088?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16494408#comment-16494408
]
ASF GitHub Bot commented on TRAFODION-3088:
-------------------------------------------
Github user zellerh commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1584#discussion_r191595555
--- Diff: core/sql/optimizer/ValueDesc.cpp ---
@@ -6382,7 +6382,32 @@ void ValueIdList::convertToTextKey(const
ValueIdList& keyList, NAString& result)
{
short vLen = val.length();
- if ((constType->getTypeQualifier() == NA_NUMERIC_TYPE) &&
+ if (constType->getTypeQualifier() == NA_INTERVAL_TYPE)
+ {
+ // In some code paths, the text may have "INTERVAL 'xxx'
<qualifier>"
+ // junk around it so we have to strip that off. (Example: An
equality
+ // predicate when query caching has been turned off via
+ // CQD QUERY_CACHE '0'. Another example happens with BETWEEN,
whether
+ // or not query caching is turned off. See JIRA TRAFODION-3088
for
+ // that example.)
+ Lng32 start = val.index("'");
+ Lng32 minus = val.index("-");
+ if (start > 0)
+ {
+ Lng32 end = val.index("'", start+1);
+ if (end > 0)
+ {
+ val = val(start+1, (end-start-1));
+ if (minus > 0)
--- End diff --
Should this also check for minus < start? Could we encounter year-month
intervals with a '-' inside the literal, such as interval '1-1' year to month?
> 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)
