Nested SQL query not working as expected

Hello!

Is anyone able to help me understand why a relatively simple SQL query isn’t working for me. The query has one subquery, and is expected to result in a value of 1:

select count()/
(
select count(
) as total
from DATA_SOURCE
where __time > current_timestamp - interval ‘8’ hour
) as total
from DATA_SOURCE
where __time > current_timestamp - interval ‘8’ hour

``

The logs, a sample of which can be seen below, are not very illuminating.

Thanks!

2017-05-08T22:44:23,406 WARN [qtp2073333566-68] io.druid.sql.http.SqlResource - Failed to handle query: SqlQuery{query='select count(event_name)/

(

 select count(*) as total

    from DATA_SOURCE

    where  __time > current_timestamp - interval '1' hour

) as total

from DATA_SOURCE

where

    __time > current_timestamp - interval '1' hour

', context={sqlTimeZone=America/Los_Angeles}}

org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node [rel#14206:Subset#8.DRUID.] could not be implemented; planner state:

Root: rel#14206:Subset#8.DRUID.

LOTS OF STATE

    at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:443) ~[calcite-core-1.11.0.jar:1.11.0]

    at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:293) ~[calcite-core-1.11.0.jar:1.11.0]

    at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:819) ~[calcite-core-1.11.0.jar:1.11.0]

    at org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:339) ~[calcite-core-1.11.0.jar:1.11.0]

    at org.apache.calcite.prepare.PlannerImpl.transform(PlannerImpl.java:309) ~[calcite-core-1.11.0.jar:1.11.0]

    at io.druid.sql.calcite.planner.DruidPlanner.planWithDruidConvention(DruidPlanner.java:110) ~[druid-sql-0.10.0.jar:0.10.0]

    at io.druid.sql.calcite.planner.DruidPlanner.plan(DruidPlanner.java:80) ~[druid-sql-0.10.0.jar:0.10.0]

    at io.druid.sql.http.SqlResource.doPost(SqlResource.java:86) [druid-sql-0.10.0.jar:0.10.0]

    at sun.reflect.GeneratedMethodAccessor77.invoke(Unknown Source) ~[?:?]

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_121]

    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_121]

    at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) [jersey-server-1.19.jar:1.19]

    at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205) [jersey-server-1.19.jar:1.19]

LOTS MORE STACK TRACE

Hey Alex,

That means the SQL query can’t be planned as a native Druid query. In turn the reason is because Druid only has the ability to do certain limited kinds of subqueries as native queries (one example is nested groupBys). You should be able to get this working by passing “useFallback” : true in your query context. Remember, though, that with great power comes great responsibility: useFallback activates an interpreter for SQL queries that can generate query plans that involve bringing a really unreasonable amount of data into the Druid query broker. But in this case, you should be fine, since this particular query plan shouldn’t do that.

And finally: if what you’re trying to do is percent of total, there’s a simpler way to do it (which will generate a more efficient plan too):

SELECT CAST(COUNT() FILTER(WHERE countryName = ‘United States’) AS FLOAT) / COUNT() FROM wikiticker;

The cast to float is so you get floating point math rather than integer math. You might want to do that on your other query too, depending on what you’re going for.

Thanks Gian, that’s super informative!