[DRILL-2121] Join on complex data with sub-queries is returning empty maps

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Fix Version/s: 0.9.0
• Component/s: Execution - Data Types, Execution - Relational Operators
• Assignee: Hanifi Gunes
• Reporter: Rahul Kumar Challapalli
• Created: 29/Jan/15 22:46
• Updated: 21/Apr/15 20:58
• Resolved: 24/Feb/15 21:36

Description

git.commit.id.abbrev=3e33880

Data Set :

{
    "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"}
  ]
}

The below query returns empty maps for the 3rd field

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"} | {}          |

If we interchange the sub-queries, drill returns an empty map for event

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"} |

Issue Links

• relates to: DRILL-2163 Projecting nested types past join returns invalid result