[
https://issues.apache.org/jira/browse/TRAFODION-3071?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16477749#comment-16477749
]
David Wayne Birdsall commented on TRAFODION-3071:
-------------------------------------------------
The issue looks like it is in ZZZBinderFunction::bindNode
(optimizer/BindItemExpr.cpp). There, the DATEDIFF function (and several other
functions) are rewritten in terms of simpler functions before type synthesis is
done. This seems to be done in order to avoid premature type checking errors on
dynamic parameters. The problem is the type checking rules for the simpler
functions are not the same as the originals.
> 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
> Priority: Major
>
> 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)
