sascha-coenen opened a new issue #9323: Regression: using forceLimitPushDown option on SQL queries throws exception URL: https://github.com/apache/druid/issues/9323 ### Affected Version 0.11.x - 0.16.0 The feature worked well for us in Druid 0.10.x. We haven't had a chance to test it in Druid 0.17.x yet as it will take us a couple of weeks to migrate. ### Description In Druid 0.10 and earlier, it was possible to set the forceLimitPushDown option to "true" for both JSON queries and SQL queries. We highly depend on this feature as it drastically accelerates a broad range of queries at the expense of delivering approximate results which we found to be very precise. Since Druid 0.11, setting the forceLimitPushDown option to "true" does not work for SQL queries anymore. For native JSON queries, the option still works fine. Below I attached screenshots depicting a working query with the forceLimitPushDown flag set to false as well as the same query failing if the flag is set to true. <img width="551" alt="limitpushdown-on-off-comparison" src="https://user-images.githubusercontent.com/1635350/73957544-c7446080-4906-11ea-89a5-36c56e66a3c6.png";> The attached exception which is raised on the broker rightfully states that the forceLimitPushDown flag can only be applied if the query contains a LIMIT clause, but as can be seen from the screenshot, my query does contain a LIMIT clause. To make sure that the sql query is legit, I pasted it into the Druid SQL webconsole and used the "explain query" function to translate it into an identical JSON query. Submitting this JSON query with the forceLimitPushDown flag set to true works fine and from the swiftness of the execution I can also tell that the feature was actually engaged. ` Following is the exception raised on the Druid broker: [java] java.lang.RuntimeException: Error while applying rule DruidQueryRule(AGGREGATE), args [rel#162407:LogicalAggregate.NONE.[](input=rel#162401:Subset#8.NONE.[],group={4, 11, 21},Revenue=SUM($0)), rel#162413:DruidQueryRel.NONE.[](query={"queryType":"scan","dataSource":{"type":"table","name":"revenue_statistics"},"intervals":{"type":"intervals","intervals":["2019-09-01T00:00:00.001Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"order":"none","filter":null,"columns":["AccountableRevenue","AdRequests","AdspaceID","AdvertiserID","ApplicationID","ApplicationTypeID","CampaignID","Clicks","CountryCode","CreativeID","DealId","DemandPartnerID","DemandPartnerType","LineItemID","LineItemTypeID","MonetizationChannel","MonetizationPartner","MultiplierID","NoAds","OrderID","PublisherFee","PublisherID","PublisherRevenue","SeatBidID","ServedAds","SiteID","SmaatoShareRevenue","SomaClicks","SomaViews","Views","__time","recordCount"],"legacy":false,"context":{"forceLimitPushDown":true,"priority":0,"sqlQueryId":"18d65d15-c8be-4152-aa95-24eef3f6ed6d"},"descending":false,"granularity":{"type":"all"}},signature={AccountableRevenue:FLOAT, AdRequests:LONG, AdspaceID:STRING, AdvertiserID:STRING, ApplicationID:STRING, ApplicationTypeID:STRING, CampaignID:STRING, Clicks:LONG, CountryCode:STRING, CreativeID:STRING, DealId:STRING, DemandPartnerID:STRING, DemandPartnerType:STRING, LineItemID:STRING, LineItemTypeID:STRING, MonetizationChannel:STRING, MonetizationPartner:STRING, MultiplierID:STRING, NoAds:LONG, OrderID:STRING, PublisherFee:FLOAT, PublisherID:STRING, PublisherRevenue:FLOAT, SeatBidID:STRING, ServedAds:LONG, SiteID:STRING, SmaatoShareRevenue:FLOAT, SomaClicks:LONG, SomaViews:LONG, Views:LONG, __time:LONG, recordCount:LONG})] [java] at org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:236) ~[calcite-core-1.17.0.jar:1.17.0] [java] at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:646) ~[calcite-core-1.17.0.jar:1.17.0] [java] at org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:339) ~[calcite-core-1.17.0.jar:1.17.0] [java] at org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:358) ~[calcite-core-1.17.0.jar:1.17.0] [java] at org.apache.calcite.prepare.PlannerImpl.transform(PlannerImpl.java:337) ~[calcite-core-1.17.0.jar:1.17.0] [java] at org.apache.druid.sql.calcite.planner.DruidPlanner.planWithDruidConvention(DruidPlanner.java:119) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.calcite.planner.DruidPlanner.plan(DruidPlanner.java:89) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.SqlLifecycle.plan(SqlLifecycle.java:141) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.SqlLifecycle.plan(SqlLifecycle.java:152) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.SqlLifecycle.planAndAuthorize(SqlLifecycle.java:218) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.http.SqlResource.doPost(SqlResource.java:91) [druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at sun.reflect.GeneratedMethodAccessor441.invoke(Unknown Source) ~[?:?] [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_212] [java] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_212] [java] at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) [jersey-server-1.19.3.jar:1.19.3] [java] at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205) [jersey-server-1.19.3.jar:1.19.3] [java] at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75) [jersey-server-1.19.3.jar:1.19.3] [java] at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302) [jersey-server-1.19.3.jar:1.19.3] [java] at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108) [jersey-server-1.19.3.jar:1.19.3] [java] at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) [jersey-server-1.19.3.jar:1.19.3] [java] at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) [jersey-server-1.19.3.jar:1.19.3] [java] at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542) [jersey-server-1.19.3.jar:1.19.3] [java] at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473) [jersey-server-1.19.3.jar:1.19.3] [java] at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419) [jersey-server-1.19.3.jar:1.19.3] [java] at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409) [jersey-server-1.19.3.jar:1.19.3] [java] at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409) [jersey-servlet-1.19.3.jar:1.19.3] [java] at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:558) [jersey-servlet-1.19.3.jar:1.19.3] [java] at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:733) [jersey-servlet-1.19.3.jar:1.19.3] [java] at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) [javax.servlet-api-3.1.0.jar:3.1.0] [java] at com.google.inject.servlet.ServletDefinition.doServiceImpl(ServletDefinition.java:286) [guice-servlet-4.1.0.jar:?] [java] at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:276) [guice-servlet-4.1.0.jar:?] [java] at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:181) [guice-servlet-4.1.0.jar:?] [java] at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91) [guice-servlet-4.1.0.jar:?] [java] at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:85) [guice-servlet-4.1.0.jar:?] [java] at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:120) [guice-servlet-4.1.0.jar:?] [java] at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:135) [guice-servlet-4.1.0.jar:?] [java] at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642) [jetty-servlet-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.apache.druid.server.security.PreResponseAuthorizationCheckFilter.doFilter(PreResponseAuthorizationCheckFilter.java:82) [druid-server-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642) [jetty-servlet-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.apache.druid.server.security.AllowOptionsResourceFilter.doFilter(AllowOptionsResourceFilter.java:75) [druid-server-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642) [jetty-servlet-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.apache.druid.server.security.AllowAllAuthenticator$1.doFilter(AllowAllAuthenticator.java:84) [druid-server-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.server.security.AuthenticationWrappingFilter.doFilter(AuthenticationWrappingFilter.java:59) [druid-server-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642) [jetty-servlet-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.apache.druid.server.security.SecuritySanityCheckFilter.doFilter(SecuritySanityCheckFilter.java:86) [druid-server-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642) [jetty-servlet-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.apache.druid.server.initialization.jetty.LimitRequestsFilter.doFilter(LimitRequestsFilter.java:64) [druid-server-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642) [jetty-servlet-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:533) [jetty-servlet-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1253) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473) [jetty-servlet-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1155) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:724) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:61) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:169) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.Server.handle(Server.java:531) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:352) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:281) [jetty-io-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102) [jetty-io-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118) [jetty-io-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:760) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:678) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503] [java] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_212] [java] Caused by: org.apache.druid.java.util.common.IAE: When forcing limit push down, a limit spec must be provided. [java] at org.apache.druid.query.groupby.GroupByQuery.validateAndGetForceLimitPushDown(GroupByQuery.java:453) ~[druid-processing-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.query.groupby.GroupByQuery.determineApplyLimitPushDown(GroupByQuery.java:507) ~[druid-processing-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.query.groupby.GroupByQuery.<init>(GroupByQuery.java:223) ~[druid-processing-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.query.groupby.GroupByQuery.<init>(GroupByQuery.java:140) ~[druid-processing-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.calcite.rel.DruidQuery.toGroupByQuery(DruidQuery.java:858) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.calcite.rel.DruidQuery.computeQuery(DruidQuery.java:645) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.calcite.rel.DruidQuery.<init>(DruidQuery.java:195) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.calcite.rel.PartialDruidQuery.build(PartialDruidQuery.java:306) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.calcite.rel.DruidQueryRel.toDruidQuery(DruidQueryRel.java:97) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.calcite.rel.DruidQueryRel.toDruidQueryForExplaining(DruidQueryRel.java:109) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.calcite.rel.DruidRel.isValidDruidQuery(DruidRel.java:62) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.druid.sql.calcite.rule.DruidRules$DruidQueryRule.onMatch(DruidRules.java:132) ~[druid-sql-0.16.0-incubating.jar:0.16.0-incubating] [java] at org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:212) ~[calcite-core-1.17.0.jar:1.17.0] [java] ... 76 more `
---------------------------------------------------------------- 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] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
![https://user-images.githubusercontent.com/1635350/73957544-c7446080-4906-11ea-89a5-36c56e66a3c6.png"](https://user-images.githubusercontent.com/1635350/73957544-c7446080-4906-11ea-89a5-36c56e66a3c6.png"){kind=link}