[SERVER-40546] Min/Max with collation can return incorrect results Created: 09/Apr/19  Updated: 29/Oct/23  Resolved: 07/Jun/19

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.6.12, 4.0.8, 4.1.9
Fix Version/s: 4.3.1

Type: Bug Priority: Major - P3
Reporter: Ian Boros Assignee: Mikhail Ivkov (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v4.2, v4.0
Sprint: Query 2019-06-17
Participants:

 Description   

In QueryPlanner::plan() we decide whether an index is compatible with min/max by only looking at the value provided to min() or the value provided to max() (but not both). If a non-empty value is provided for min(), and the value provided to max() is collatable, the index chosen may lead to wrong results.

To reproduce, run this:

min_max_collation.js

(function() {
    db.c.insert({a: "Hello"});
    db.c.insert({a: "hello"});
 
    const caseInsensitive = {locale: "en", strength: 2};
 
    // With no collation, 'hello' > "Hello1" > "Hello".
    // Under a case insensitive collation, 'hello' = 'Hello' < 'Hello1'
 
    const res = db.c.find({a: {$lt: "Hello1"}}, {_id: 0}).collation(caseInsensitive).toArray();
    assert.sameMembers(res, [{a: "Hello"}, {a: "hello"}]);
 
    // Now do a similar query with min/max, using an index that has the case insensitive collation.
    db.c.createIndex({a: 1}, {collation: caseInsensitive});
    const minMaxRes = db.c.find({}, {_id: 0})
                          .min({a: MinKey})
                          .max({a: "Hello1"})
                          .hint({a: 1})
                          .collation(caseInsensitive)
                          .toArray();
    assert.sameMembers(minMaxRes, [{a: "Hello"}, {a: "hello"}]);
 
    // Now run the same min/max query, attempting to use an index that has no collation.
    db.c.dropIndexes();
    db.c.createIndex({a: 1});
    const minMaxNoCollationRes = db.c.find({}, {_id: 0})
                                     .min({a: MinKey})
                                     .max({a: "Hello1"})
                                     .hint({a: 1})
                                     .collation(caseInsensitive)
          .toArray();
 
    // The planner will use the {a: 1} index, where 'hello' comes _after_ 'Hello1' (since there's
    // no collation). This means that we will only get the result {a: "Hello"}.
    assert.sameMembers(minMaxNoCollationRes, [{a: "Hello"}, {a: "hello"}]); // Fails!
})();

 resmoke.py --suites=core min_max_collation.js 

Note that while I am changing min/max in SERVER-39567, I do not plan on changing this behavior as part of that work.

 

This is also an issue on 4.0 and 3.6 (and probably 3.4). To reproduce on older versions, you may have to omit a hint.



 Comments   
Comment by Githook User [ 29/Oct/19 ]

Author:

{'name': 'Ian Boros', 'username': 'puppyofkosh', 'email': 'ian.boros@mongodb.com'}

Message: SERVER-40546 Fix min/max boundary checking
Branch: v4.2
https://github.com/mongodb/mongo/commit/7b95afe61b71396af1907a55f3db7bd99d337849

Comment by Githook User [ 07/Jun/19 ]

Author:

{'name': 'Misha Ivkov', 'email': 'misha.ivkov@10gen.com'}

Message: SERVER-40546 max/min boundary checking on both
Branch: master
https://github.com/mongodb/mongo/commit/e67db1f1c523309be615ebf1ec7f12b944b21bd9

Generated at Thu Feb 08 04:55:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.