[
https://issues.apache.org/jira/browse/TRAFODION-2582?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15958199#comment-15958199
]
Hans Zeller commented on TRAFODION-2582:
----------------------------------------
The result we would have expected would show the difference between the
timestamps, which is 300 seconds or 5 minutes for the first two queries.
The problem (if it is indeed a bug, as I believe) is in BiArith::bindNode. When
we see this operation of a difference between two timestamps, we get to the
following code:
{noformat}
else if ((naType0->getTypeQualifier() == NA_DATETIME_TYPE) &&
(naType1->getTypeQualifier() == NA_DATETIME_TYPE) &&
(getOperatorType() == ITM_MINUS))
{
// Column of DATE datatype is internally created as TIMESTAMP(0).
// timestamp(0) - date = diff in days
// timestamp(0) - timestamp(0) = diff in days
// date - date = diff in days
{noformat}
After some more checks, we cast both timestamp(0) values to a date. Therefore,
the result will be the difference in days.
That looks wrong to me. I probably need to consult with some other folks before
making this change, but it does not look right to me.
> Two timestamp minus and cannot cast to interval value
> -----------------------------------------------------
>
> Key: TRAFODION-2582
> URL: https://issues.apache.org/jira/browse/TRAFODION-2582
> Project: Apache Trafodion
> Issue Type: Bug
> Components: Build Infrastructure
> Affects Versions: 2.1-incubating
> Reporter: Yuan Liu
> Fix For: any
>
>
> SQL>select cast((timestamp'2017-04-06 11:50:00' - timestamp'2017-04-06
> 11:45:00') as interval second) from (values(1));
> (EXPR)
> ----------
> 0.000000
> --- 1 row(s) selected.
> SQL>select cast((timestamp'2017-04-06 11:50:00' - timestamp'2017-04-06
> 11:45:00') as interval minute) from (values(1));
> (EXPR)
> ------
> 0
> >>select timestamp'2017-04-06 11:50:00' - timestamp'2017-04-03 11:45:00' from
> >>dual;
> (EXPR)
> -------------
> 3
> --- 1 row(s) selected.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
