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

Regex search on an indexed field, gives unexpected behavior after using collation

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Works as Designed
    • Icon: Major - P3 Major - P3
    • None
    • 3.4.2
    • Index Maintenance
    • None
    • ALL

    Description

      I was expecting the following result for below scenarios, but it's not?
      "totalKeysExamined" : 1,
      "totalDocsExamined" : 1,

      // Without collation
      db.createCollection("test");
      db.test.createIndex({name: 1});
      db.test.insert({name: "Anders"});
      db.test.insert({name: "Bo"});
      db.test.insert({name: "Ole"});
      db.test.insert({name: "Peter"});
      db.test.insert({name: "Hans"});
      db.test.find({"name": {$regex: /^and.*/i}}).explain("executionStats");
      

      Result:
      "totalKeysExamined" : 5,
      "totalDocsExamined" : 1,

      // Collection with collation
      db.createCollection("testWithCollation", {collation: {locale: "en", strength: 2}});
      db.testWithCollation.createIndex({name: 1});
      db.testWithCollation.insert({name: "Anders"});
      db.testWithCollation.insert({name: "Bo"});
      db.testWithCollation.insert({name: "Ole"});
      db.testWithCollation.insert({name: "Peter"});
      db.testWithCollation.insert({name: "Hans"});
      db.testWithCollation.find({"name": {$regex: /^and.*/i}}).explain("executionStats");
      

      Result:
      "totalKeysExamined" : 5,
      "totalDocsExamined" : 5,

      // Collection with an collation index field
      db.createCollection("testWithCollationIndex");
      db.testWithCollationIndex.createIndex({name: 1}, {collation: {locale: 'en', strength: 2}});
      db.testWithCollationIndex.insert({name: "Anders"});
      db.testWithCollationIndex.insert({name: "Bo"});
      db.testWithCollationIndex.insert({name: "Ole"});
      db.testWithCollationIndex.insert({name: "Peter"});
      db.testWithCollationIndex.insert({name: "Hans"});
      db.testWithCollationIndex.find({"name": {$regex: /^and.*/i}}).collation({locale: 'en', strength: 2}).explain("executionStats");
      

      Result:
      "totalKeysExamined" : 5,
      "totalDocsExamined" : 5,

      Attachments

        Activity

          People

            mark.agarunov Mark Agarunov
            jesperbendtsen83@gmail.com Jesper Erik Bendtsen
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: