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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: In Progress
    • Priority: Major - P3
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Querying
    • Operating System:
      ALL
    • Sprint:
      Query Optimization 2021-06-28, QO 2021-09-20, QO 2021-10-04, QO 2021-10-18, QO 2021-11-01

      Description

      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\"]"
                  ]
              }
          }
      }
      */
      

        Attachments

          Activity

            People

            Assignee:
            timour.katchaounov Timour Katchaounov
            Reporter:
            alex.bevilacqua Alex Bevilacqua
            Participants:
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

              Dates

              Created:
              Updated: