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

Large number of $or clauses can create profiling entry exceeding max BSON size, causing the query to fail when it should not

    • Type: Icon: Bug Bug
    • Resolution: Fixed
    • Priority: Icon: Major - P3 Major - P3
    • 4.9.0, 4.4.5, 4.0.24, 4.2.14
    • Affects Version/s: None
    • Component/s: Querying
    • None
    • Fully Compatible
    • v4.4, v4.2, v4.0
    • Query Execution 2021-03-22

      Each clause in a $or expression can use its own index so when a query has a lot $or and each $or has a lot of clause it can create a very large profiler entry when profiler is enabled.

      {
              "op" : "query",
              "ns" : "database.collection",
              "command" : {
                      "find" : "collection",
                      "filter" : {
                              "$or" : [
                                      {
                                              "$and" : [
                                                      {
                                                              "$or" : [
                                                                      {
                                                                              "field1" : "1163"
                                                                      },
                                                                      {
                                                                              "field2" : "1163"
                                                                      }
                                                              ]
                                                      },
                                                      {
                                                              "field3" : "G3"
                                                      },
                                                      {
                                                              "field4" : "GYN"
                                                      }
                                              ]
                                      },
                                      {
                                              "$and" : [
                                                      {
                                                              "$or" : [
                                                                      {
                                                                              "field1" : "3402"
                                                                      },
                                                                      {
                                                                              "field2" : "3402"
                                                                      }
                                                              ]
                                                      },
                                                      {
                                                              "field3" : "IB"
                                                      },
                                                      {
                                                              "field4" : "MAD"
                                                      }
                                              ]
                                      }
                              ]
                      },
                      "lsid" : {
                              "id" : UUID("d7f6df2a-1941-4463-9e85-91e050c35f38")
                      },
                      "$clusterTime" : {
                              "clusterTime" : Timestamp(1614021369, 1),
                              "signature" : {
                                      "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                                      "keyId" : NumberLong(0)
                              }
                      },
                      "$db" : "database"
              },
              "keysExamined" : 25,
              "docsExamined" : 8,
              "cursorExhausted" : true,
              "numYield" : 0,
              "nreturned" : 8,
              "locks" : {
                      "Global" : {
                              "acquireCount" : {
                                      "r" : NumberLong(1)
                              }
                      },
                      "Database" : {
                              "acquireCount" : {
                                      "r" : NumberLong(1)
                              }
                      },
                      "Collection" : {
                              "acquireCount" : {
                                      "r" : NumberLong(1)
                              }
                      }
              },
              "responseLength" : 53607,
              "protocol" : "op_msg",
              "millis" : 0,
              "planSummary" : "IXSCAN { field3: 1, field1: 1, field5: 1, field6: 1 }, IXSCAN { field2: 1, field5: 1, field7: 1, field3: 1, field4: 1 }, IXSCAN { field3: 1, field1: 1, field5: 1, field6: 1 }, IXSCAN { field2: 1, field5: 1, field7: 1, field3: 1, field4: 1 }",
              "execStats" : {
                      "stage" : "SUBPLAN",
                      "nReturned" : 8,
                      "executionTimeMillisEstimate" : 0,
                      "works" : 27,
                      "advanced" : 8,
                      "needTime" : 18,
                      "needYield" : 0,
                      "saveState" : 0,
                      "restoreState" : 0,
                      "isEOF" : 1,
                      "invalidates" : 0,
                      "inputStage" : {
                              "stage" : "OR",
                              "nReturned" : 8,
                              "executionTimeMillisEstimate" : 0,
                              "works" : 27,
                              "advanced" : 8,
                              "needTime" : 18,
                              "needYield" : 0,
                              "saveState" : 0,
                              "restoreState" : 0,
                              "isEOF" : 1,
                              "invalidates" : 0,
                              "dupsTested" : 8,
                              "dupsDropped" : 0,
                              "recordIdsForgotten" : 0,
                              "inputStages" : [
                                      {
                                              "stage" : "FETCH",
                                              "filter" : {
                                                      "field4" : {
                                                              "$eq" : "GYN"
                                                      }
                                              },
                                              "nReturned" : 3,
                                              "executionTimeMillisEstimate" : 0,
                                              "works" : 14,
                                              "advanced" : 3,
                                              "needTime" : 10,
                                              "needYield" : 0,
                                              "saveState" : 0,
                                              "restoreState" : 0,
                                              "isEOF" : 1,
                                              "invalidates" : 0,
                                              "docsExamined" : 3,
                                              "alreadyHasObj" : 0,
                                              "inputStage" : {
                                                      "stage" : "OR",
                                                      "nReturned" : 3,
                                                      "executionTimeMillisEstimate" : 0,
                                                      "works" : 14,
                                                      "advanced" : 3,
                                                      "needTime" : 10,
                                                      "needYield" : 0,
                                                      "saveState" : 0,
                                                      "restoreState" : 0,
                                                      "isEOF" : 1,
                                                      "invalidates" : 0,
                                                      "dupsTested" : 6,
                                                      "dupsDropped" : 3,
                                                      "recordIdsForgotten" : 0,
                                                      "inputStages" : [
                                                              {
                                                                      "stage" : "IXSCAN",
                                                                      "nReturned" : 3,
                                                                      "executionTimeMillisEstimate" : 0,
                                                                      "works" : 4,
                                                                      "advanced" : 3,
                                                                      "needTime" : 0,
                                                                      "needYield" : 0,
                                                                      "saveState" : 0,
                                                                      "restoreState" : 0,
                                                                      "isEOF" : 1,
                                                                      "invalidates" : 0,
                                                                      "keyPattern" : {
                                                                              "field3" : 1,
                                                                              "field1" : 1,
                                                                              "field5" : 1,
                                                                              "field6" : 1
                                                                      },
                                                                      "indexName" : "index_1",
                                                                      "isMultiKey" : false,
                                                                      "multiKeyPaths" : {
                                                                              "field3" : [ ],
                                                                              "field1" : [ ],
                                                                              "field5" : [ ],
                                                                              "field6" : [ ]
                                                                      },
                                                                      "isUnique" : false,
                                                                      "isSparse" : false,
                                                                      "isPartial" : false,
                                                                      "indexVersion" : 2,
                                                                      "direction" : "forward",
                                                                      "indexBounds" : {
                                                                              "field3" : [
                                                                                      "[\"G3\", \"G3\"]"
                                                                              ],
                                                                              "field1" : [
                                                                                      "[\"1163\", \"1163\"]"
                                                                              ],
                                                                              "field5" : [
                                                                                      "[MinKey, MaxKey]"
                                                                              ],
                                                                              "field6" : [
                                                                                      "[MinKey, MaxKey]"
                                                                              ]
                                                                      },
                                                                      "keysExamined" : 3,
                                                                      "seeks" : 1,
                                                                      "dupsTested" : 0,
                                                                      "dupsDropped" : 0,
                                                                      "seenInvalidated" : 0
                                                              },
                                                              {
                                                                      "stage" : "IXSCAN",
                                                                      "nReturned" : 3,
                                                                      "executionTimeMillisEstimate" : 0,
                                                                      "works" : 10,
                                                                      "advanced" : 3,
                                                                      "needTime" : 6,
                                                                      "needYield" : 0,
                                                                      "saveState" : 0,
                                                                      "restoreState" : 0,
                                                                      "isEOF" : 1,
                                                                      "invalidates" : 0,
                                                                      "keyPattern" : {
                                                                              "field2" : 1,
                                                                              "field5" : 1,
                                                                              "field7" : 1,
                                                                              "field3" : 1,
                                                                              "field4" : 1
                                                                      },
                                                                      "indexName" : "index_2",
                                                                      "isMultiKey" : false,
                                                                      "multiKeyPaths" : {
                                                                              "field2" : [ ],
                                                                              "field5" : [ ],
                                                                              "field7" : [ ],
                                                                              "field3" : [ ],
                                                                              "field4" : [ ]
                                                                      },
                                                                      "isUnique" : false,
                                                                      "isSparse" : false,
                                                                      "isPartial" : false,
                                                                      "indexVersion" : 2,
                                                                      "direction" : "forward",
                                                                      "indexBounds" : {
                                                                              "field2" : [
                                                                                      "[\"1163\", \"1163\"]"
                                                                              ],
                                                                              "field5" : [
                                                                                      "[MinKey, MaxKey]"
                                                                              ],
                                                                              "field7" : [
                                                                                      "[MinKey, MaxKey]"
                                                                              ],
                                                                              "field3" : [
                                                                                      "[\"G3\", \"G3\"]"
                                                                              ],
                                                                              "field4" : [
                                                                                      "[\"GYN\", \"GYN\"]"
                                                                              ]
                                                                      },
                                                                      "keysExamined" : 10,
                                                                      "seeks" : 7,
                                                                      "dupsTested" : 0,
                                                                      "dupsDropped" : 0,
                                                                      "seenInvalidated" : 0
                                                              }
                                                      ]
                                              }
                                      },
                                      {
                                              "stage" : "FETCH",
                                              "filter" : {
                                                      "field4" : {
                                                              "$eq" : "MAD"
                                                      }
                                              },
                                              "nReturned" : 5,
                                              "executionTimeMillisEstimate" : 0,
                                              "works" : 13,
                                              "advanced" : 5,
                                              "needTime" : 7,
                                              "needYield" : 0,
                                              "saveState" : 0,
                                              "restoreState" : 0,
                                              "isEOF" : 1,
                                              "invalidates" : 0,
                                              "docsExamined" : 5,
                                              "alreadyHasObj" : 0,
                                              "inputStage" : {
                                                      "stage" : "OR",
                                                      "nReturned" : 5,
                                                      "executionTimeMillisEstimate" : 0,
                                                      "works" : 13,
                                                      "advanced" : 5,
                                                      "needTime" : 7,
                                                      "needYield" : 0,
                                                      "saveState" : 0,
                                                      "restoreState" : 0,
                                                      "isEOF" : 1,
                                                      "invalidates" : 0,
                                                      "dupsTested" : 10,
                                                      "dupsDropped" : 5,
                                                      "recordIdsForgotten" : 0,
                                                      "inputStages" : [
                                                              {
                                                                      "stage" : "IXSCAN",
                                                                      "nReturned" : 5,
                                                                      "executionTimeMillisEstimate" : 0,
                                                                      "works" : 6,
                                                                      "advanced" : 5,
                                                                      "needTime" : 0,
                                                                      "needYield" : 0,
                                                                      "saveState" : 0,
                                                                      "restoreState" : 0,
                                                                      "isEOF" : 1,
                                                                      "invalidates" : 0,
                                                                      "keyPattern" : {
                                                                              "field3" : 1,
                                                                              "field1" : 1,
                                                                              "field5" : 1,
                                                                              "field6" : 1
                                                                      },
                                                                      "indexName" : "index_1",
                                                                      "isMultiKey" : false,
                                                                      "multiKeyPaths" : {
                                                                              "field3" : [ ],
                                                                              "field1" : [ ],
                                                                              "field5" : [ ],
                                                                              "field6" : [ ]
                                                                      },
                                                                      "isUnique" : false,
                                                                      "isSparse" : false,
                                                                      "isPartial" : false,
                                                                      "indexVersion" : 2,
                                                                      "direction" : "forward",
                                                                      "indexBounds" : {
                                                                              "field3" : [
                                                                                      "[\"IB\", \"IB\"]"
                                                                              ],
                                                                              "field1" : [
                                                                                      "[\"3402\", \"3402\"]"
                                                                              ],
                                                                              "field5" : [
                                                                                      "[MinKey, MaxKey]"
                                                                              ],
                                                                              "field6" : [
                                                                                      "[MinKey, MaxKey]"
                                                                              ]
                                                                      },
                                                                      "keysExamined" : 5,
                                                                      "seeks" : 1,
                                                                      "dupsTested" : 0,
                                                                      "dupsDropped" : 0,
                                                                      "seenInvalidated" : 0
                                                              },
                                                              {
                                                                      "stage" : "IXSCAN",
                                                                      "nReturned" : 5,
                                                                      "executionTimeMillisEstimate" : 0,
                                                                      "works" : 7,
                                                                      "advanced" : 5,
                                                                      "needTime" : 1,
                                                                      "needYield" : 0,
                                                                      "saveState" : 0,
                                                                      "restoreState" : 0,
                                                                      "isEOF" : 1,
                                                                      "invalidates" : 0,
                                                                      "keyPattern" : {
                                                                              "field2" : 1,
                                                                              "field5" : 1,
                                                                              "field7" : 1,
                                                                              "field3" : 1,
                                                                              "field4" : 1
                                                                      },
                                                                      "indexName" : "index_2",
                                                                      "isMultiKey" : false,
                                                                      "multiKeyPaths" : {
                                                                              "field2" : [ ],
                                                                              "field5" : [ ],
                                                                              "field7" : [ ],
                                                                              "field3" : [ ],
                                                                              "field4" : [ ]
                                                                      },
                                                                      "isUnique" : false,
                                                                      "isSparse" : false,
                                                                      "isPartial" : false,
                                                                      "indexVersion" : 2,
                                                                      "direction" : "forward",
                                                                      "indexBounds" : {
                                                                              "field2" : [
                                                                                      "[\"3402\", \"3402\"]"
                                                                              ],
                                                                              "field5" : [
                                                                                      "[MinKey, MaxKey]"
                                                                              ],
                                                                              "field7" : [
                                                                                      "[MinKey, MaxKey]"
                                                                              ],
                                                                              "field3" : [
                                                                                      "[\"IB\", \"IB\"]"
                                                                              ],
                                                                              "field4" : [
                                                                                      "[\"MAD\", \"MAD\"]"
                                                                              ]
                                                                      },
                                                                      "keysExamined" : 7,
                                                                      "seeks" : 2,
                                                                      "dupsTested" : 0,
                                                                      "dupsDropped" : 0,
                                                                      "seenInvalidated" : 0
                                                              }
                                                      ]
                                              }
                                      }
                              ]
                      }
              },
              "ts" : ISODate("2021-02-22T19:16:17.280Z"),
              "client" : "127.0.0.1",
              "appName" : "MongoDB Shell",
              "allUsers" : [ ],
              "user" : ""
      }
      

      When the profiler entry is too large it can trigger the "BSONObjectTooLarge" error and causing such a query fail to execute.

      Error: error: {
        "operationTime" : Timestamp(1614018738, 1),
        "ok" : 0,
        "errmsg" : "BSONObj size: 20642198 (0x13AF996) is invalid. Size must be between 0 and 16793600(16MB) First element: stage: \"OR\"",
        "code" : 10334,
        "codeName" : "BSONObjectTooLarge",
        "$clusterTime" : {
          "clusterTime" : Timestamp(1614018738, 1),
          "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
          }
        }
      }
      

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            jennifer.huang@mongodb.com Jennifer Huang (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: