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

Wrong order when using index with collation

    • Type: Icon: Bug Bug
    • Resolution: Fixed
    • Priority: Icon: Major - P3 Major - P3
    • 8.1.0-rc0, 8.0.0-rc8
    • Affects Version/s: 7.3.2, 8.0.0-rc6
    • Component/s: None
    • None
    • Query Execution
    • Fully Compatible
    • ALL
    • v8.0, v7.3
    • Hide

      In SERVER-63132, a change was made that would allow a query to use an index with collation to retrieve records without the need to insert a fetch stage (code). This does not work as expected in all cases. From local testing I believe that this will not work on any field that is not simple numeric value. The error seems to come from the fact that in non-numeric fields, when there is a non-simple collation, the sortKey is not the real value of the field so the final results are not in the correct order. I believe that the current check should become more restrictive to allow it only in cases of numerical values.

       
      Example

      db.col1.drop();
      db.createCollection("col1", {collation: {locale: "en", strength: 1}});
      db.col1.insertMany([{ x: 1, y: "str1" , z: ["str1", "str2"] }, { x: 1, y: "str2" , z: ["str2","str3"] }])
      db.col1.createIndex({ x:1 ,y:1 ,z:1 })
      

       
      Query

      db.col1.find({ x: 1 }, {z:1}).sort({z: -1})

       
      Expected Results (on a version of mongoDB that does not contain the change from SERVER-63132)

      [
        { _id: ObjectId("6659a93088a2e02cb99eaaf4"), z: [ 'str2', 'str3' ] },
        { _id: ObjectId("6659a93088a2e02cb99eaaf3"), z: [ 'str1', 'str2' ] }
      ] 

       
      Returned Results

      [
        { _id: ObjectId("6659a93088a2e02cb99eaaf3"), z: [ 'str1', 'str2' ] },
        { _id: ObjectId("6659a93088a2e02cb99eaaf4"), z: [ 'str2', 'str3' ] }
      ] 

       

      Show
      In SERVER-63132 , a change was made that would allow a query to use an index with collation to retrieve records without the need to insert a fetch stage ( code ). This does not work as expected in all cases. From local testing I believe that this will not work on any field that is not simple numeric value. The error seems to come from the fact that in non-numeric fields, when there is a non-simple collation, the sortKey is not the real value of the field so the final results are not in the correct order. I believe that the current check should become more restrictive to allow it only in cases of numerical values.   Example db.col1.drop(); db.createCollection( "col1" , {collation: {locale: "en" , strength: 1}}); db.col1.insertMany([{ x: 1, y: "str1" , z: [ "str1" , "str2" ] }, { x: 1, y: "str2" , z: [ "str2" , "str3" ] }]) db.col1.createIndex({ x:1 ,y:1 ,z:1 })   Query db.col1.find({ x: 1 }, {z:1}).sort({z: -1})   Expected Results (on a version of mongoDB that does not contain the change from SERVER-63132 ) [   { _id: ObjectId( "6659a93088a2e02cb99eaaf4" ), z: [ 'str2' , 'str3' ] },   { _id: ObjectId( "6659a93088a2e02cb99eaaf3" ), z: [ 'str1' , 'str2' ] } ]   Returned Results [   { _id: ObjectId( "6659a93088a2e02cb99eaaf3" ), z: [ 'str1' , 'str2' ] },   { _id: ObjectId( "6659a93088a2e02cb99eaaf4" ), z: [ 'str2' , 'str3' ] } ]  

          Assignee:
          foteini.alvanaki@mongodb.com Foteini Alvanaki
          Reporter:
          foteini.alvanaki@mongodb.com Foteini Alvanaki
          Votes:
          0 Vote for this issue
          Watchers:
          8 Start watching this issue

            Created:
            Updated:
            Resolved: