Major - P3 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.