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.
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' ] }
]
related to
SERVER-91249Allow indexed field to provide sort key when index has non-simple collation