drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rahul Challapalli (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-2121) Join on complex data with sub-queries is returning empty maps
Date Tue, 24 Feb 2015 21:30:07 GMT

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

Rahul Challapalli commented on DRILL-2121:
------------------------------------------

Verified this. It works as expected. Thank You

> Join on complex data with sub-queries is returning empty maps
> -------------------------------------------------------------
>
>                 Key: DRILL-2121
>                 URL: https://issues.apache.org/jira/browse/DRILL-2121
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Execution - Data Types, Execution - Relational Operators
>            Reporter: Rahul Challapalli
>            Assignee: Hanifi Gunes
>            Priority: Blocker
>             Fix For: 0.9.0
>
>
> git.commit.id.abbrev=3e33880
> Data Set :
> {code}
> {
>     "uid": 1,
>     "events" : [
>         { "evnt_id":"e1", "campaign_id":"c1", "event_name":"e1_name", "event_time":1000000,
"type" : "cmpgn1"},
>         { "evnt_id":"e2", "campaign_id":"c1", "event_name":"e2_name", "event_time":2000000,
"type" : "cmpgn4"},
>         { "evnt_id":"e3", "campaign_id":"c1", "event_name":"e3_name", "event_time":3000000,
"type" : "cmpgn1"},
>         { "evnt_id":"e4", "campaign_id":"c1", "event_name":"e4_name", "event_time":4000000,
"type" : "cmpgn1"},
>         { "evnt_id":"e5", "campaign_id":"c2", "event_name":"e5_name", "event_time":5000000,
"type" : "cmpgn3"},
>         { "evnt_id":"e6", "campaign_id":"c1", "event_name":"e6_name", "event_time":6000000,
"type" : "cmpgn9"},
>         { "evnt_id":"e7", "campaign_id":"c1", "event_name":"e7_name", "event_time":7000000,
"type" : "cmpgn3"},
>         { "evnt_id":"e8", "campaign_id":"c2", "event_name":"e8_name", "event_time":8000000,
"type" : "cmpgn2"},
>         { "evnt_id":"e9", "campaign_id":"c2", "event_name":"e9_name", "event_time":9000000,
"type" : "cmpgn4"}
>   ],
>   "transactions" : [
>        { "trans_id":"t1", "amount":100, "trans_time":7777777, "type":"sports"},
>        { "trans_id":"t2", "amount":1000, "trans_time":8888888, "type":"groceries"}
>   ]
> }
> {code}
> The below query returns empty maps for the 3rd field
> {code}
> 0: jdbc:drill:schema=dfs.drillTestDir> select t1.uid, t1.event, t2.transaction from
 (select uid, flatten(events) event from `temp4.json`) t1 inner join (select uid, flatten(transactions)
transaction from `temp4.json`) t2 on t1.uid = t2.uid;
> +------------+------------+-------------+
> |    uid     |   event    | transaction |
> +------------+------------+-------------+
> | 1          | {"evnt_id":"e1","campaign_id":"c1","event_name":"e1_name","event_time":1000000,"type":"cmpgn1"}
| {}          |
> | 1          | {"evnt_id":"e1","campaign_id":"c1","event_name":"e1_name","event_time":1000000,"type":"cmpgn1"}
| {}          |
> | 1          | {"evnt_id":"e2","campaign_id":"c1","event_name":"e2_name","event_time":2000000,"type":"cmpgn4"}
| {}          |
> | 1          | {"evnt_id":"e2","campaign_id":"c1","event_name":"e2_name","event_time":2000000,"type":"cmpgn4"}
| {}          |
> | 1          | {"evnt_id":"e3","campaign_id":"c1","event_name":"e3_name","event_time":3000000,"type":"cmpgn1"}
| {}          |
> | 1          | {"evnt_id":"e3","campaign_id":"c1","event_name":"e3_name","event_time":3000000,"type":"cmpgn1"}
| {}          |
> | 1          | {"evnt_id":"e4","campaign_id":"c1","event_name":"e4_name","event_time":4000000,"type":"cmpgn1"}
| {}          |
> | 1          | {"evnt_id":"e4","campaign_id":"c1","event_name":"e4_name","event_time":4000000,"type":"cmpgn1"}
| {}          |
> | 1          | {"evnt_id":"e5","campaign_id":"c2","event_name":"e5_name","event_time":5000000,"type":"cmpgn3"}
| {}          |
> | 1          | {"evnt_id":"e5","campaign_id":"c2","event_name":"e5_name","event_time":5000000,"type":"cmpgn3"}
| {}          |
> | 1          | {"evnt_id":"e6","campaign_id":"c1","event_name":"e6_name","event_time":6000000,"type":"cmpgn9"}
| {}          |
> | 1          | {"evnt_id":"e6","campaign_id":"c1","event_name":"e6_name","event_time":6000000,"type":"cmpgn9"}
| {}          |
> | 1          | {"evnt_id":"e7","campaign_id":"c1","event_name":"e7_name","event_time":7000000,"type":"cmpgn3"}
| {}          |
> | 1          | {"evnt_id":"e7","campaign_id":"c1","event_name":"e7_name","event_time":7000000,"type":"cmpgn3"}
| {}          |
> | 1          | {"evnt_id":"e8","campaign_id":"c2","event_name":"e8_name","event_time":8000000,"type":"cmpgn2"}
| {}          |
> | 1          | {"evnt_id":"e8","campaign_id":"c2","event_name":"e8_name","event_time":8000000,"type":"cmpgn2"}
| {}          |
> | 1          | {"evnt_id":"e9","campaign_id":"c2","event_name":"e9_name","event_time":9000000,"type":"cmpgn4"}
| {}          |
> | 1          | {"evnt_id":"e9","campaign_id":"c2","event_name":"e9_name","event_time":9000000,"type":"cmpgn4"}
| {}          |
> {code}
> If we interchange the sub-queries, drill returns an empty map for event
> {code}
> 0: jdbc:drill:schema=dfs.drillTestDir> select t1.uid, t2.event, t1.transaction from
 (select uid, flatten(transactions) transaction from `temp4.json`) t1 inner join (select uid,
flatten(events) event from `temp4.json`) t2 on t1.uid = t2.uid;
> +------------+------------+-------------+
> |    uid     |   event    | transaction |
> +------------+------------+-------------+
> | 1          | {}         | {"trans_id":"t1","amount":100,"trans_time":7777777,"type":"sports"}
|
> | 1          | {}         | {"trans_id":"t1","amount":100,"trans_time":7777777,"type":"sports"}
|
> | 1          | {}         | {"trans_id":"t1","amount":100,"trans_time":7777777,"type":"sports"}
|
> | 1          | {}         | {"trans_id":"t1","amount":100,"trans_time":7777777,"type":"sports"}
|
> | 1          | {}         | {"trans_id":"t1","amount":100,"trans_time":7777777,"type":"sports"}
|
> | 1          | {}         | {"trans_id":"t1","amount":100,"trans_time":7777777,"type":"sports"}
|
> | 1          | {}         | {"trans_id":"t1","amount":100,"trans_time":7777777,"type":"sports"}
|
> | 1          | {}         | {"trans_id":"t1","amount":100,"trans_time":7777777,"type":"sports"}
|
> | 1          | {}         | {"trans_id":"t1","amount":100,"trans_time":7777777,"type":"sports"}
|
> | 1          | {}         | {"trans_id":"t2","amount":1000,"trans_time":8888888,"type":"groceries"}
|
> | 1          | {}         | {"trans_id":"t2","amount":1000,"trans_time":8888888,"type":"groceries"}
|
> | 1          | {}         | {"trans_id":"t2","amount":1000,"trans_time":8888888,"type":"groceries"}
|
> | 1          | {}         | {"trans_id":"t2","amount":1000,"trans_time":8888888,"type":"groceries"}
|
> | 1          | {}         | {"trans_id":"t2","amount":1000,"trans_time":8888888,"type":"groceries"}
|
> | 1          | {}         | {"trans_id":"t2","amount":1000,"trans_time":8888888,"type":"groceries"}
|
> | 1          | {}         | {"trans_id":"t2","amount":1000,"trans_time":8888888,"type":"groceries"}
|
> | 1          | {}         | {"trans_id":"t2","amount":1000,"trans_time":8888888,"type":"groceries"}
|
> | 1          | {}         | {"trans_id":"t2","amount":1000,"trans_time":8888888,"type":"groceries"}
|
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message