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

$expr does not use multi-key index

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major - P3
    • Resolution: Works as Designed
    • 3.6.3, 4.0.1
    • None
    • Querying
    • None
    • ALL
    • Hide

      Start with empty collection and the index:

      > db.comment.drop()
      true
      > db.comment.createIndex({ 'links.id': 1 })
      {
              "createdCollectionAutomatically" : true,
              "numIndexesBefore" : 1,
              "numIndexesAfter" : 2,
              "ok" : 1
      }
      

      Initially, the query strategy indicates it will use the correct index

      > db.comment.find({$expr:{$eq:['$links.id', 'some-string']}}).explain()
      {
              "queryPlanner" : {
                      "plannerVersion" : 1,
                      "namespace" : "test.comment",
                      "indexFilterSet" : false,
                      "parsedQuery" : {
                              "$and" : [
                                      {
                                              "$expr" : {
                                                      "$eq" : [
                                                              "$links.id",
                                                              {
                                                                      "$const" : "some-string"
                                                              }
                                                      ]
                                              }
                                      },
                                      {
                                              "links.id" : {
                                                      "$_internalExprEq" : "some-string"
                                              }
                                      }
                              ]
                      },
                      "winningPlan" : {
                              "stage" : "FETCH",
                              "filter" : {
                                      "$expr" : {
                                              "$eq" : [
                                                      "$links.id",
                                                      {
                                                              "$const" : "some-string"
                                                      }
                                              ]
                                      }
                              },
                              "inputStage" : {
                                      "stage" : "IXSCAN",
                                      "keyPattern" : {
                                              "links.id" : 1
                                      },
                                      "indexName" : "links.id_1",
                                      "isMultiKey" : false,
                                      "multiKeyPaths" : {
                                              "links.id" : [ ]
                                      },
                                      "isUnique" : false,
                                      "isSparse" : false,
                                      "isPartial" : false,
                                      "indexVersion" : 2,
                                      "direction" : "forward",
                                      "indexBounds" : {
                                              "links.id" : [
                                                      "[\"some-string\", \"some-string\"]"
                                              ]
                                      }
                              }
                      },
                      "rejectedPlans" : [ ]
              },
              "serverInfo" : {
                      "host" : "caa1852655c1",
                      "port" : 27017,
                      "version" : "3.6.3",
                      "gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5"
              },
              "ok" : 1
      }
      

      But when an element is inserted, it "discovers" that its a multi-key index

       

      > db.comment.insert({ links: [{ id: 'some-string', type: 'test' }] })
      WriteResult({ "nInserted" : 1 })
      

      and the query no longer uses the correct index

      > db.comment.find({$expr:{$eq:['$links.id', 'some-string']}}).explain()
      {
              "queryPlanner" : {
                      "plannerVersion" : 1,
                      "namespace" : "test.comment",
                      "indexFilterSet" : false,
                      "parsedQuery" : {
                              "$and" : [
                                      {
                                              "$expr" : {
                                                      "$eq" : [
                                                              "$links.id",
                                                              {
                                                                      "$const" : "some-string"
                                                              }
                                                      ]
                                              }
                                      },
                                      {
                                              "links.id" : {
                                                      "$_internalExprEq" : "some-string"
                                              }
                                      }
                              ]
                      },
                      "winningPlan" : {
                              "stage" : "COLLSCAN",
                              "filter" : {
                                      "$and" : [
                                              {
                                                      "$expr" : {
                                                              "$eq" : [
                                                                      "$links.id",
                                                                      {
                                                                              "$const" : "some-string"
                                                                      }
                                                              ]
                                                      }
                                              },
                                              {
                                                      "links.id" : {
                                                              "$_internalExprEq" : "some-string"
                                                      }
                                              }
                                      ]
                              },
                              "direction" : "forward"
                      },
                      "rejectedPlans" : [ ]
              },
              "serverInfo" : {
                      "host" : "caa1852655c1",
                      "port" : 27017,
                      "version" : "3.6.3",
                      "gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5"
              },
              "ok" : 1
      }
      

       

      Show
      Start with empty collection and the index: > db.comment.drop() true > db.comment.createIndex({ 'links.id' : 1 }) { "createdCollectionAutomatically" : true , "numIndexesBefore" : 1 , "numIndexesAfter" : 2 , "ok" : 1 } Initially, the query strategy indicates it will use the correct index > db.comment.find({$expr:{$eq:[ '$links.id' , 'some-string' ]}}).explain() { "queryPlanner" : { "plannerVersion" : 1 , "namespace" : "test.comment" , "indexFilterSet" : false , "parsedQuery" : { "$and" : [ { "$expr" : { "$eq" : [ "$links.id" , { "$const" : "some-string" } ] } }, { "links.id" : { "$_internalExprEq" : "some-string" } } ] }, "winningPlan" : { "stage" : "FETCH" , "filter" : { "$expr" : { "$eq" : [ "$links.id" , { "$const" : "some-string" } ] } }, "inputStage" : { "stage" : "IXSCAN" , "keyPattern" : { "links.id" : 1 }, "indexName" : "links.id_1" , "isMultiKey" : false , "multiKeyPaths" : { "links.id" : [ ] }, "isUnique" : false , "isSparse" : false , "isPartial" : false , "indexVersion" : 2 , "direction" : "forward" , "indexBounds" : { "links.id" : [ "[\"some-string\", \"some-string\"]" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "caa1852655c1" , "port" : 27017 , "version" : "3.6.3" , "gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5" }, "ok" : 1 } But when an element is inserted, it "discovers" that its a multi-key index   > db.comment.insert({ links: [{ id: 'some-string' , type: 'test' }] }) WriteResult({ "nInserted" : 1 }) and the query no longer uses the correct index > db.comment.find({$expr:{$eq:[ '$links.id' , 'some-string' ]}}).explain() { "queryPlanner" : { "plannerVersion" : 1 , "namespace" : "test.comment" , "indexFilterSet" : false , "parsedQuery" : { "$and" : [ { "$expr" : { "$eq" : [ "$links.id" , { "$const" : "some-string" } ] } }, { "links.id" : { "$_internalExprEq" : "some-string" } } ] }, "winningPlan" : { "stage" : "COLLSCAN" , "filter" : { "$and" : [ { "$expr" : { "$eq" : [ "$links.id" , { "$const" : "some-string" } ] } }, { "links.id" : { "$_internalExprEq" : "some-string" } } ] }, "direction" : "forward" }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "caa1852655c1" , "port" : 27017 , "version" : "3.6.3" , "gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5" }, "ok" : 1 }  

    Description

      An $expr expression in a query will not use a multi-key index. Ticket SERVER-31760 indicates this was fixed in 3.6.3 but still fails to work if the index is a multi-key index. This manifests only after an element is inserted that makes the index a multi-key index. 

      A normal query will use the correct index, but, as with the motivation for SERVER-31760, it is desired to work within a $lookup pipeline where $expr would be required. 

      The problem exists in 3.6.3 as well as 4.0.1

      Attachments

        Issue Links

          Activity

            People

              nick.brewer Nick Brewer
              twilson Trevor Wilson
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: