[ 
https://issues.apache.org/jira/browse/DRILL-6242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16434701#comment-16434701
 ] 

ASF GitHub Bot commented on DRILL-6242:
---------------------------------------

Github user jiang-wu commented on a diff in the pull request:

    https://github.com/apache/drill/pull/1184#discussion_r180925699
  
    --- Diff: exec/vector/src/main/codegen/templates/FixedValueVectors.java ---
    @@ -509,15 +509,15 @@ public long getTwoAsLong(int index) {
         public ${friendlyType} getObject(int index) {
           org.joda.time.DateTime date = new org.joda.time.DateTime(get(index), 
org.joda.time.DateTimeZone.UTC);
           date = 
date.withZoneRetainFields(org.joda.time.DateTimeZone.getDefault());
    -      return date;
    +      return new java.sql.Date(date.getMillis());
    --- End diff --
    
    Good point.  Someone with deeper knowledge should take a look.  As far as I 
can tell, I think the problem with SqlAccessor is that it uses the vector type 
to know whether to invoke getDate() vs getTimestamp().  However, such vector 
type knowledge is not there when complex type such as List and Map are 
materialized in memory to form Java JsonStringArrayList and JsonStringHashMap.  
    
    In https://issues.apache.org/jira/browse/DRILL-6242, the example describes 
this scenario:
    
    `select t.context.`date`, t.context from test t;`
    
    where the `date` field is inside a Map type.  And we select the field by 
itself as well as select the Map on the same query.  This query returns:
    
    `+--------+---------+ `
    `| EXPR$0 | context | `
    `+--------+---------+ `
    `| 2018-03-13 | {"date":{"dayOfYear":72,"year":2018, ... |`
    
    One can see that the first column shows the date in the right type.  But 
the same date is shown as a different type inside the Map.  In the vector 
package, when reading the [List|Map]Vector, the code produces its nested member 
values via the generic method "getObject()".  Since all three vector type 
returned the same DataObject type as the representation, there are no 
distinction.
    
    For the type information to be carried within the List | Map, it would seem 
that the value should be of distinct types.  These can be 
java.sql.[Date|Time|Timestamp] or some other [Date|Time|Timestamp] classes.
    



> 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)

Reply via email to