[
https://issues.apache.org/jira/browse/TRAFODION-3071?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16484296#comment-16484296
]
ASF GitHub Bot commented on TRAFODION-3071:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/trafodion/pull/1572
[TRAFODION-3071] Add missing binder checks for DATEDIFF etc. + other fixes
This pull request fixes several problems. Items 2 through 4 were noticed
while researching a fix for item 1; I fixed them since I was in the
neighborhood.
1. Some of the datetime-related functions (DATEDIFF, TIMESTAMPDIFF,
DATE_TRUNC, MONTHS_BETWEEN) lacked binder checks to enforce DATE or TIMESTAMP
data type on certain operands. This has been fixed. This is the problem
reported in the JIRA.
2. Awhile back functions CRC32, MD5, SHA, SHA1 and SHA2 were added, but
they were not added to the suitable non-reserved word production in the parser.
So, in effect these identifiers became reserved words. This has been fixed.
3. The ROUND function did not accept a dynamic parameter as an argument for
its first operand. This has been fixed.
4. Test compGeneral/TEST006 had an out-of-date DIFF006 file. This file
looks like it dates back to predecessor product days. I have deleted it.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/trafodion Trafodion3071
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1572.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1572
----
commit 9c5080c81b28f336e08dd685bd73607847251234
Author: Dave Birdsall <dbirdsall@...>
Date: 2018-05-22T16:56:18Z
[TRAFODION-3071] Add missing binder checks for DATEDIFF etc. + other fixes
----
> 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)
