Try a cast to integer on the field as well, just to be sure data types are handled correctly.
—Andries On Mar 31, 2015, at 8:54 AM, Subhajit Ghosh <[email protected]> wrote: > Checked it, there aren't any null values. Any queries with the WHERE clause > containing a nested statement like "WHERE a.b.c>100" is producing the same > error. Anyone succeeded with such queries? > > On 31 March 2015 at 16:27, Andries Engelbrecht <[email protected]> > wrote: > >> Are there any null values? >> >> Perhaps try to filter out null values (if there are any) and then see if >> it succeeds. >> >> Select <….> from …. t where >> t.json.runtimeConfiguration.properties.jvmHeapUsageInit is not null and >> t.json.runtimeConfiguration.properties.jvmHeapUsageInit >3000 >> >> In some cases I found that for the most reliable results to use a subquery >> to filter out nulls and then apply the main query. >> >> —Andries >> >> On 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 >> >> > > > -- > Thanks and Regards, > Subhajit
