abhishekkumaresan opened a new issue #10476:
URL: https://github.com/apache/druid/issues/10476
This is my Druid sql query
```
SELECT TIMESTAMPADD(HOUR,time_extract(t1.__time,'Hour')
,DATE_TRUNC('DAY',t1.__time)) AS __timestamp,
(max_val1 - min_val1) AS VAL1,
(max_val2 - min_val2) AS VAL2,
(max_val3 - min_val3) AS VAL3,
t1.UID AS UID
FROM
(SELECT __time,
VAL1 AS max_val1,
UID,
VAL2 AS max_val2,
VAL3 AS max_val3
FROM one_month
WHERE __time > TIME_PARSE('2020-09-26','yyyy-MM-dd')
and __time in
(SELECT MAX(__time)
FROM one_month
WHERE __time > TIME_PARSE('2020-09-26','yyyy-MM-dd')
GROUP BY TIMESTAMPADD(HOUR,time_extract(__time,'Hour')
,DATE_TRUNC('DAY',__time)) ,
UID) ) AS t1
INNER JOIN
(SELECT __time ,
VAL1 AS min_val1,
UID,
VAL2 AS min_val2,
VAL3 AS min_val3
FROM one_month
WHERE __time > TIME_PARSE('2020-09-26','yyyy-MM-dd')
and __time in
(SELECT MIN(__time)
FROM one_month
WHERE __time > TIME_PARSE('2020-09-26','yyyy-MM-dd')
GROUP BY TIMESTAMPADD(HOUR,time_extract(__time,'Hour')
,DATE_TRUNC('DAY',__time)) ,
UID) ) AS t2 ON DATE_TRUNC('HOUR',t2.__time)
=DATE_TRUNC('HOUR',t1.__time) AND t1.UID = t2.UID
```
When I run this query I get AssertionError
`druid error: Unknown error (Unknown): <html> <head> <meta
http-equiv="Content-Type" content="text/html;charset=utf-8"/> <title>Error 500
Server Error</title> </head> <body><h2>HTTP ERROR 500</h2> <p>Problem accessing
/druid/v2/sql. Reason: <pre> Server Error</pre></p><h3>Caused
by:</h3><pre>javax.servlet.ServletException: java.lang.AssertionError at
com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:420)
at
com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:558)
at
com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:733)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at
com.google.inject.servlet.ServletDefinition.doServiceImpl(ServletDefinition.java:286)
at
com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:276)
at
com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:181)
at com.google.inject.servlet.ManagedServletPipeline.serv
ice(ManagedServletPipeline.java:91) at
com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:120)
at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:135) at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at
org.apache.druid.server.security.PreResponseAuthorizationCheckFilter.doFilter(PreResponseAuthorizationCheckFilter.java:82)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at
org.apache.druid.server.security.AllowHttpMethodsResourceFilter.doFilter(AllowHttpMethodsResourceFilter.java:78)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at
org.apache.druid.server.security.AllowOptionsResourceFilter.doFilter(AllowOptionsResourceFilter.java:75)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at
org.apache.druid.server.security.AllowAllAuthenticator$1.doFilter(AllowAllAuthen
ticator.java:84) at
org.apache.druid.server.security.AuthenticationWrappingFilter.doFilter(AuthenticationWrappingFilter.java:59)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at
org.apache.druid.server.security.SecuritySanityCheckFilter.doFilter(SecuritySanityCheckFilter.java:86)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:533)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255)
at
org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255)
at
org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1340)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)
at
org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201)
at
org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1242)
at
org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144)
at
org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:740)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:61) at
org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:174)
at
org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
at org.eclipse.jetty.server.Server.handle(Server.java:503) at
org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:364) at
org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260) at
org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305)
at org.eclipse.jetty.io.FillI
nterest.fillable(FillInterest.java:103) at
org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118) at
org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765)
at
org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683)
at java.lang.Thread.run(Thread.java:748) Caused by: java.lang.AssertionError at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.getRootField(SqlToRelConverter.java:4448)
at
org.apache.calcite.sql2rel.SqlToRelConverter.adjustInputRef(SqlToRelConverter.java:3765)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:3737)
at
org.apache.calcite.sql2rel.SqlToRelConverter.access$2200(SqlToRelConverter.java:217)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4796)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4092)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:317) at org
.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4656)
at
org.apache.calcite.sql2rel.StandardConvertletTable.convertExpressionList(StandardConvertletTable.java:793)
at
org.apache.calcite.sql2rel.StandardConvertletTable.convertCall(StandardConvertletTable.java:769)
at
org.apache.calcite.sql2rel.StandardConvertletTable.lambda$new$2(StandardConvertletTable.java:120)
at
org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:63)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4787)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4092)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4656)
at
org.apache.calcite.sql2rel.StandardConvertletTable.lambda$new$9(StandardConvertletTable.java:205)
at org.apache.calcite.sql2rel
.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:63) at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4787)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4092)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4656)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:3939)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:670)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:627)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3181)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:563)
at org.apache.calcite.prepare.PlannerImpl.rel(PlannerImpl.java:254) at
org.apache.druid.sql.calcite.pla
nner.DruidPlanner.plan(DruidPlanner.java:136) at
org.apache.druid.sql.SqlLifecycle.plan(SqlLifecycle.java:168) at
org.apache.druid.sql.SqlLifecycle.plan(SqlLifecycle.java:179) at
org.apache.druid.sql.SqlLifecycle.planAndAuthorize(SqlLifecycle.java:240) at
org.apache.druid.sql.http.SqlResource.doPost(SqlResource.java:95) at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498) at
com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
at
com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205)
at
com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.ja
va:75) at
com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
at
com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
at
com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
at
com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
at
com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)
at
com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)
at
com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
at
com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
at
com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
... 45 more </pre> <h3>Caused by:</h3><pre>java.lang.AssertionError at
org.apache.calcite.sql2rel.SqlToRelC
onverter$Blackboard.getRootField(SqlToRelConverter.java:4448) at
org.apache.calcite.sql2rel.SqlToRelConverter.adjustInputRef(SqlToRelConverter.java:3765)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:3737)
at
org.apache.calcite.sql2rel.SqlToRelConverter.access$2200(SqlToRelConverter.java:217)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4796)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4092)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:317) at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4656)
at
org.apache.calcite.sql2rel.StandardConvertletTable.convertExpressionList(StandardConvertletTable.java:793)
at
org.apache.calcite.sql2rel.StandardConvertletTable.convertCall(StandardConvertletTable.java:769)
at
org.apache.calcite.sql2rel.StandardConvertletTable.lambda$new$2(StandardConvertlet
Table.java:120) at
org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:63)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4787)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4092)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4656)
at
org.apache.calcite.sql2rel.StandardConvertletTable.lambda$new$9(StandardConvertletTable.java:205)
at
org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:63)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4787)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4092)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpressi
on(SqlToRelConverter.java:4656) at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:3939)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:670)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:627)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3181)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:563)
at org.apache.calcite.prepare.PlannerImpl.rel(PlannerImpl.java:254) at
org.apache.druid.sql.calcite.planner.DruidPlanner.plan(DruidPlanner.java:136)
at org.apache.druid.sql.SqlLifecycle.plan(SqlLifecycle.java:168) at
org.apache.druid.sql.SqlLifecycle.plan(SqlLifecycle.java:179) at
org.apache.druid.sql.SqlLifecycle.planAndAuthorize(SqlLifecycle.java:240) at
org.apache.druid.sql.http.SqlResource.doPost(SqlResource.java:95) at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.ref
lect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498) at
com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
at
com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205)
at
com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
at
com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
at
com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
at
com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
at
com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
at com.sun.
jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)
at
com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)
at
com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
at
com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
at
com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
at
com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:558)
at
com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:733)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at
com.google.inject.servlet.ServletDefinition.doServiceImpl(ServletDefinition.java:286)
at
com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:276)
at
com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:18
1) at
com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
at
com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:120)
at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:135) at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at
org.apache.druid.server.security.PreResponseAuthorizationCheckFilter.doFilter(PreResponseAuthorizationCheckFilter.java:82)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at
org.apache.druid.server.security.AllowHttpMethodsResourceFilter.doFilter(AllowHttpMethodsResourceFilter.java:78)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at
org.apache.druid.server.security.AllowOptionsResourceFilter.doFilter(AllowOptionsResourceFilter.java:75)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at org.apache.druid.serv
er.security.AllowAllAuthenticator$1.doFilter(AllowAllAuthenticator.java:84) at
org.apache.druid.server.security.AuthenticationWrappingFilter.doFilter(AuthenticationWrappingFilter.java:59)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at
org.apache.druid.server.security.SecuritySanityCheckFilter.doFilter(SecuritySanityCheckFilter.java:86)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:533)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255)
at
org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255)
at
org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1340)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203)
at org.eclipse.je
tty.servlet.ServletHandler.doScope(ServletHandler.java:473) at
org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201)
at
org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1242)
at
org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144)
at
org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:740)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:61) at
org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:174)
at
org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
at org.eclipse.jetty.server.Server.handle(Server.java:503) at
org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:364) at
org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260) at
org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded
(AbstractConnection.java:305) at
org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103) at
org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118) at
org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765)
at
org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683)
at java.lang.Thread.run(Thread.java:748) </pre> <hr><a
href="http://eclipse.org/jetty";>Powered by Jetty:// 9.4.12.v20180830</a><hr/>
</body> </html> `
explain_plan on the same query gives `druid error: Unknown exception
(org.apache.calcite.sql.parser.SqlParseException): Non-query expression
encountered in illegal context`
when I remove this condition `DATE_TRUNC('HOUR',t2.__time)
=DATE_TRUNC('HOUR',t1.__time)` ON the join the query runs and gives an output
The short question is how to JOIN on Date and Hour of my timestamp value ?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
