[
https://issues.apache.org/jira/browse/DRILL-6242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16454400#comment-16454400
]
ASF GitHub Bot commented on DRILL-6242:
---------------------------------------
Github user jiang-wu commented on the issue:
https://github.com/apache/drill/pull/1184
Yes. There are at least two issues.
One is about how Drill represent Date, Time, Timestamp internally using a
UTC based instant representation and fudges the timezone in order to make the
none time zone fields looking right, that Paul outlined nicely above. To
really fix this, one would need to first define how Drill wishes to handle
Date, Time, Timestamp: e.g. no time zone at all, time zone aware but not
preserving, time zone aware and preserving, etc. One can look at how databases
handle time zones to get some inspirations. This part is too ambitious for me
to fix here.
The second is to obtain a complex object value from JDBC interface. Drill
doesn't make a JSON object in order to send the data to a JDBC interface. It
looks like the JDBC interface is simple an alternative accessor to vector data
being transmitted from the server side to the client side. Once the vector
data arrives on the client side, the JDBC layer builds a Map (or List) object
by reading the values from the vectors. The issue I found was that inside this
process, date|time|timestamp values from their respective vector classes were
all represented using the same Java class, hence losing its type information
all together when the Java object is placed inside the Map|List.
So a fix for this part is simple (in concept), just use different Java
types. Except, when making this change, the first issue popped up as one has
to figure out how to make Date and Time out of UTC instant values, which are
these fudged values from the existing logic
> Output format for nested date, time, timestamp values in an object hierarchy
> ----------------------------------------------------------------------------
>
> Key: DRILL-6242
> URL: https://issues.apache.org/jira/browse/DRILL-6242
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Data Types
> Affects Versions: 1.12.0
> Reporter: Jiang Wu
> Assignee: Jiang Wu
> Priority: Major
> Fix For: 1.14.0
>
>
> Some storages (mapr db, mongo db, etc.) have hierarchical objects that
> contain nested fields of date, time, timestamp types. When a query returns
> these objects, the output format for the nested date, time, timestamp, are
> showing the internal object (org.joda.time.DateTime), rather than the logical
> data value.
> For example. Suppose in MongoDB, we have a single object that looks like
> this:
> {code:java}
> > db.test.findOne();
> {
> "_id" : ObjectId("5aa8487d470dd39a635a12f5"),
> "name" : "orange",
> "context" : {
> "date" : ISODate("2018-03-13T21:52:54.940Z"),
> "user" : "jack"
> }
> }
> {code}
> Then connect Drill to the above MongoDB storage, and run the following query
> within Drill:
> {code:java}
> > select t.context.`date`, t.context from test t;
> +--------+---------+
> | EXPR$0 | context |
> +--------+---------+
> | 2018-03-13 |
> {"date":{"dayOfYear":72,"year":2018,"dayOfMonth":13,"dayOfWeek":2,"era":1,"millisOfDay":78774940,"weekOfWeekyear":11,"weekyear":2018,"monthOfYear":3,"yearOfEra":2018,"yearOfCentury":18,"centuryOfEra":20,"millisOfSecond":940,"secondOfMinute":54,"secondOfDay":78774,"minuteOfHour":52,"minuteOfDay":1312,"hourOfDay":21,"zone":{"fixed":true,"id":"UTC"},"millis":1520977974940,"chronology":{"zone":{"fixed":true,"id":"UTC"}},"afterNow":false,"beforeNow":true,"equalNow":false},"user":"jack"}
> |
> {code}
> We can see that from the above output, when the date field is retrieved as a
> top level column, Drill outputs a logical date value. But when the same
> field is within an object hierarchy, Drill outputs the internal object used
> to hold the date value.
> The expected output is the same display for whether the date field is shown
> as a top level column or when it is within an object hierarchy:
> {code:java}
> > select t.context.`date`, t.context from test t;
> +--------+---------+
> | EXPR$0 | context |
> +--------+---------+
> | 2018-03-13 | {"date":"2018-03-13","user":"jack"} |
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
