You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by Z0ltrix <z0...@pm.me.INVALID> on 2022/02/23 09:56:23 UTC
WG: Superset Drill Time Range Filter
Hi drill devs,
we have a problem with our superset -> drill connection with time range filters, as described below.
Superset sends the following to drill:
WHERE `startTime` >= '2022-02-14 00:00:00.000000'
AND `startTime` < '2022-02-21 00:00:00.000000'
ORDER BY `startTime` DESC
and i get the following error:
SYSTEM ERROR: ClassCastException: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
Please, refer to logs for more information.
(org.apache.drill.exec.work.foreman.ForemanException) Unexpected exception during fragment initialization: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
org.apache.drill.exec.work.foreman.Foreman.run():305
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():748
Caused By (java.lang.ClassCastException) org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
org.apache.drill.exec.expr.FilterBuilder.getValueExpressionFromConst():208
org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():240
org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():58
org.apache.drill.common.expression.FunctionHolderExpression.accept():53
org.apache.drill.exec.expr.FilterBuilder.generateNewExpressions():268
org.apache.drill.exec.expr.FilterBuilder.handleCompareFunction():278
org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():246
org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():58
org.apache.drill.common.expression.FunctionHolderExpression.accept():53
org.apache.drill.exec.expr.FilterBuilder.buildFilterPredicate():80
org.apache.drill.exec.physical.base.AbstractGroupScanWithMetadata.getFilterPredicate():317
org.apache.drill.exec.store.parquet.ParquetPushDownFilter.doOnMatch():150
org.apache.drill.exec.store.parquet.ParquetPushDownFilter$2.onMatch():103
org.apache.calcite.plan.AbstractRelOptPlanner.fireRule():319
org.apache.calcite.plan.hep.HepPlanner.applyRule():561
org.apache.calcite.plan.hep.HepPlanner.applyRules():420
org.apache.calcite.plan.hep.HepPlanner.executeInstruction():257
org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute():127
org.apache.calcite.plan.hep.HepPlanner.executeProgram():216
org.apache.calcite.plan.hep.HepPlanner.findBestExp():203
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():419
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():370
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():353
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToPrel():536
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():178
org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan():216
org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan():121
org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():87
org.apache.drill.exec.work.foreman.Foreman.runSQL():593
org.apache.drill.exec.work.foreman.Foreman.run():276
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():748
When i manually resend the query with TIMESTAMP as here:
WHERE `startTime` >= TIMESTAMP '2022-02-14 00:00:00.000000'
AND `startTime` < TIMESTAMP '2022-02-21 00:00:00.000000'
ORDER BY `startTime` DESC
Everything is fine, but superset doesnt create the query this way.
So, now to my question:Is this error message legit because of the missing "TIMESTAMP" before the timestamp string, or do we have a problem here in drill?
Regards
Christian
------- Original Message -------
Z0ltrix <z0...@pm.me.INVALID> schrieb am Mittwoch, 23. Februar 2022 um 10:49:
> Hello superset devs,
>
> we have a problem with our superset -> drill connection with time range filters.
>
> When we filter a dashboard by time range (last week, month, etc.) i get an
>
> SYSTEM ERROR: ClassCastException: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
>
> from drill.
>
> I dont want to talk here too much about the drill error because this is a topic for the drill project, but i think we could solve this also by adding something to db_engine_specs/drill.py
>
> Superset sends the following to drill:
>
> WHERE `startTime` >= '2022-02-14 00:00:00.000000'
> AND `startTime` < '2022-02-21 00:00:00.000000'
> ORDER BY `startTime` DESC
>
> Superset should send the following filter:
> WHERE `startTime` >= TIMESTAMP '2022-02-14 00:00:00.000000'
> AND `startTime` < TIMESTAMP '2022-02-21 00:00:00.000000'
> ORDER BY `startTime` DESC
>
> My Question is, which classmethod of BaseEngineSpec must be overwritten and maybe you could give me a hint how i could do this.
> If im aware what to do, i can create a PR for this.
>
> Regards,
> Christian
AW: Re: Superset Drill Time Range Filter
Posted by Z0ltrix <z0...@pm.me.INVALID>.
I tested drill 1.16 and 1.20 hadoop2 rc4 with same behaviour
------- Original Message -------
luoc <lu...@apache.org> schrieb am Mittwoch, 23. Februar 2022 um 11:27:
> Which Drill version are you running?
>
> > On Feb 23, 2022, at 17:57, Z0ltrix z0ltrix@pm.me.invalid wrote:
> >
> > Hi drill devs,
> >
> > we have a problem with our superset -> drill connection with time range filters, as described below.
> >
> > Superset sends the following to drill:
> >
> > WHERE `startTime` >= '2022-02-14 00:00:00.000000'
> >
> > AND `startTime` < '2022-02-21 00:00:00.000000'
> >
> > ORDER BY `startTime` DESC
> >
> > and i get the following error:
> >
> > SYSTEM ERROR: ClassCastException: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> >
> > Please, refer to logs for more information.
> >
> > (org.apache.drill.exec.work.foreman.ForemanException) Unexpected exception during fragment initialization: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> >
> > org.apache.drill.exec.work.foreman.Foreman.run():305
> >
> > java.util.concurrent.ThreadPoolExecutor.runWorker():1149
> >
> > java.util.concurrent.ThreadPoolExecutor$Worker.run():624
> >
> > java.lang.Thread.run():748
> >
> > Caused By (java.lang.ClassCastException) org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> >
> > org.apache.drill.exec.expr.FilterBuilder.getValueExpressionFromConst():208
> >
> > org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():240
> >
> > org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():58
> >
> > org.apache.drill.common.expression.FunctionHolderExpression.accept():53
> >
> > org.apache.drill.exec.expr.FilterBuilder.generateNewExpressions():268
> >
> > org.apache.drill.exec.expr.FilterBuilder.handleCompareFunction():278
> >
> > org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():246
> >
> > org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():58
> >
> > org.apache.drill.common.expression.FunctionHolderExpression.accept():53
> >
> > org.apache.drill.exec.expr.FilterBuilder.buildFilterPredicate():80
> >
> > org.apache.drill.exec.physical.base.AbstractGroupScanWithMetadata.getFilterPredicate():317
> >
> > org.apache.drill.exec.store.parquet.ParquetPushDownFilter.doOnMatch():150
> >
> > org.apache.drill.exec.store.parquet.ParquetPushDownFilter$2.onMatch():103
> >
> > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule():319
> >
> > org.apache.calcite.plan.hep.HepPlanner.applyRule():561
> >
> > org.apache.calcite.plan.hep.HepPlanner.applyRules():420
> >
> > org.apache.calcite.plan.hep.HepPlanner.executeInstruction():257
> >
> > org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute():127
> >
> > org.apache.calcite.plan.hep.HepPlanner.executeProgram():216
> >
> > org.apache.calcite.plan.hep.HepPlanner.findBestExp():203
> >
> > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():419
> >
> > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():370
> >
> > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():353
> >
> > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToPrel():536
> >
> > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():178
> >
> > org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan():216
> >
> > org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan():121
> >
> > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():87
> >
> > org.apache.drill.exec.work.foreman.Foreman.runSQL():593
> >
> > org.apache.drill.exec.work.foreman.Foreman.run():276
> >
> > java.util.concurrent.ThreadPoolExecutor.runWorker():1149
> >
> > java.util.concurrent.ThreadPoolExecutor$Worker.run():624
> >
> > java.lang.Thread.run():748
> >
> > When i manually resend the query with TIMESTAMP as here:
> >
> > WHERE `startTime` >= TIMESTAMP '2022-02-14 00:00:00.000000'
> >
> > AND `startTime` < TIMESTAMP '2022-02-21 00:00:00.000000'
> >
> > ORDER BY `startTime` DESC
> >
> > Everything is fine, but superset doesnt create the query this way.
> >
> > So, now to my question:
> >
> > Is this error message legit because of the missing "TIMESTAMP" before the timestamp string, or do we have a problem here in drill?
> >
> > Regards
> >
> > Christian
> >
> > ------- Original Message -------
> >
> > Z0ltrix z0ltrix@pm.me.INVALID schrieb am Mittwoch, 23. Februar 2022 um 10:49:
> >
> > > Hello superset devs,
> > >
> > > we have a problem with our superset -> drill connection with time range filters.
> > >
> > > When we filter a dashboard by time range (last week, month, etc.) i get an
> > >
> > > SYSTEM ERROR: ClassCastException: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> > >
> > > from drill.
> > >
> > > I dont want to talk here too much about the drill error because this is a topic for the drill project, but i think we could solve this also by adding something to db_engine_specs/drill.py
> > >
> > > Superset sends the following to drill:
> > >
> > > WHERE `startTime` >= '2022-02-14 00:00:00.000000'
> > >
> > > AND `startTime` < '2022-02-21 00:00:00.000000'
> > >
> > > ORDER BY `startTime` DESC
> > >
> > > Superset should send the following filter:
> > >
> > > WHERE `startTime` >= TIMESTAMP '2022-02-14 00:00:00.000000'
> > >
> > > AND `startTime` < TIMESTAMP '2022-02-21 00:00:00.000000'
> > >
> > > ORDER BY `startTime` DESC
> > >
> > > My Question is, which classmethod of BaseEngineSpec must be overwritten and maybe you could give me a hint how i could do this.
> > >
> > > If im aware what to do, i can create a PR for this.
> > >
> > > Regards,
> > >
> > > Christian
> >
> > <publickey - z0ltrix@pm.me - 0xF0E154C5.asc>
Re: Superset Drill Time Range Filter
Posted by luoc <lu...@apache.org>.
Which Drill version are you running?
> On Feb 23, 2022, at 17:57, Z0ltrix <z0...@pm.me.invalid> wrote:
>
>
> Hi drill devs,
>
> we have a problem with our superset -> drill connection with time range filters, as described below.
>
> Superset sends the following to drill:
> WHERE `startTime` >= '2022-02-14 00:00:00.000000'
> AND `startTime` < '2022-02-21 00:00:00.000000'
> ORDER BY `startTime` DESC
>
> and i get the following error:
>
> SYSTEM ERROR: ClassCastException: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
>
>
> Please, refer to logs for more information.
>
>
> (org.apache.drill.exec.work.foreman.ForemanException) Unexpected exception during fragment initialization: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> org.apache.drill.exec.work.foreman.Foreman.run():305
> java.util.concurrent.ThreadPoolExecutor.runWorker():1149
> java.util.concurrent.ThreadPoolExecutor$Worker.run():624
> java.lang.Thread.run():748
> Caused By (java.lang.ClassCastException) org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> org.apache.drill.exec.expr.FilterBuilder.getValueExpressionFromConst():208
> org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():240
> org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():58
> org.apache.drill.common.expression.FunctionHolderExpression.accept():53
> org.apache.drill.exec.expr.FilterBuilder.generateNewExpressions():268
> org.apache.drill.exec.expr.FilterBuilder.handleCompareFunction():278
> org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():246
> org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():58
> org.apache.drill.common.expression.FunctionHolderExpression.accept():53
> org.apache.drill.exec.expr.FilterBuilder.buildFilterPredicate():80
> org.apache.drill.exec.physical.base.AbstractGroupScanWithMetadata.getFilterPredicate():317
> org.apache.drill.exec.store.parquet.ParquetPushDownFilter.doOnMatch():150
> org.apache.drill.exec.store.parquet.ParquetPushDownFilter$2.onMatch():103
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule():319
> org.apache.calcite.plan.hep.HepPlanner.applyRule():561
> org.apache.calcite.plan.hep.HepPlanner.applyRules():420
> org.apache.calcite.plan.hep.HepPlanner.executeInstruction():257
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute():127
> org.apache.calcite.plan.hep.HepPlanner.executeProgram():216
> org.apache.calcite.plan.hep.HepPlanner.findBestExp():203
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():419
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():370
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():353
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToPrel():536
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():178
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan():216
> org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan():121
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():87
> org.apache.drill.exec.work.foreman.Foreman.runSQL():593
> org.apache.drill.exec.work.foreman.Foreman.run():276
> java.util.concurrent.ThreadPoolExecutor.runWorker():1149
> java.util.concurrent.ThreadPoolExecutor$Worker.run():624
> java.lang.Thread.run():748
>
> When i manually resend the query with TIMESTAMP as here:
> WHERE `startTime` >= TIMESTAMP '2022-02-14 00:00:00.000000'
> AND `startTime` < TIMESTAMP '2022-02-21 00:00:00.000000'
> ORDER BY `startTime` DESC
>
> Everything is fine, but superset doesnt create the query this way.
>
> So, now to my question:
> Is this error message legit because of the missing "TIMESTAMP" before the timestamp string, or do we have a problem here in drill?
>
> Regards
> Christian
>
> ------- Original Message -------
> Z0ltrix <z0...@pm.me.INVALID> schrieb am Mittwoch, 23. Februar 2022 um 10:49:
>
>> Hello superset devs,
>>
>> we have a problem with our superset -> drill connection with time range filters.
>>
>> When we filter a dashboard by time range (last week, month, etc.) i get an
>> SYSTEM ERROR: ClassCastException: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
>> from drill.
>>
>> I dont want to talk here too much about the drill error because this is a topic for the drill project, but i think we could solve this also by adding something to db_engine_specs/drill.py
>>
>> Superset sends the following to drill:
>> WHERE `startTime` >= '2022-02-14 00:00:00.000000'
>> AND `startTime` < '2022-02-21 00:00:00.000000'
>> ORDER BY `startTime` DESC
>>
>> Superset should send the following filter:
>> WHERE `startTime` >= TIMESTAMP '2022-02-14 00:00:00.000000'
>> AND `startTime` < TIMESTAMP '2022-02-21 00:00:00.000000'
>> ORDER BY `startTime` DESC
>>
>> My Question is, which classmethod of BaseEngineSpec must be overwritten and maybe you could give me a hint how i could do this.
>> If im aware what to do, i can create a PR for this.
>>
>> Regards,
>> Christian
>>
>
>
> <publickey - z0ltrix@pm.me - 0xF0E154C5.asc>
AW: Re: WG: Superset Drill Time Range Filter
Posted by Z0ltrix <z0...@pm.me.INVALID>.
When i manually resend the query with TIMESTAMP:
WHERE `startTime` >= TIMESTAMP '2022-02-14 00:00:00.000000'
AND `startTime` < TIMESTAMP '2022-02-21 00:00:00.000000'
ORDER BY `startTime` DESC
Everything is fine, but superset doesnt create the query this way.
fyi... i have already created a issue in superset https://github.com/apache/superset/issues/18869
------- Original Message -------
James Turton <dz...@apache.org> schrieb am Mittwoch, 23. Februar 2022 um 12:46:
> As a matter of interest, if you test directly against Drill with the
>
> following timestamp literal expressions, what happens?
>
> SELECT *
>
> FROM dfs.foo.bar
>
> WHERE `startTime` >= timestamp '2022-02-14 00:00:00.000000'
>
> AND `startTime` < timestamp '2022-02-21 00:00:00.000000'
>
> ORDER BY `startTime` DESC
>
> On 2022/02/23 11:56, Z0ltrix wrote:
>
> > Hi drill devs,
> >
> > we have a problem with our superset -> drill connection with time
> >
> > range filters, as described below.
> >
> > Superset sends the following to drill:
> >
> > WHERE `startTime` >= '2022-02-14 00:00:00.000000'
> >
> > AND `startTime` < '2022-02-21 00:00:00.000000'
> >
> > ORDER BY `startTime` DESC
> >
> > and i get the following error:
> >
> > SYSTEM ERROR: ClassCastException: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> >
> > Please, refer to logs for more information.
> >
> > (org.apache.drill.exec.work.foreman.ForemanException) Unexpected exception during fragment initialization: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> >
> > org.apache.drill.exec.work.foreman.Foreman.run():305
> >
> > java.util.concurrent.ThreadPoolExecutor.runWorker():1149
> >
> > java.util.concurrent.ThreadPoolExecutor$Worker.run():624
> >
> > java.lang.Thread.run():748
> >
> > Caused By (java.lang.ClassCastException) org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> >
> > org.apache.drill.exec.expr.FilterBuilder.getValueExpressionFromConst():208
> >
> > org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():240
> >
> > org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():58
> >
> > org.apache.drill.common.expression.FunctionHolderExpression.accept():53
> >
> > org.apache.drill.exec.expr.FilterBuilder.generateNewExpressions():268
> >
> > org.apache.drill.exec.expr.FilterBuilder.handleCompareFunction():278
> >
> > org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():246
> >
> > org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():58
> >
> > org.apache.drill.common.expression.FunctionHolderExpression.accept():53
> >
> > org.apache.drill.exec.expr.FilterBuilder.buildFilterPredicate():80
> >
> > org.apache.drill.exec.physical.base.AbstractGroupScanWithMetadata.getFilterPredicate():317
> >
> > org.apache.drill.exec.store.parquet.ParquetPushDownFilter.doOnMatch():150
> >
> > org.apache.drill.exec.store.parquet.ParquetPushDownFilter$2.onMatch():103
> >
> > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule():319
> >
> > org.apache.calcite.plan.hep.HepPlanner.applyRule():561
> >
> > org.apache.calcite.plan.hep.HepPlanner.applyRules():420
> >
> > org.apache.calcite.plan.hep.HepPlanner.executeInstruction():257
> >
> > org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute():127
> >
> > org.apache.calcite.plan.hep.HepPlanner.executeProgram():216
> >
> > org.apache.calcite.plan.hep.HepPlanner.findBestExp():203
> >
> > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():419
> >
> > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():370
> >
> > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():353
> >
> > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToPrel():536
> >
> > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():178
> >
> > org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan():216
> >
> > org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan():121
> >
> > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():87
> >
> > org.apache.drill.exec.work.foreman.Foreman.runSQL():593
> >
> > org.apache.drill.exec.work.foreman.Foreman.run():276
> >
> > java.util.concurrent.ThreadPoolExecutor.runWorker():1149
> >
> > java.util.concurrent.ThreadPoolExecutor$Worker.run():624
> >
> > java.lang.Thread.run():748
> >
> > When i manually resend the query with TIMESTAMP as here:
> >
> > WHERE `startTime` >= TIMESTAMP '2022-02-14 00:00:00.000000'
> >
> > AND `startTime` < TIMESTAMP '2022-02-21 00:00:00.000000'
> >
> > ORDER BY `startTime` DESC
> >
> > Everything is fine, but superset doesnt create the query this way.
> >
> > So, now to my question:
> >
> > Is this error message legit because of the missing "TIMESTAMP" before
> >
> > the timestamp string, or do we have a problem here in drill?
> >
> > Regards
> >
> > Christian
> >
> > ------- Original Message -------
> >
> > Z0ltrix z0ltrix@pm.me.INVALID schrieb am Mittwoch, 23. Februar 2022
> >
> > um 10:49:
> >
> > > Hello superset devs,
> > >
> > > we have a problem with our superset -> drill connection with time
> > >
> > > range filters.
> > >
> > > When we filter a dashboard by time range (last week, month, etc.) i
> > >
> > > get an
> > >
> > > SYSTEM ERROR: ClassCastException: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> > >
> > > from drill.
> > >
> > > I dont want to talk here too much about the drill error because this
> > >
> > > is a topic for the drill project, but i think we could solve this
> > >
> > > also by adding something to db_engine_specs/drill.py
> > >
> > > Superset sends the following to drill:
> > >
> > > WHERE `startTime` >= '2022-02-14 00:00:00.000000'
> > >
> > > AND `startTime` < '2022-02-21 00:00:00.000000'
> > >
> > > ORDER BY `startTime` DESC
> > >
> > > Superset should send the following filter:
> > >
> > > WHERE `startTime` >= TIMESTAMP '2022-02-14 00:00:00.000000'
> > >
> > > AND `startTime` < TIMESTAMP '2022-02-21 00:00:00.000000'
> > >
> > > ORDER BY `startTime` DESC
> > >
> > > My Question is, which classmethod of BaseEngineSpec must be
> > >
> > > overwritten and maybe you could give me a hint how i could do this.
> > >
> > > If im aware what to do, i can create a PR for this.
> > >
> > > Regards,
> > >
> > > Christian
Re: WG: Superset Drill Time Range Filter
Posted by James Turton <dz...@apache.org>.
As a matter of interest, if you test directly against Drill with the
following timestamp literal expressions, what happens?
SELECT *
FROM dfs.foo.bar
WHERE `startTime` >= timestamp '2022-02-14 00:00:00.000000'
AND `startTime` < timestamp '2022-02-21 00:00:00.000000'
ORDER BY `startTime` DESC
On 2022/02/23 11:56, Z0ltrix wrote:
> Hi drill devs,
>
> we have a problem with our superset -> drill connection with time
> range filters, as described below.
>
> Superset sends the following to drill:
> WHERE `startTime` >= '2022-02-14 00:00:00.000000'
> AND `startTime` < '2022-02-21 00:00:00.000000'
> ORDER BY `startTime` DESC
>
> and i get the following error:
>
> SYSTEM ERROR: ClassCastException: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
>
>
> Please, refer to logs for more information.
>
>
> (org.apache.drill.exec.work.foreman.ForemanException) Unexpected exception during fragment initialization: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> org.apache.drill.exec.work.foreman.Foreman.run():305
> java.util.concurrent.ThreadPoolExecutor.runWorker():1149
> java.util.concurrent.ThreadPoolExecutor$Worker.run():624
> java.lang.Thread.run():748
> Caused By (java.lang.ClassCastException) org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
> org.apache.drill.exec.expr.FilterBuilder.getValueExpressionFromConst():208
> org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():240
> org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():58
> org.apache.drill.common.expression.FunctionHolderExpression.accept():53
> org.apache.drill.exec.expr.FilterBuilder.generateNewExpressions():268
> org.apache.drill.exec.expr.FilterBuilder.handleCompareFunction():278
> org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():246
> org.apache.drill.exec.expr.FilterBuilder.visitFunctionHolderExpression():58
> org.apache.drill.common.expression.FunctionHolderExpression.accept():53
> org.apache.drill.exec.expr.FilterBuilder.buildFilterPredicate():80
> org.apache.drill.exec.physical.base.AbstractGroupScanWithMetadata.getFilterPredicate():317
> org.apache.drill.exec.store.parquet.ParquetPushDownFilter.doOnMatch():150
> org.apache.drill.exec.store.parquet.ParquetPushDownFilter$2.onMatch():103
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule():319
> org.apache.calcite.plan.hep.HepPlanner.applyRule():561
> org.apache.calcite.plan.hep.HepPlanner.applyRules():420
> org.apache.calcite.plan.hep.HepPlanner.executeInstruction():257
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute():127
> org.apache.calcite.plan.hep.HepPlanner.executeProgram():216
> org.apache.calcite.plan.hep.HepPlanner.findBestExp():203
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():419
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():370
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.transform():353
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToPrel():536
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():178
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan():216
> org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan():121
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():87
> org.apache.drill.exec.work.foreman.Foreman.runSQL():593
> org.apache.drill.exec.work.foreman.Foreman.run():276
> java.util.concurrent.ThreadPoolExecutor.runWorker():1149
> java.util.concurrent.ThreadPoolExecutor$Worker.run():624
> java.lang.Thread.run():748
>
> When i manually resend the query with TIMESTAMP as here:
> WHERE `startTime` >= TIMESTAMP '2022-02-14 00:00:00.000000'
> AND `startTime` < TIMESTAMP '2022-02-21 00:00:00.000000'
> ORDER BY `startTime` DESC
>
> Everything is fine, but superset doesnt create the query this way.
>
> So, now to my question:
> Is this error message legit because of the missing "TIMESTAMP" before
> the timestamp string, or do we have a problem here in drill?
>
> Regards
> Christian
>
> ------- Original Message -------
> Z0ltrix <z0...@pm.me.INVALID> schrieb am Mittwoch, 23. Februar 2022
> um 10:49:
>
>> Hello superset devs,
>>
>> we have a problem with our superset -> drill connection with time
>> range filters.
>>
>> When we filter a dashboard by time range (last week, month, etc.) i
>> get an
>> SYSTEM ERROR: ClassCastException: org.apache.drill.exec.expr.holders.NullableTimeStampHolder cannot be cast to org.apache.drill.exec.expr.holders.TimeStampHolder
>> from drill.
>>
>> I dont want to talk here too much about the drill error because this
>> is a topic for the drill project, but i think we could solve this
>> also by adding something to db_engine_specs/drill.py
>>
>> Superset sends the following to drill:
>> WHERE `startTime` >= '2022-02-14 00:00:00.000000'
>> AND `startTime` < '2022-02-21 00:00:00.000000'
>> ORDER BY `startTime` DESC
>>
>> Superset should send the following filter:
>> WHERE `startTime` >= TIMESTAMP '2022-02-14 00:00:00.000000'
>> AND `startTime` < TIMESTAMP '2022-02-21 00:00:00.000000'
>> ORDER BY `startTime` DESC
>>
>> My Question is, which classmethod of BaseEngineSpec must be
>> overwritten and maybe you could give me a hint how i could do this.
>> If im aware what to do, i can create a PR for this.
>>
>> Regards,
>> Christian
>>
>