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