Nested SQL query not working as expected

[Alex Charlton]

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

[Gian Merlino]

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.

Gian

--
You received this message because you are subscribed to the Google Groups "Druid User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to druid-user+unsubscribe@googlegroups.com.
To post to this group, send email to druid...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/1084949c-b9a6-4d70-8a61-3ad7fd3c57cf%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alex Charlton]

Thanks Gian, that's super informative!

Gian

To unsubscribe from this group and stop receiving emails from it, send an email to druid-user+...@googlegroups.com.