MongoSQL: typed key in LEFT JOIN behaves like INNER JOIN

ExportXMLWordPrintableJSON

    • Atlas SQL
    • Needed
    • Hide
      Fixes a bug in LEFT and RIGHT joins where if they had a single simple equality ON condition and both sides of the equality are nullable, the joins are treated as INNER joins instead of LEFT/RIGHT joins. For example, foo LEFT JOIN bar ON foo.a = bar.b would be equivalent to foo INNER JOIN bar ON foo.a = bar.b if both foo.a and bar.b are possibly null or missing.
      Show
      Fixes a bug in LEFT and RIGHT joins where if they had a single simple equality ON condition and both sides of the equality are nullable, the joins are treated as INNER joins instead of LEFT/RIGHT joins. For example, foo LEFT JOIN bar ON foo.a = bar.b would be equivalent to foo INNER JOIN bar ON foo.a = bar.b if both foo.a and bar.b are possibly null or missing.

      Issue Status as of November 6

      ISSUE DESCRIPTION AND IMPACT

      We have identified an issue affecting the SQL interface that prefilters rows before executing a LEFT or RIGHT JOIN when certain query conditions are met. This can lead to incorrect result sets for SQL queries if ALL of the following are true:

      • The query contains at least one LEFT or RIGHT JOIN with a single ON condition that is exactly an equality comparison between a field from the left datasource and a field from the right datasource
      • For a LEFT JOIN, the right datasource must be a simple collection datasource and it may be aliased
      • For RIGHT JOIN, the left datasource must be a simple collection datasource and it may be aliased
      • The schema statically indicates that both fields in the condition are nullable (i.e., may be NULL or MISSING)
      • For a LEFT JOIN, the left datasource contains rows where the join key (field in the predicate) is UNKNOWN (NULL or MISSING)
      • For a RIGHT JOIN, the right datasource contains rows where the join key is UNKNOWN

      Rows for which all the conditions are met will be omitted from the result set.

      DIAGNOSIS AND AFFECTED VERSIONS

      The following versions of the SQL interface components are affected:

      • JDBC 3.0.8 and earlier versions
      • ODBC 2.0.10 and earlier versions
      • MongoSQL versions between 1.1.0 and 1.8.7 (deployed on Atlas Data Federation)

      Only customers with business intelligence dashboards or native queries meeting ALL the criteria for the bug to occur are impacted.

      REMEDIATION AND WORKAROUNDS
      For EA SQL and non-federated Atlas SQL Interface users, the first step is to upgrade their ODBC or JDBC driver(s) to the latest version available on the download center. All users then need to refresh impacted dashboards and update native SQL query results by re-executing impacted SQL queries.

      OTHER AFFECTED TOOLS
      We are not aware of any other affected tool.

      —-----------------------------------------------------

      Original description

      (This issue was originally reported in GitHub, but we've created this ticket for tracking purposes)

      Use Case

      A LEFT JOIN returns the same rows as an INNER JOIN when the join key has a type defined in the schema. This also happens when the type allows null

      If the same field is left untyped, the LEFT JOIN returns the expected rows.

      Data:

       

      use test
db.parents.insertMany([{ _id: 1, name: "P1" }])
db.children.insertMany([
  { _id: 1, parentId: 1 },
  { _id: 2, parentId: null }
])

      Schema:

       

       

        test:
    parents:
      bsonType: "object"
      properties:
        _id: { bsonType: "int" }
        name: { bsonType: "string" }
    children:
      bsonType: "object"
      properties:
        _id: { bsonType: "int" }
        parentId: { bsonType: "int" }

       

       

      Run:

       

      mongosql-cli -u "mongodb://localhost:27017/test?directConnection=true" -d test \
  -f schema.yml --execute --translation \
  "SELECT c._id AS child_id, p.name AS parent_name FROM children c LEFT JOIN parents p ON c.parentId = p._id"

       

       

      Expected: 2 rows (keep rows from children even when there is no matching row in parents)

      {"child_id": 1, "parent_name": "P1"}
{"child_id": 2}

      Actual: 1 row (child 2 silently dropped)

      {"child_id": 1, "parent_name": "P1"}

      User Impact

      • Potentially incorrect result sets for LEFT JOINs.

      Dependencies

      • None

      Acceptance Criteria

      Implementation Requirements

      • Investigate and identify the problem and fix for the LEFT JOIN behavior

      Tests plan

      • Spec Query test validating LEFT JOIN correctness

            Assignee:
            Matthew Chiaravalloti
            Reporter:
            Jonathan Powell
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: