[
https://issues.apache.org/jira/browse/DRILL-2352?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14345256#comment-14345256
]
Mariano Ruiz commented on DRILL-2352:
-------------------------------------
Bhallamudi, the patch you mentioned solves the most of the issues, but remains
some errors.
For example, this works fine and return all the 1.595 rows selected:
{code:sql}
select * from zips as z join states as s on z.state = s.code where z.state =
'NY';
{code}
But this returns no rows, despite there are 1.516 rows:
{code}
0: jdbc:drill:zk=local> select * from zips as z join states as s on z.state =
s.code where z.state = 'CA';
+------------+------------+------------+------------+------------+------------+
| city | loc | pop | state | code | name |
+------------+------------+------------+------------+------------+------------+
+------------+------------+------------+------------+------------+------------+
No rows selected (1,416 seconds)
{code}
But, if I add a LIMIT clause to the query, with a limit less or high than the
number of rows, then works! For example, this returns all the 1.516 rows:
{code:sql}
select * from zips as z join states as s on z.state = s.code where z.state =
'CA' limit 10000;
{code}
As you can see, it's the same query than the previous one, only with a LIMIT
clause added.
These are the JSON to popule both documents in MongoDB to replicate all the
tests:
-
[states.json|https://drive.google.com/uc?export=download&id=0B1v39YERCugsWU5UclNvN3FrNWs]
-
[zips.json|https://drive.google.com/uc?export=download&id=0B1v39YERCugsdDRQUENpb0Z1Vlk]
> 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 * 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)
