Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-2352

MongoDB join queries can't select fields

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 0.7.0
    • Fix Version/s: None
    • Component/s: Storage - MongoDB
    • Labels:
      None
    • Environment:

      Ubuntu 14.04 64 bits, OpenJDK 1.7.0_75, Mongo 2.6.8

      Description

      When I perform this JOIN query between two MongoDB documents, there is no problem:

      select * from zips as z join states as s on z.state = s.code;
      

      Result:

      +------------+------------+
      |     *      |     *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"} |
      

      But when I try to select which field I want to see I get this:

      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)
      

      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:

      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 |
      

      But this doesn't:

      jdbc:drill:zk=local> select s.name from states as s join zips as z on s.code = z.state limit 5;
      +------------+
      |    name    |
      +------------+
      +------------+
      

      The same occurs in the WHERE clause, for example this works:

      select z.city from states as s join zips as z on s.code = z.state where z.city = 'LOS ANGELES';
      

      And this doesn't:

      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)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                mrsarm Mariano Ruiz
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: