[DRILL-2352] MongoDB join queries can't select fields

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.7.0
• Component/s: Storage - MongoDB

Environment

Ubuntu 14.04 64 bits, OpenJDK 1.7.0_75, Mongo 2.6.8

• Assignee: Unassigned
• Reporter: Mariano Ruiz
• Created: 02/Mar/15 05:56
• Updated: 09/Nov/15 02:45
• Resolved: 09/Nov/15 02:45

Description

When I perform this JOIN query between two MongoDB documents, there is no problem:

select * from zips as z join states as s on z.state = s.code;

Result:

+------------+------------+
|     *      |     *0     |
+------------+------------+
| { "city" : "AGAWAM" , "loc" : [ -72.622739 , 42.070206] , "pop" : 15338 , "state" : "MA"} | { "code" : "CA" , "name" : "California"} |
| { "city" : "AGAWAM" , "loc" : [ -72.622739 , 42.070206] , "pop" : 15338 , "state" : "MA"} | { "code" : "DC" , "name" : "Washington"} |
| { "city" : "AGAWAM" , "loc" : [ -72.622739 , 42.070206] , "pop" : 15338 , "state" : "MA"} | { "code" : "NY" , "name" : "New York"} |
| { "city" : "CUSHMAN" , "loc" : [ -72.51565 , 42.377017] , "pop" : 36963 , "state" : "MA"} | { "code" : "CA" , "name" : "California"} |
| { "city" : "CUSHMAN" , "loc" : [ -72.51565 , 42.377017] , "pop" : 36963 , "state" : "MA"} | { "code" : "DC" , "name" : "Washington"} |

But when I try to select which field I want to see I get this:

0: jdbc:drill:zk=local> select z.city, s.name from zips as z join states as s on z.state = s.code;
Query failed: Query failed: Unexpected exception during fragment initialization: null

Error: exception while executing query: Failure while executing query. (state=,code=0)

I realized after a few tests, that the problem is you can't specify a column from the table left operator in the JOIN-ON clause.

For example, this works:

jdbc:drill:zk=local> select z.city from states as s join zips as z on s.code = z.state limit 5;
+------------+
|    city    |
+------------+
| LOS ANGELES |
| TRUCKEE    |
| TRUCKEE    |
| SOUTH LAKE TAHOE |
| TAHOE VISTA |

But this doesn't:

jdbc:drill:zk=local> select s.name from states as s join zips as z on s.code = z.state limit 5;
+------------+
|    name    |
+------------+
+------------+

The same occurs in the WHERE clause, for example this works:

select z.city from states as s join zips as z on s.code = z.state where z.city = 'LOS ANGELES';

And this doesn't:

0: jdbc:drill:zk=local> select z.city from states as s join zips as z on s.code = z.state where s.code = 'CA' limit 5;
Query failed: Query failed: Unexpected exception during fragment initialization: null

Error: exception while executing query: Failure while executing query. (state=,code=0)

Issue Links

• is related to: DRILL-2430 Improve Error Propagation (Umbrella)
• relates to: DRILL-1692 select * on mongo join returns invalid results