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

An index with non-simple collation should not be considered for predicates with a regex value

    • Type: Icon: Task Task
    • Resolution: Fixed
    • Priority: Icon: Minor - P4 Minor - P4
    • 8.0.0-rc0
    • Affects Version/s: 6.0.0-rc0, 5.3.1
    • Component/s: Query Planning
    • None
    • Query Optimization
    • Fully Compatible
    • QO 2024-02-05

      Neither the existing pcre library nor the new pcre2 library support ICU collation. Therefore predicates that contain a regex value/s should not be eligible to use indexes with non-simple collations for index scans, as the index will not be selective and all strings in the index will be used:

      For example, notice this explain output and the final index bounds used:

      replSet:PRIMARY> db.coll.createIndex({x: 1}, {collation: {locale: "en", strength: 1}})
      replSet:PRIMARY> db.coll.explain().find({x: {$regex: /^foo$/, $options: "i"}})
      {
        "explainVersion" : "2",
        "queryPlanner" : {
          "namespace" : "newer.coll",
          "indexFilterSet" : false,
          "parsedQuery" : {
            "x" : {
              "$regex" : "^foo$",
              "$options" : "i"
            }
          },
          ...
          "winningPlan" : {
            "queryPlan" : {
              "stage" : "FETCH",
              "planNodeId" : 2,
              "filter" : {
                "x" : {
                  "$regex" : "^foo$",
                  "$options" : "i"
                }
              },
              "inputStage" : {
                "stage" : "IXSCAN",
                "planNodeId" : 1,
                "keyPattern" : {
                  "x" : 1
                },
                "indexName" : "x_1",
                "collation" : {
                  "locale" : "en",
                  "caseLevel" : false,
                  "caseFirst" : "off",
                  "strength" : 1,
                  "numericOrdering" : false,
                  "alternate" : "non-ignorable",
                  "maxVariable" : "punct",
                  "normalization" : false,
                  "backwards" : false,
                  "version" : "57.1"
                },
                ...
                "indexBounds" : {
                  "x" : [
                    "[CollationKey(0x), {})",
                    "[/^foo$/i, /^foo$/i]"
                  ]
                }
              ...
      

        1. image-2024-01-24-12-44-58-896.png
          image-2024-01-24-12-44-58-896.png
          39 kB
        2. image-2024-01-24-12-44-34-026.png
          image-2024-01-24-12-44-34-026.png
          40 kB
        3. image-2024-01-24-12-44-23-817.png
          image-2024-01-24-12-44-23-817.png
          41 kB
        4. image-2024-01-24-12-44-15-398.png
          image-2024-01-24-12-44-15-398.png
          36 kB
        5. bench.js
          3 kB

            Assignee:
            c.alonso@mongodb.com Carlos Alonso Pérez
            Reporter:
            kyle.suarez@mongodb.com Kyle Suarez
            Votes:
            0 Vote for this issue
            Watchers:
            19 Start watching this issue

              Created:
              Updated:
              Resolved: