David Wayne Birdsall created TRAFODION-3071:
-----------------------------------------------
Summary: DATEDIFF function gives strange results when executed on
interval data types
Key: TRAFODION-3071
URL: https://issues.apache.org/jira/browse/TRAFODION-3071
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Affects Versions: 2.3
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall
According to the SQL reference manual,
[http://trafodion.apache.org/docs/sql_reference/index.html#datediff_function,]
the DATEDIFF function is limited to DATE and TIMESTAMP operands.
If one attempts DATEDIFF on INTERVAL data types, the errors are strange and
non-intuitive. In some cases, DATEDIFF even succeeds on an INTERVAL data type.
The following session output illustrates:
{quote}>>drop table if exists t;
--- SQL operation complete.
>>create table t (c1 interval year, c2 interval year to month, c3 interval
>>month);
--- SQL operation complete.
>>insert into t values (interval '11' year, interval '22-02' year to month,
>>interval '33' month);
--- 1 row(s) inserted.
>>
>>select DATEDIFF(MONTH, c1, c1) from t;
*** ERROR[4037] Field MONTH cannot be extracted from a source of type INTERVAL
YEAR(2).
*** ERROR[4062] The preceding error actually occurred in function DATEDIFF.
*** ERROR[8822] The statement was not prepared.
>>select DATEDIFF(MONTH, c2, c2) from t;
(EXPR)
-----------
0
--- 1 row(s) selected.
>>select DATEDIFF(MONTH, c3, c3) from t;
*** ERROR[4037] Field YEAR cannot be extracted from a source of type INTERVAL
MONTH(2).
*** ERROR[4062] The preceding error actually occurred in function DATEDIFF.
*** ERROR[8822] The statement was not prepared.
>>
{quote}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
