drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mariano Ruiz (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-2352) MongoDB join queries can't select fields
Date Mon, 02 Mar 2015 18:24:05 GMT

    [ https://issues.apache.org/jira/browse/DRILL-2352?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14343535#comment-14343535
] 

Mariano Ruiz commented on DRILL-2352:
-------------------------------------

Hello Bhallamudi, I just build the latest version from Git repository (https://git-wip-us.apache.org/repos/asf/drill.git,
commit {{#9c0738d94f246443554a1b6d03c7a8d4d21d29a8}}), and things get worse.

Now with the same data sets, where I try this:

{code:sql}
select * from states as s join zips as z on z.state = s.code;
{code}

I get this:

{code}
+------------+------------+
|     *      |     *0     |
+------------+------------+
+------------+------------+
No rows selected (16,88 seconds)
{code}

With version 0.7.0 this query works. And of course none of the previous tests works with both
builds.

> 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)

Mime
View raw message