You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Mariano Ruiz (JIRA)" <ji...@apache.org> on 2015/03/02 06:57:04 UTC
[jira] [Created] (DRILL-2352) MongoDB join queries can't select
fields
Mariano Ruiz created DRILL-2352:
-----------------------------------
Summary: MongoDB join queries can't select fields
Key: DRILL-2352
URL: https://issues.apache.org/jira/browse/DRILL-2352
Project: Apache Drill
Issue Type: Bug
Components: Storage - MongoDB
Affects Versions: 0.7.0
Environment: Ubuntu 14.04 64 bits, OpenJDK 1.7.0_75, Mongo 2.6.8
Reporter: Mariano Ruiz
Assignee: B Anil Kumar
When I perform this *JOIN* query between two MongoDB documents, there is no problem:
{code:sql}
select z.city, s.name from zips as z join states as s on z.state = s.code;
{code}
Result:
{code}
+------------+------------+
| * | *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"} |
{code}
But when I try to select which field I want to see I get this:
{code}
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)
{code}
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:
{code}
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 |
{code}
But this doesn't:
{code}
jdbc:drill:zk=local> select s.name from states as s join zips as z on s.code = z.state limit 5;
+------------+
| name |
+------------+
+------------+
{code}
The same occurs in the WHERE clause, for example this works:
{code:sql}
select z.city from states as s join zips as z on s.code = z.state where z.city = 'LOS ANGELES';
{code}
And this doesn't:
{code}
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)
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)