[jira] [Commented] (TRAFODION-2582) Two timestamp minus and cannot cast to interval value

["Hans Zeller (JIRA)" <ji...@apache.org>]

    [ 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)