[JBoss JIRA] (TEIID-5406) Allow usage of long datatype as the increment parameter in TimestampAdd function
3:12 a.m.
[
https://issues.jboss.org/browse/TEIID-5406?page=com.atlassian.jira.plugin...
]
dalex dalex updated TEIID-5406:
-------------------------------
Description:
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}
was:
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 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}
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: dalex dalex
Assignee: Steven Hawkins
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.5.0#75005)
2365
days inactive
2365
days old
0 comments
1 participants
participants (1)
-
dalex dalex (JIRA)