-
Type:
Bug
-
Status: Resolved
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 0.7.0
-
Fix Version/s: None
-
Component/s: Storage - MongoDB
-
Labels:None
-
Environment:
Ubuntu 14.04 64 bits, OpenJDK 1.7.0_75, Mongo 2.6.8
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)
- is related to
-
DRILL-2430 Improve Error Propagation (Umbrella)
-
- Open
-
- relates to
-
DRILL-1692 select * on mongo join returns invalid results
-
- Resolved
-