[
https://issues.jboss.org/browse/TEIID-4684?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-4684:
---------------------------------------
The engine is simplifying the push down to a single function, rather than considering all
three. We haven't run across a case where it is necessary to use the specific
function rather than just the format/parsetimestamp.
Also the issue with the parse/format functions is that every database tends to have
it's own formatting syntax, which requires extra work to map to. In this case presto
supports the standard java formatting.
PrestoDB translator - format* functions - invalid conversion to
'timestamp with timezone'
-----------------------------------------------------------------------------------------
Key: TEIID-4684
URL:
https://issues.jboss.org/browse/TEIID-4684
Project: Teiid
Issue Type: Bug
Components: Misc. Connectors
Affects Versions: 8.12.8.6_3
Reporter: Juraj DurĂ¡ni
Assignee: Kylin Soong
Fix For: 9.2.1
When issuing query with formatdate or formattime, Teiid tries convert respective column
to 'timestamp with timezone'. This conversion causes exception in PrestoDB. The
proper conversion should be to 'timestamp'.
{code:sql|title=Query}
SELECT FORMATDATE(DateValue, 'MM-dd-yy') FROM BQT1.SmallA
{code}
{code:sql|title=Source command}
SELECT format_datetime(cast(g_0.datevalue AS timestamp with timezone),
'MM-dd-yy') FROM smalla AS g_0
{code}
{code:plain|title=Exception}
14:41:32,061 WARN [org.teiid.CONNECTOR] (Worker30_QueryProcessorQueue143) Connector
worker process failed for atomic-request=A7WqBzZhsyYM.50.0.34:
org.teiid.translator.jdbc.JDBCExecutionException: 1 TEIID11008:TEIID11004 Error executing
statement(s): [SQL: SELECT format_datetime(cast(g_0.datevalue AS timestamp with timezone),
'MM-dd-yy') FROM smalla AS g_0]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
[translator-jdbc-8.12.5.redhat-8.jar:8.12.5.redhat-8]
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
at sun.reflect.GeneratedMethodAccessor97.invoke(Unknown Source) [:1.8.0_92]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[rt.jar:1.8.0_92]
at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_92]
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy48.execute(Unknown Source)
at
org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at
org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at
org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_92]
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
[rt.jar:1.8.0_92]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
[rt.jar:1.8.0_92]
at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_92]
Caused by: java.sql.SQLException: Query failed (#20170106_134131_04118_k4ss5): line 1:56:
mismatched input 'with' expecting {')', 'ARRAY'}
at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1799)
at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:209)
at com.facebook.presto.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:69)
at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119)
[translator-jdbc-8.12.5.redhat-8.jar:8.12.5.redhat-8]
... 17 more
Caused by: com.facebook.presto.sql.parser.ParsingException: line 1:56: mismatched input
'with' expecting {')', 'ARRAY'}
at com.facebook.presto.sql.parser.SqlParser$1.syntaxError(SqlParser.java:45)
at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:65)
at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:566)
at
org.antlr.v4.runtime.DefaultErrorStrategy.reportInputMismatch(DefaultErrorStrategy.java:325)
at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:148)
at com.facebook.presto.sql.parser.SqlBaseParser.type(SqlBaseParser.java:8691)
at
com.facebook.presto.sql.parser.SqlBaseParser.primaryExpression(SqlBaseParser.java:7817)
at
com.facebook.presto.sql.parser.SqlBaseParser.valueExpression(SqlBaseParser.java:6677)
at com.facebook.presto.sql.parser.SqlBaseParser.predicated(SqlBaseParser.java:5955)
at
com.facebook.presto.sql.parser.SqlBaseParser.booleanExpression(SqlBaseParser.java:5858)
at com.facebook.presto.sql.parser.SqlBaseParser.expression(SqlBaseParser.java:5613)
at
com.facebook.presto.sql.parser.SqlBaseParser.primaryExpression(SqlBaseParser.java:7613)
at
com.facebook.presto.sql.parser.SqlBaseParser.valueExpression(SqlBaseParser.java:6677)
at com.facebook.presto.sql.parser.SqlBaseParser.predicated(SqlBaseParser.java:5955)
at
com.facebook.presto.sql.parser.SqlBaseParser.booleanExpression(SqlBaseParser.java:5858)
at com.facebook.presto.sql.parser.SqlBaseParser.expression(SqlBaseParser.java:5613)
at com.facebook.presto.sql.parser.SqlBaseParser.selectItem(SqlBaseParser.java:4706)
at
com.facebook.presto.sql.parser.SqlBaseParser.querySpecification(SqlBaseParser.java:3718)
at com.facebook.presto.sql.parser.SqlBaseParser.queryPrimary(SqlBaseParser.java:3494)
at com.facebook.presto.sql.parser.SqlBaseParser.queryTerm(SqlBaseParser.java:3306)
at com.facebook.presto.sql.parser.SqlBaseParser.queryNoWith(SqlBaseParser.java:3162)
at com.facebook.presto.sql.parser.SqlBaseParser.query(SqlBaseParser.java:2634)
at com.facebook.presto.sql.parser.SqlBaseParser.statement(SqlBaseParser.java:1294)
at com.facebook.presto.sql.parser.SqlBaseParser.singleStatement(SqlBaseParser.java:231)
at com.facebook.presto.sql.parser.SqlParser.invokeParser(SqlParser.java:92)
at com.facebook.presto.sql.parser.SqlParser.createStatement(SqlParser.java:65)
at com.facebook.presto.execution.SqlQueryManager.createQuery(SqlQueryManager.java:289)
at
com.facebook.presto.server.StatementResource$Query.<init>(StatementResource.java:309)
at com.facebook.presto.server.StatementResource.createQuery(StatementResource.java:172)
at sun.reflect.GeneratedMethodAccessor474.invoke(Unknown Source) [:1.8.0_92]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[rt.jar:1.8.0_92]
at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_92]
at
org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
at
org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)
at
org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)
at
org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:160)
at
org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)
at
org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
at
org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
at
org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)
at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)
at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)
at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:845)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1689)
at io.airlift.http.server.TraceTokenFilter.doFilter(TraceTokenFilter.java:63)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1676)
at io.airlift.http.server.TimingFilter.doFilter(TimingFilter.java:52)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1676)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:581)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:395)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1182)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1112)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at
org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:119)
at
org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:169)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
at org.eclipse.jetty.server.Server.handle(Server.java:523)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:320)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
at
org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
at
org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume(ExecuteProduceConsume.java:303)
at
org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:148)
at
org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:136)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:671)
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:589)
... 1 more
Caused by: org.antlr.v4.runtime.InputMismatchException
at org.antlr.v4.runtime.DefaultErrorStrategy.sync(DefaultErrorStrategy.java:268)
at com.facebook.presto.sql.parser.SqlBaseParser.type(SqlBaseParser.java:8666)
... 77 more
{code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)