drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andries Engelbrecht <aengelbre...@maprtech.com>
Subject Re: WHERE clause with nested JSON data
Date Tue, 31 Mar 2015 16:01:13 GMT
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 <subhajit@skipjaq.com> 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 <aengelbrecht@maprtech.com>
> 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 <subhajit@skipjaq.com> 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


Mime
View raw message