[teiid-issues] [JBoss JIRA] (TEIID-5406) Allow usage of long datatype as the increment parameter in TimestampAdd function

[Dmitrii Pogorelov (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5406?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13714243#comment-13714243 ] 

Dmitrii Pogorelov commented on TEIID-5406:
------------------------------------------

Hi [~shawkins], the issue was resolved previous year but just a question: do I understand correctly that some of DB still can treat the fractional second parameter as Integer type? That's why you added the check on Integer range? 

> Allow usage of long datatype as the increment parameter in TimestampAdd function
> --------------------------------------------------------------------------------
>
>                 Key: TEIID-5406
>                 URL: https://issues.jboss.org/browse/TEIID-5406
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: Query Engine
>    Affects Versions: 10.2
>         Environment: teiid-10.2.0 on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
>            Reporter: Dmitrii Pogorelov
>            Assignee: Steven Hawkins
>            Priority: Major
>             Fix For: 11.1
>
>
> Currently the TIMESTAMPADD(interval, count, timestamp) expects the count parameter to be integer. This is inconsistent with the return value of the TIMSTAMPDIFF function that is long. Thus we cannot take the fraction of the count returned by TIMSTAMPDIFF and submit it to TIMESTAMPADD. Since the SQL_TSI_FRAC_SECOND difference is too large for most human values (and it's the only way to retrieve millis, even though millions of millis), we need a way to submit this data to TIMSTAMPADD.
> 1. There is an inconsistency when adding 1 billion fractions of a second. Please execute the script below, one adds one billion minus one, the next one exactly 1bln fractions which results in two values, one of which is 1 fraction larger than expected:
> {code:sql}
> Select TimestampAdd(SQL_TSI_FRAC_SECOND, 999999999, Cast(CurDate() as timestamp))
> Union All
> Select TimestampAdd(SQL_TSI_FRAC_SECOND, 999999999 + 1, Cast(CurDate() as timestamp));;
> {code}
> 2. Another example:
> {code:sql}
> Begin
>     Declare timestamp initialDate = CurDate();
>     Declare long diff = TimestampDiff(SQL_TSI_FRAC_SECOND, initialDate, TimestampAdd(SQL_TSI_DAY, 1, initialDate));
>     Declare timestamp back = TimestampAdd(SQL_TSI_FRAC_SECOND, diff, initialDate);
>     select initialDate, back;
> End ;;
> {code}
> which leads to the following error message:
> {code:noformat}
> 2018-07-03 10:10:33,942 WARN  [org.teiid.PROCESSOR] (Worker7_QueryProcessorQueue47) 1RBg/ooz5vx7 TEIID30020 Processing exception for request 1RBg/ooz5vx7.27 'TEIID30070 The function
> 'TimestampAdd(SQL_TSI_FRAC_SECOND, VARIABLES.diff, VARIABLES.initialDate)' is a valid function form, but the arguments do not match a known type signature and cannot be converted usi
> ng implicit type conversions.'. Originally QueryResolverException ResolverVisitor.java:757.: org.teiid.api.exception.query.QueryResolverException: TEIID30070 The function 'TimestampA
> dd(SQL_TSI_FRAC_SECOND, VARIABLES.diff, VARIABLES.initialDate)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit
>  type conversions.
>         at org.teiid.query.resolver.util.ResolverVisitor.resolveFunction(ResolverVisitor.java:757)
>         at org.teiid.query.resolver.util.ResolverVisitor.visit(ResolverVisitor.java:392)
>         at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:169)
>         at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:50)
>         at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.postVisitVisitor(PreOrPostOrderNavigator.java:57)
>         at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:194)
>         at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:169)
>         at org.teiid.query.resolver.util.ResolverVisitor.resolveLanguageObject(ResolverVisitor.java:1456)
>         at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveStatement(UpdateProcedureResolver.java:234)
>         at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveBlock(UpdateProcedureResolver.java:127)
>         at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveCommand(UpdateProcedureResolver.java:110)
>         at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:282)
>         at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:128)
>         at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:282)
>         at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:418)
>         at org.teiid.dqp.internal.process.Request.processRequest(Request.java:486)
>         at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:660)
>         at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339)
>         at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
>         at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
>         at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
>         at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
>         at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
>         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
>         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
>         at java.lang.Thread.run(Thread.java:745)
> {code}



--
This message was sent by Atlassian Jira
(v7.12.1#712002)