These examples of nested data queries that use a where clause might help: http://drill.apache.org/docs/json-data-model/#example:-access-a-map-field-in-an-array
http://drill.apache.org/docs/json-data-model/#example:-flatten-an-array-of-maps-using-a-subquery Kristine Hahn Sr. Technical Writer 415-497-8107 @krishahn On Tue, Mar 31, 2015 at 12:38 AM, Subhajit Ghosh <[email protected]> wrote: > I am facing some issues when running a SELECT query with a WHERE clause on > a nested value/column. Note that the query is run on a view of the HBase > table. > > 0: jdbc:drill:schema:hbase:zk=localhost> select > t.json.runtimeConfiguration.properties.jvmHeapUsageInit as val from > IndividualTestRun_ t; > +------------+ > | val | > +------------+ > | 2686 | > | 2539 | > | 3814 | > | 3525 | > | 3227 | > | 3486 | > | 2055 | > | 3191 | > | 2931 | > +------------+ > 9 rows selected (0.692 seconds) > > The SELECT (without the WHERE clause) works as expected. "properties" is a > dictionary. > > 0: jdbc:drill:schema:hbase:zk=localhost> select > t.json.runtimeConfiguration.properties.jvmHeapUsageInit as val from > IndividualTestRun_ t where > t.json.runtimeConfiguration.properties.jvmHeapUsageInit>3000; > Query failed: AssertionError: RexInputRef index 2 out of range 0..1 > > Am I missing something here? Is this supported? I am on Drill 0.8 > > Following is the stack trace: > > 2015-03-31 08:36:24,962 [2ae5b186-84f3-7220-c78a-d67379dd1df8:foreman] INFO > o.a.d.e.s.hbase.TableStatsCalculator - Region size calculation disabled. > 2015-03-31 08:36:25,088 [2ae5b186-84f3-7220-c78a-d67379dd1df8:foreman] INFO > o.a.drill.exec.work.foreman.Foreman - State change requested. PENDING --> > FAILED > org.apache.drill.exec.work.foreman.ForemanException: Unexpected exception > during fragment initialization: null > at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:211) > [drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38) > [drill-common-0.8.0-rebuffed.jar:0.8.0] > at > > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) > [na:1.7.0_71] > at > > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) > [na:1.7.0_71] > at java.lang.Thread.run(Thread.java:745) [na:1.7.0_71] > Caused by: java.lang.reflect.UndeclaredThrowableException: null > at com.sun.proxy.$Proxy61.getRowCount(Unknown Source) ~[na:na] > at > > org.eigenbase.rel.metadata.RelMetadataQuery.getRowCount(RelMetadataQuery.java:96) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.rel.SingleRel.getRows(SingleRel.java:65) > ~[optiq-core-0.9-drill-r20.jar:na] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier$MajorFragmentStat.add(ExcessiveExchangeIdentifier.java:99) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier.visitPrel(ExcessiveExchangeIdentifier.java:74) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier.visitPrel(ExcessiveExchangeIdentifier.java:31) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.BasePrelVisitor.visitProject(BasePrelVisitor.java:48) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.ProjectPrel.accept(ProjectPrel.java:69) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier.visitScreen(ExcessiveExchangeIdentifier.java:61) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier.visitScreen(ExcessiveExchangeIdentifier.java:31) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.ScreenPrel.accept(ScreenPrel.java:64) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier.removeExcessiveEchanges(ExcessiveExchangeIdentifier.java:42) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToPrel(DefaultSqlHandler.java:290) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:140) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:145) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:735) > [drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:202) > [drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > ... 4 common frames omitted > Caused by: java.lang.reflect.InvocationTargetException: null > at sun.reflect.GeneratedMethodAccessor27.invoke(Unknown Source) ~[na:na] > at > > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > ~[na:1.7.0_71] > at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_71] > at > > org.eigenbase.rel.metadata.ReflectiveRelMetadataProvider$2$1.invoke(ReflectiveRelMetadataProvider.java:135) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 21 common frames omitted > Caused by: java.lang.reflect.UndeclaredThrowableException: null > at com.sun.proxy.$Proxy67.getSelectivity(Unknown Source) ~[na:na] > at > > org.eigenbase.rel.metadata.RelMetadataQuery.getSelectivity(RelMetadataQuery.java:218) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.rel.metadata.RelMdRowCount.getRowCount(RelMdRowCount.java:54) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 25 common frames omitted > Caused by: java.lang.reflect.InvocationTargetException: null > at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source) ~[na:na] > at > > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > ~[na:1.7.0_71] > at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_71] > at > > org.eigenbase.rel.metadata.ReflectiveRelMetadataProvider$2$1.invoke(ReflectiveRelMetadataProvider.java:135) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 28 common frames omitted > Caused by: java.lang.reflect.UndeclaredThrowableException: null > at com.sun.proxy.$Proxy67.getSelectivity(Unknown Source) ~[na:na] > at > > org.eigenbase.rel.metadata.RelMetadataQuery.getSelectivity(RelMetadataQuery.java:218) > ~[optiq-core-0.9-drill-r20.jar:na] > at > > org.eigenbase.rel.metadata.RelMdSelectivity.getSelectivity(RelMdSelectivity.java:168) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 32 common frames omitted > Caused by: java.lang.reflect.InvocationTargetException: null > at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source) ~[na:na] > at > > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > ~[na:1.7.0_71] > at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_71] > at > > org.eigenbase.rel.metadata.ReflectiveRelMetadataProvider$2$1.invoke(ReflectiveRelMetadataProvider.java:135) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 35 common frames omitted > Caused by: java.lang.AssertionError: RexInputRef index 2 out of range 0..1 > at > > org.eigenbase.rex.RexProgramBuilder$RegisterInputShuttle.visitInputRef(RexProgramBuilder.java:891) > ~[optiq-core-0.9-drill-r20.jar:na] > at > > org.eigenbase.rex.RexProgramBuilder$RegisterInputShuttle.visitInputRef(RexProgramBuilder.java:878) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.rex.RexInputRef.accept(RexInputRef.java:103) > ~[optiq-core-0.9-drill-r20.jar:na] > at > > org.eigenbase.rex.RexProgramBuilder.registerInput(RexProgramBuilder.java:271) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.rex.RexProgramBuilder.addProject(RexProgramBuilder.java:184) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.rex.RexProgramBuilder.addIdentity(RexProgramBuilder.java:801) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.relopt.RelOptUtil.pushFilterPastProject(RelOptUtil.java:2178) > ~[optiq-core-0.9-drill-r20.jar:na] > at > > org.eigenbase.rel.metadata.RelMdSelectivity.getSelectivity(RelMdSelectivity.java:166) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 39 common frames omitted > 2015-03-31 08:36:25,096 [2ae5b186-84f3-7220-c78a-d67379dd1df8:foreman] INFO > o.a.drill.exec.work.foreman.Foreman - foreman cleaning up - status: [] > 2015-03-31 08:36:25,099 [2ae5b186-84f3-7220-c78a-d67379dd1df8:foreman] > ERROR o.a.drill.exec.work.foreman.Foreman - Error > 77b27118-3f8f-48c2-af42-9a4246b1de50: AssertionError: RexInputRef index 2 > out of range 0..1 > org.apache.drill.exec.work.foreman.ForemanException: Unexpected exception > during fragment initialization: null > at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:211) > [drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38) > [drill-common-0.8.0-rebuffed.jar:0.8.0] > at > > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) > [na:1.7.0_71] > at > > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) > [na:1.7.0_71] > at java.lang.Thread.run(Thread.java:745) [na:1.7.0_71] > Caused by: java.lang.reflect.UndeclaredThrowableException: null > at com.sun.proxy.$Proxy61.getRowCount(Unknown Source) ~[na:na] > at > > org.eigenbase.rel.metadata.RelMetadataQuery.getRowCount(RelMetadataQuery.java:96) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.rel.SingleRel.getRows(SingleRel.java:65) > ~[optiq-core-0.9-drill-r20.jar:na] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier$MajorFragmentStat.add(ExcessiveExchangeIdentifier.java:99) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier.visitPrel(ExcessiveExchangeIdentifier.java:74) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier.visitPrel(ExcessiveExchangeIdentifier.java:31) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.BasePrelVisitor.visitProject(BasePrelVisitor.java:48) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.ProjectPrel.accept(ProjectPrel.java:69) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier.visitScreen(ExcessiveExchangeIdentifier.java:61) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier.visitScreen(ExcessiveExchangeIdentifier.java:31) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.ScreenPrel.accept(ScreenPrel.java:64) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.physical.visitor.ExcessiveExchangeIdentifier.removeExcessiveEchanges(ExcessiveExchangeIdentifier.java:42) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToPrel(DefaultSqlHandler.java:290) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:140) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at > > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:145) > ~[drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:735) > [drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:202) > [drill-java-exec-0.8.0-rebuffed.jar:0.8.0] > ... 4 common frames omitted > Caused by: java.lang.reflect.InvocationTargetException: null > at sun.reflect.GeneratedMethodAccessor27.invoke(Unknown Source) ~[na:na] > at > > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > ~[na:1.7.0_71] > at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_71] > at > > org.eigenbase.rel.metadata.ReflectiveRelMetadataProvider$2$1.invoke(ReflectiveRelMetadataProvider.java:135) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 21 common frames omitted > Caused by: java.lang.reflect.UndeclaredThrowableException: null > at com.sun.proxy.$Proxy67.getSelectivity(Unknown Source) ~[na:na] > at > > org.eigenbase.rel.metadata.RelMetadataQuery.getSelectivity(RelMetadataQuery.java:218) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.rel.metadata.RelMdRowCount.getRowCount(RelMdRowCount.java:54) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 25 common frames omitted > Caused by: java.lang.reflect.InvocationTargetException: null > at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source) ~[na:na] > at > > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > ~[na:1.7.0_71] > at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_71] > at > > org.eigenbase.rel.metadata.ReflectiveRelMetadataProvider$2$1.invoke(ReflectiveRelMetadataProvider.java:135) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 28 common frames omitted > Caused by: java.lang.reflect.UndeclaredThrowableException: null > at com.sun.proxy.$Proxy67.getSelectivity(Unknown Source) ~[na:na] > at > > org.eigenbase.rel.metadata.RelMetadataQuery.getSelectivity(RelMetadataQuery.java:218) > ~[optiq-core-0.9-drill-r20.jar:na] > at > > org.eigenbase.rel.metadata.RelMdSelectivity.getSelectivity(RelMdSelectivity.java:168) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 32 common frames omitted > Caused by: java.lang.reflect.InvocationTargetException: null > at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source) ~[na:na] > at > > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > ~[na:1.7.0_71] > at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_71] > at > > org.eigenbase.rel.metadata.ReflectiveRelMetadataProvider$2$1.invoke(ReflectiveRelMetadataProvider.java:135) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 35 common frames omitted > Caused by: java.lang.AssertionError: RexInputRef index 2 out of range 0..1 > at > > org.eigenbase.rex.RexProgramBuilder$RegisterInputShuttle.visitInputRef(RexProgramBuilder.java:891) > ~[optiq-core-0.9-drill-r20.jar:na] > at > > org.eigenbase.rex.RexProgramBuilder$RegisterInputShuttle.visitInputRef(RexProgramBuilder.java:878) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.rex.RexInputRef.accept(RexInputRef.java:103) > ~[optiq-core-0.9-drill-r20.jar:na] > at > > org.eigenbase.rex.RexProgramBuilder.registerInput(RexProgramBuilder.java:271) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.rex.RexProgramBuilder.addProject(RexProgramBuilder.java:184) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.rex.RexProgramBuilder.addIdentity(RexProgramBuilder.java:801) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.relopt.RelOptUtil.pushFilterPastProject(RelOptUtil.java:2178) > ~[optiq-core-0.9-drill-r20.jar:na] > at > > org.eigenbase.rel.metadata.RelMdSelectivity.getSelectivity(RelMdSelectivity.java:166) > ~[optiq-core-0.9-drill-r20.jar:na] > ... 39 common frames omitted > > > -- > Thanks and Regards, > Subhajit >
