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 Wed, 04 Mar 2015 03:33:04 GMT

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

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

Good point Hanafi! I checked the log, and in fact a {{java.lang.IllegalStateException}} is
launched where the query is executed without the LIMIT clause. Please check the files attached.

Drill should display the error or any other message instead of a empty result set in the user
console, so there are two errors:

# An unexpected {{IllegalStateException}} where some queries are requested.
# A bad handling of unexpected errors. *This is more severe than the first error*, because
users believe than the query has result zero and there is no error, so the client software
will propagate the wrong result, showing bad results or taking bad decisions according with
the data set returned.

> 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
>         Attachments: drill-error-select-without-limit.log, drill-select-with-limit.log
>
>
> 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