SQL: inconsistent date(time) format handling. #30002
Description
Original comment by @bpintea:
When selecting a column of type DATE, a ISO8601 DATETIME string is returned.
When trying to cast the returned string as DATE, an exception is thrown, the trouble seeming to be related to the milliseconds field: Invalid format: "1953-09-02T00:00:00.000Z" is malformed at ".000Z".
sql> select birth_date from test_emp limit 1;
birth_date
------------------------
1953-09-02T00:00:00.000Z
sql>
sql> SELECT CAST('1953-09-02T00:00:00.000Z' AS DATE);
Server error [Server encountered an error [cannot cast [1953-09-02T00:00:00.000Z] to [Date]:Invalid format: "1953-09-02T00:00:00.000Z" is malformed at ".000Z"]. [SqlIllegalArgumentException[cannot cast [1953-09-02T00:00:00.000Z] to [Date]:Invalid format: "1953-09-02T00:00:00.000Z" is malformed at ".000Z"]; nested: IllegalArgumentException[Invalid format: "1953-09-02T00:00:00.000Z" is malformed at ".000Z"];
at org.elasticsearch.xpack.sql.type.DataTypeConversion$Conversion.lambda$fromString$15(DataTypeConversion.java:372)
at org.elasticsearch.xpack.sql.type.DataTypeConversion$Conversion.convert(DataTypeConversion.java:385)
at org.elasticsearch.xpack.sql.type.DataTypeConversion.convert(DataTypeConversion.java:313)
at org.elasticsearch.xpack.sql.expression.function.scalar.Cast.fold(Cast.java:73)
at org.elasticsearch.xpack.sql.optimizer.Optimizer$ConstantFolding.fold(Optimizer.java:1114)
at org.elasticsearch.xpack.sql.optimizer.Optimizer$ConstantFolding.rule(Optimizer.java:1099)
at org.elasticsearch.xpack.sql.tree.Node.transformDown(Node.java:173)
at org.elasticsearch.xpack.sql.plan.QueryPlan.lambda$transformExpressionsDown$2(QueryPlan.java:72)
at org.elasticsearch.xpack.sql.plan.QueryPlan.doTransformExpression(QueryPlan.java:81)
at org.elasticsearch.xpack.sql.plan.QueryPlan.doTransformExpression(QueryPlan.java:97)
at org.elasticsearch.xpack.sql.plan.QueryPlan.lambda$transformExpressionsDown$3(QueryPlan.java:72)
at org.elasticsearch.xpack.sql.tree.NodeInfo.lambda$transform$0(NodeInfo.java:67)
at org.elasticsearch.xpack.sql.tree.NodeInfo$3.innerTransform(NodeInfo.java:128)
at org.elasticsearch.xpack.sql.tree.NodeInfo.transform(NodeInfo.java:71)
at org.elasticsearch.xpack.sql.tree.Node.transformNodeProps(Node.java:252)
at org.elasticsearch.xpack.sql.tree.Node.lambda$transformPropertiesDown$12(Node.java:236)
at org.elasticsearch.xpack.sql.tree.Node.transformDown(Node.java:173)
at org.elasticsearch.xpack.sql.tree.Node.transformPropertiesDown(Node.java:236)
at org.elasticsearch.xpack.sql.plan.QueryPlan.transformExpressionsDown(QueryPlan.java:72)
at org.elasticsearch.xpack.sql.optimizer.Optimizer$OptimizerExpressionRule.apply(Optimizer.java:1435)
at org.elasticsearch.xpack.sql.optimizer.Optimizer$OptimizerExpressionRule.apply(Optimizer.java:1425)
at org.elasticsearch.xpack.sql.rule.RuleExecutor$Transformation.<init>(RuleExecutor.java:94)
at org.elasticsearch.xpack.sql.rule.RuleExecutor.executeWithInfo(RuleExecutor.java:167)
at org.elasticsearch.xpack.sql.rule.RuleExecutor.execute(RuleExecutor.java:142)
at org.elasticsearch.xpack.sql.optimizer.Optimizer.optimize(Optimizer.java:107)
at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:155)
at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
at org.elasticsearch.xpack.sql.session.SqlSession.preAnalyze(SqlSession.java:147)
at org.elasticsearch.xpack.sql.session.SqlSession.analyzedPlan(SqlSession.java:108)
at org.elasticsearch.xpack.sql.session.SqlSession.optimizedPlan(SqlSession.java:155)
at org.elasticsearch.xpack.sql.session.SqlSession.physicalPlan(SqlSession.java:159)
at org.elasticsearch.xpack.sql.session.SqlSession.sqlExecutable(SqlSession.java:168)
at org.elasticsearch.xpack.sql.session.SqlSession.sql(SqlSession.java:163)
at org.elasticsearch.xpack.sql.execution.PlanExecutor.sql(PlanExecutor.java:76)
at org.elasticsearch.xpack.sql.plugin.TransportSqlQueryAction.operation(TransportSqlQueryAction.java:76)
at org.elasticsearch.xpack.sql.plugin.TransportSqlQueryAction.doExecute(TransportSqlQueryAction.java:63)
at org.elasticsearch.xpack.sql.plugin.TransportSqlQueryAction.doExecute(TransportSqlQueryAction.java:43)
at org.elasticsearch.action.support.TransportAction.doExecute(TransportAction.java:143)
at org.elasticsearch.action.support.TransportAction$RequestFilterChain.proceed(TransportAction.java:167)
at org.elasticsearch.action.support.TransportAction.execute(TransportAction.java:139)
at org.elasticsearch.action.support.TransportAction.execute(TransportAction.java:81)
at org.elasticsearch.client.node.NodeClient.executeLocally(NodeClient.java:83)
at org.elasticsearch.xpack.sql.plugin.RestSqlQueryAction.lambda$prepareRequest$0(RestSqlQueryAction.java:60)
at org.elasticsearch.rest.BaseRestHandler.handleRequest(BaseRestHandler.java:97)
at org.elasticsearch.rest.RestController.dispatchRequest(RestController.java:240)
at org.elasticsearch.rest.RestController.tryAllHandlers(RestController.java:336)
at org.elasticsearch.rest.RestController.dispatchRequest(RestController.java:174)
at org.elasticsearch.http.netty4.Netty4HttpServerTransport.dispatchRequest(Netty4HttpServerTransport.java:467)
at org.elasticsearch.http.netty4.Netty4HttpRequestHandler.channelRead0(Netty4HttpRequestHandler.java:137)
at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340)
at org.elasticsearch.http.netty4.pipelining.HttpPipeliningHandler.channelRead(HttpPipeliningHandler.java:68)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340)
at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102)
at io.netty.handler.codec.MessageToMessageCodec.channelRead(MessageToMessageCodec.java:111)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340)
at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340)
at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340)
at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:310)
at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:284)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340)
at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340)
at io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340)
at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1359)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:935)
at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:134)
at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:645)
at io.netty.channel.nio.NioEventLoop.processSelectedKeysPlain(NioEventLoop.java:545)
at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:499)
at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:459)
at io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:858)
at java.base/java.lang.Thread.run(Thread.java:844)
Caused by: java.lang.IllegalArgumentException: Invalid format: "1953-09-02T00:00:00.000Z" is malformed at ".000Z"
at org.joda.time.format.DateTimeParserBucket.doParseMillis(DateTimeParserBucket.java:187)
at org.joda.time.format.DateTimeFormatter.parseMillis(DateTimeFormatter.java:826)
at org.elasticsearch.xpack.sql.type.DataTypeConversion$Conversion.lambda$fromString$15(DataTypeConversion.java:368)
... 93 more
]]
sql>
sql> SELECT CAST('1953-09-02T00:00:00Z' AS DATE);
CAST(1953-09-02T00:00:00Z)
--------------------------
-515376000000