Date function is not working with mongo storage plugin
Closed this issue · 5 comments
Running below query on MongoDB using mongo storage plugin (with Drill 1.20.2) fails with error:
SELECT
Name
, DATE(UpdatedBy)
FROM
mongo.tables.fe79d3ea-44b0-4d1b-92c3-d3932e8977e5
WHERE (RequesterID = 'RID1032')
LIMIT 500
2022-08-12 06:41:10,047 [1d0a09f8-c32d-9883-f67a-462faa2566b4:foreman] INFO o.a.drill.exec.work.foreman.Foreman - Query text for query with id 1d0a09f8-c32d-9883-f67a-462faa2566b4 issued by admin: SELECT
Name
, DATE(UpdatedBy)
FROM
mongo.tables.fe79d3ea-44b0-4d1b-92c3-d3932e8977e5
WHERE (RequesterID = 'RID1032')
LIMIT 500
2022-08-12 06:41:10,266 [1d0a09f8-c32d-9883-f67a-462faa2566b4:frag:1:0] INFO o.a.d.e.w.fragment.FragmentExecutor - 1d0a09f8-c32d-9883-f67a-462faa2566b4:1:0: State change requested AWAITING_ALLOCATION --> FAILED
2022-08-12 06:41:10,267 [1d0a09f8-c32d-9883-f67a-462faa2566b4:frag:1:0] INFO o.a.d.e.w.fragment.FragmentExecutor - 1d0a09f8-c32d-9883-f67a-462faa2566b4:1:0: State change requested FAILED --> FINISHED
2022-08-12 06:41:10,268 [1d0a09f8-c32d-9883-f67a-462faa2566b4:frag:1:0] ERROR o.a.d.e.w.fragment.FragmentExecutor - SYSTEM ERROR: ExpressionParsingException: extraneous input 'date' expecting {'if', 'case', 'cast', Convert, AnyValue, '!', '+', '-', '(', ''', Bool, Number, Identifier, QuotedIdentifier, String}
Fragment: 1:0
Please, refer to logs for more information.
[Error Id: 68761310-0a7b-4aff-b2f6-688683ee10a9 on :31010]
org.apache.drill.common.exceptions.UserException: SYSTEM ERROR: ExpressionParsingException: extraneous input 'date' expecting {'if', 'case', 'cast', Convert, AnyValue, '!', '+', '-', '(', ''', Bool, Number, Identifier, QuotedIdentifier, String}
Fragment: 1:0
Please, refer to logs for more information.
[Error Id: 68761310-0a7b-4aff-b2f6-688683ee10a9 on :31010]
at org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:657)
at org.apache.drill.exec.work.fragment.FragmentExecutor.sendFinalState(FragmentExecutor.java:392)
at org.apache.drill.exec.work.fragment.FragmentExecutor.cleanup(FragmentExecutor.java:244)
at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:359)
at org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)
Caused by: com.fasterxml.jackson.databind.JsonMappingException: extraneous input 'date' expecting {'if', 'case', 'cast', Convert, AnyValue, '!', '+', '-', '(', ''', Bool, Number, Identifier, QuotedIdentifier, String} (through reference chain: org.apache.drill.exec.physical.config.SingleSender["child"]->org.apache.drill.exec.physical.config.Project["exprs"]->java.util.ArrayList[1]->org.apache.drill.common.logical.data.NamedExpression["expr"])
at com.fasterxml.jackson.databind.JsonMappingException.wrapWithPath(JsonMappingException.java:392)
at com.fasterxml.jackson.databind.JsonMappingException.wrapWithPath(JsonMappingException.java:351)
at com.fasterxml.jackson.databind.deser.BeanDeserializerBase.wrapAndThrow(BeanDeserializerBase.java:1821)
at com.fasterxml.jackson.databind.deser.BeanDeserializer._deserializeWithErrorWrapping(BeanDeserializer.java:565)
at com.fasterxml.jackson.databind.deser.BeanDeserializer._deserializeUsingPropertyBased(BeanDeserializer.java:438)
at com.fasterxml.jackson.databind.deser.BeanDeserializerBase.deserializeFromObjectUsingNonDefault(BeanDeserializerBase.java:1405)
at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserializeFromObject(BeanDeserializer.java:351)
at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserialize(BeanDeserializer.java:184)
at com.fasterxml.jackson.databind.deser.std.CollectionDeserializer._deserializeFromArray(CollectionDeserializer.java:355)
at com.fasterxml.jackson.databind.deser.std.CollectionDeserializer.deserialize(CollectionDeserializer.java:244)
at com.fasterxml.jackson.databind.deser.std.CollectionDeserializer.deserialize(CollectionDeserializer.java:28)
at com.fasterxml.jackson.databind.deser.SettableBeanProperty.deserialize(SettableBeanProperty.java:542)
at com.fasterxml.jackson.databind.deser.BeanDeserializer._deserializeWithErrorWrapping(BeanDeserializer.java:563)
at com.fasterxml.jackson.databind.deser.BeanDeserializer._deserializeUsingPropertyBased(BeanDeserializer.java:438)
at com.fasterxml.jackson.databind.deser.BeanDeserializerBase.deserializeFromObjectUsingNonDefault(BeanDeserializerBase.java:1405)
at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserializeFromObject(BeanDeserializer.java:351)
at com.fasterxml.jackson.databind.deser.BeanDeserializerBase.deserializeWithObjectId(BeanDeserializerBase.java:1371)
at com.fasterxml.jackson.databind.deser.BeanDeserializer._deserializeOther(BeanDeserializer.java:217)
at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserialize(BeanDeserializer.java:186)
at com.fasterxml.jackson.databind.jsontype.impl.AsPropertyTypeDeserializer._deserializeTypedForId(AsPropertyTypeDeserializer.java:144)
at com.fasterxml.jackson.databind.jsontype.impl.AsPropertyTypeDeserializer.deserializeTypedFromObject(AsPropertyTypeDeserializer.java:110)
at com.fasterxml.jackson.databind.deser.AbstractDeserializer.deserializeWithType(AbstractDeserializer.java:263)
at com.fasterxml.jackson.databind.deser.SettableBeanProperty.deserialize(SettableBeanProperty.java:539)
at com.fasterxml.jackson.databind.deser.BeanDeserializer._deserializeWithErrorWrapping(BeanDeserializer.java:563)
at com.fasterxml.jackson.databind.deser.BeanDeserializer._deserializeUsingPropertyBased(BeanDeserializer.java:438)
at com.fasterxml.jackson.databind.deser.BeanDeserializerBase.deserializeFromObjectUsingNonDefault(BeanDeserializerBase.java:1405)
at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserializeFromObject(BeanDeserializer.java:351)
at com.fasterxml.jackson.databind.deser.BeanDeserializerBase.deserializeWithObjectId(BeanDeserializerBase.java:1371)
at com.fasterxml.jackson.databind.deser.BeanDeserializer._deserializeOther(BeanDeserializer.java:217)
at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserialize(BeanDeserializer.java:186)
at com.fasterxml.jackson.databind.jsontype.impl.AsPropertyTypeDeserializer._deserializeTypedForId(AsPropertyTypeDeserializer.java:144)
at com.fasterxml.jackson.databind.jsontype.impl.AsPropertyTypeDeserializer.deserializeTypedFromObject(AsPropertyTypeDeserializer.java:110)
at com.fasterxml.jackson.databind.deser.AbstractDeserializer.deserializeWithType(AbstractDeserializer.java:263)
at com.fasterxml.jackson.databind.deser.impl.TypeWrappedDeserializer.deserialize(TypeWrappedDeserializer.java:74)
at com.fasterxml.jackson.databind.deser.DefaultDeserializationContext.readRootValue(DefaultDeserializationContext.java:322)
at com.fasterxml.jackson.databind.ObjectReader._bindAndClose(ObjectReader.java:2051)
at com.fasterxml.jackson.databind.ObjectReader.readValue(ObjectReader.java:1492)
at org.apache.drill.exec.planner.PhysicalPlanReader.readFragmentRoot(PhysicalPlanReader.java:103)
at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:288)
... 4 common frames omitted
Caused by: org.apache.drill.common.exceptions.ExpressionParsingException: extraneous input 'date' expecting {'if', 'case', 'cast', Convert, AnyValue, '!', '+', '-', '(', ''', Bool, Number, Identifier, QuotedIdentifier, String}
at org.apache.drill.common.parser.ErrorListener.syntaxError(ErrorListener.java:35)
at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)
at org.antlr.v4.runtime.DefaultErrorStrategy.reportUnwantedToken(DefaultErrorStrategy.java:377)
at org.antlr.v4.runtime.DefaultErrorStrategy.singleTokenDeletion(DefaultErrorStrategy.java:548)
at org.antlr.v4.runtime.DefaultErrorStrategy.sync(DefaultErrorStrategy.java:266)
at org.apache.drill.common.expression.parser.ExprParser.expression(ExprParser.java:1660)
at org.apache.drill.common.expression.parser.ExprParser.parse(ExprParser.java:202)
at org.apache.drill.common.parser.LogicalExpressionParser.parse(LogicalExpressionParser.java:51)
at org.apache.drill.common.expression.LogicalExpression$De.deserialize(LogicalExpression.java:64)
at org.apache.drill.common.expression.LogicalExpression$De.deserialize(LogicalExpression.java:47)
at com.fasterxml.jackson.databind.deser.SettableBeanProperty.deserialize(SettableBeanProperty.java:542)
at com.fasterxml.jackson.databind.deser.BeanDeserializer._deserializeWithErrorWrapping(BeanDeserializer.java:563)
... 39 common frames omitted
This query was working fine in drill 1.17.0
There are few more queries which are failing, attached log for reference
DrillErrors-1.20.2.log
https://drill.apache.org/docs/date-time-and-timestamp/
Date function is working in 1.17.0
I'm with @jnturton . I'm surprised that ever worked. Try:
SELECT CAST('2010-01-01' AS DATE)
Alternatively, we could easily implement that...
SELECT CAST('2010-01-01' AS DATE) . This is working. Thanks for the quick response.