collation is ignored by expr in inside match

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: 8.2.4, 8.2.6, 8.2.12
    • Component/s: None
    • None
    • Query Execution
    • ALL
    • Hide 
      docker run -d --name repro mongo:8.2.12

      # wait for startup, then:
docker exec repro mongosh --quiet test --eval '
  db.k.insertMany([{val: "apple"}, {val: "Apple"}, {val: "APPLE"}]);
  var C = {locale: "en", strength: 1};   // case insensitive

  function vals(f) { return JSON.stringify(db.k.find(f, {_id: 0, val: 1}).collation(C).toArray().map(d => d.val)); }

  print("expr in        : " + vals({$expr: {$in: ["$val", ["Apple"]]}}));
  print("plain in       : " + vals({val: {$in: ["Apple"]}}));
  print("expr eq        : " + vals({$expr: {$eq: ["$val", "Apple"]}}));
  print("countDocuments : " + db.k.countDocuments({$expr: {$in: ["$val", ["Apple"]]}}, {collation: C}));
'

      expr in        : ["Apple"]
plain in       : ["apple","Apple","APPLE"]
expr eq        : ["apple","Apple","APPLE"]
countDocuments : 3
      Show
      docker run -d --name repro mongo:8.2.12 # wait for startup, then: docker exec repro mongosh --quiet test --eval ' db.k.insertMany([{val: "apple"}, {val: "Apple"}, {val: "APPLE"}]); var C = {locale: "en", strength: 1}; // case insensitive function vals(f) { return JSON.stringify(db.k.find(f, {_id: 0, val: 1}).collation(C).toArray().map(d => d.val)); } print("expr in : " + vals({$expr: {$in: ["$val", ["Apple"]]}})); print("plain in : " + vals({val: {$in: ["Apple"]}})); print("expr eq : " + vals({$expr: {$eq: ["$val", "Apple"]}})); print("countDocuments : " + db.k.countDocuments({$expr: {$in: ["$val", ["Apple"]]}}, {collation: C})); ' expr in : ["Apple"] plain in : ["apple","Apple","APPLE"] expr eq : ["apple","Apple","APPLE"] countDocuments : 3
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      A query using $expr with $in compares strings exactly, ignoring any collation supplied with the query. A case insensitive collation therefore misses documents that differ only in case, and the query still succeeds.

      Substituting $eq for $in in the same $expr gives the correct result, as does the ordinary $in form outside $expr, so neither the collation nor $expr is at fault on its own.

      An index on the field makes no difference. A collection default collation behaves the same as a collation passed with the query.

      7.0.31 applies the collation correctly here, so this changed somewhere in 8.x.

      Nothing fails, which is what makes it easy to miss. The query succeeds and the documents it returns look reasonable, so an application would quietly see fewer results than it asked for.

      Expected

      Every document matching under the collation, which is all three in the reproduction steps.

      Actual

      Only the document matching exact case, with no error and nothing logged.

            Assignee:
            Projjal Chanda
            Reporter:
            Daniel Frankcom (EXT)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated: