Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-57588

Inconsistent query results when an array position is indexed whose value is an array

    • Fully Compatible
    • ALL
    • v5.2, v5.1, v5.0, v4.4, v4.2, v4.0, v3.6
    • Query Optimization 2021-06-28, QO 2021-09-20, QO 2021-10-04, QO 2021-10-18, QO 2021-11-01, QO 2021-11-15, QO 2021-11-29

      When indexing an array element by position (ex: {"a.0"}) if the value at the indexed position is an array, the index won't be multikey and can effect the result of a query if the index is selected as the winning plan.

      db.version() 
      // => 4.4.6
      db.foo.drop();
      db.foo.insert(
      { a: 
          [ 
              [ "b" ],
              [ "c" ]        
          ]
      });
      
      print(db.foo.count({ "a.0": { $elemMatch: { $in: ["b"] } } }));
      // => 1
      
      /*
      "inputStage" : {
          "stage" : "COLLSCAN", 
          "filter" : {
              "a.0" : {
                  "$elemMatch" : {
                      "$eq" : "b"
                  }
              }
          }, 
          "nReturned" : 1.0, 
          "executionTimeMillisEstimate" : 0.0, 
          "works" : 3.0, 
          "advanced" : 1.0, 
          "needTime" : 1.0, 
          "needYield" : 0.0, 
          "saveState" : 0.0, 
          "restoreState" : 0.0, 
          "isEOF" : 1.0, 
          "direction" : "forward", 
          "docsExamined" : 1.0
      }
      */
      

      Now if the following index is created and the operation run again the results are different:

      db.foo.createIndex({ "a.0": 1 })
      print(db.foo.count({ "a.0": { $elemMatch: { $in: ["b"] } } }));
      // => 0
      
      /*
      "inputStage" : {
          "stage" : "FETCH", 
          "filter" : {
              "a.0" : {
                  "$elemMatch" : {
                      "$eq" : "b"
                  }
              }
          }, 
          "inputStage" : {
              "stage" : "IXSCAN", 
              "keyPattern" : {
                  "a.0" : 1.0
              }, 
              "indexName" : "a.0_1", 
              "isMultiKey" : false, 
              "multiKeyPaths" : {
                  "a.0" : [
      
                  ]
              }, 
              "isUnique" : false, 
              "isSparse" : false, 
              "isPartial" : false, 
              "indexVersion" : 2.0, 
              "direction" : "forward", 
              "indexBounds" : {
                  "a.0" : [
                      "[\"b\", \"b\"]"
                  ]
              }
          }
      }
      */
      

            Assignee:
            timour.katchaounov@mongodb.com Timour Katchaounov
            Reporter:
            alex.bevilacqua@mongodb.com Alex Bevilacqua
            Votes:
            0 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: