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

Find queries with SORT_MERGE incorrectly sort the results when the collation is specified

XMLWordPrintableJSON

    • Fully Compatible
    • ALL
    • v4.4, v4.2, v4.0, v3.6
    • Hide 
      > db.c.createIndex({a : 1, b : 1}, {collation: {locale:"en", strength:2}})
 
> db.c.insertMany([{a: "1", b: "a"}, {a: "1", b: "c"}, {a: "2", b: "b"}, {a: "2", b: "d"}])

> db.c.find({a: {$in: ["1", "2"]}}).collation({locale:"en", strength:2}).sort({b:1})
{ "_id" : ObjectId("5f6c67a5697671c039a17929"), "a" : "1", "b" : "a" }
{ "_id" : ObjectId("5f6c67a5697671c039a1792a"), "a" : "1", "b" : "c" }
{ "_id" : ObjectId("5f6c67a5697671c039a1792b"), "a" : "2", "b" : "b" }
{ "_id" : ObjectId("5f6c67a5697671c039a1792c"), "a" : "2", "b" : "d" }
      Show
      > db.c.createIndex({a : 1, b : 1}, {collation: {locale: "en" , strength:2}}) > db.c.insertMany([{a: "1" , b: "a" }, {a: "1" , b: "c" }, {a: "2" , b: "b" }, {a: "2" , b: "d" }]) > db.c.find({a: {$in: [ "1" , "2" ]}}).collation({locale: "en" , strength:2}).sort({b:1}) { "_id" : ObjectId( "5f6c67a5697671c039a17929" ), "a" : "1" , "b" : "a" } { "_id" : ObjectId( "5f6c67a5697671c039a1792a" ), "a" : "1" , "b" : "c" } { "_id" : ObjectId( "5f6c67a5697671c039a1792b" ), "a" : "2" , "b" : "b" } { "_id" : ObjectId( "5f6c67a5697671c039a1792c" ), "a" : "2" , "b" : "d" }
    • Query 2020-10-05, Query 2020-10-19

      Issue Status as of Sep 30, 2020

      ISSUE DESCRIPTION AND IMPACT

      Certain queries involving a collated index and sorting on the collated fields can return incorrectly sorted results. This impacts $or and $in operations where each clause is indexed in a way that does not require a blocking sort. The set of results is accurate, but the documents are not ordered correctly.

      The bug is that the SORT_MERGE query plan stage incorrectly interprets the collated index keys.

      DIAGNOSIS AND AFFECTED VERSIONS

      All versions are affected. You can confirm a collation query is affected by using explain(). If a SORT_MERGE stage is in the query plan, and at least one child of the SORT_MERGE stage is an IXSCAN without a FETCH, then the query can return incorrectly sorted results.

      REMEDIATION AND WORKAROUNDS

      This issue will be corrected in the 4.4.2, 4.2.11, 4.0.21, and 3.6.21 versions of MongoDB. Until these versions are released, the main workaround is to sort results on the client side until a fix version is released.

      original description

      Given a compound index with a non-simple collation, a multi-point query on a prefix fields together with a sort on a prefix of suffix fields produces incorrect sort results when a collation is specified that matches the collation of the index and a sort merge (SORT_MERGE) is selected in the query execution plan. For example, if a collection has a compound index with a non-simple collation on fields a, b, c, d, then a find command with a matching collation specified that has a multi-point query on a and b fields and is sorted on field c will not produce correctly sorted result.

            Assignee:
            mindaugas.malinauskas@mongodb.com Mindaugas Malinauskas
            Reporter:
            mindaugas.malinauskas@mongodb.com Mindaugas Malinauskas
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated:
              Resolved: