Info

Bernard made a very insightful comment in a code review which lead us to realize this behavior: db.c.drop(); db.c.insert({b: 1}); const query = {a: {$gte: null}}; // Returns the document. {b:1} printjson(db.c.find(query).toArray()); db.c.createIndex({a: 1}, {sparse: true}); // Returns nothing. printjson(db.c.find(query).toArray()); On a query like {a: {$gte: null}}, the planner may choose to use a sparse index on 'a', even though the results should include documents which don't have an 'a' field (and therefore, don't have a key in the sparse index). I plan on fixing this as part of SERVER-36731, but we should probably do a special fix for older branches. [EDIT: We should decide which behavior is correct before changing anything].

Top User Comments

xgen-internal-githook commented on Fri, 2 Aug 2024 08:45:39 +0000: Author: {'name': 'Ruoxin Xu', 'email': 'ruoxin.xu@mongodb.com', 'username': 'RuoxinXu'} Message: SERVER-37164 Prevent a sparse index being used on $gte and $lte queries with null (#25474) GitOrigin-RevId: a0a2864ef6eb3ac83cefa732e379e180d4bcc5a2 Branch: v7.0 https://github.com/mongodb/mongo/commit/9f9ff9911bba8380d70734b76b214e4c4220ba4a xgen-internal-githook commented on Wed, 26 Jun 2024 21:02:25 +0000: Author: {'name': 'Ruoxin Xu', 'email': 'ruoxin.xu@mongodb.com', 'username': 'RuoxinXu'} Message: SERVER-37164 Prevent a sparse index being used on $gte and $lte queries with null (#23912) GitOrigin-RevId: ad52cc929dafa4a0c20ce52d8310ed755619c6c9 Branch: v7.3 https://github.com/mongodb/mongo/commit/ca0eb3cb223299b0d78cafa67d54601c464ed514 xgen-internal-githook commented on Thu, 13 Jun 2024 14:30:11 +0000: Author: {'name': 'Ruoxin Xu', 'email': 'ruoxin.xu@mongodb.com', 'username': 'RuoxinXu'} Message: SERVER-37164 Prevent a sparse index being used on $gte and $lte queries with null (#23085) (#23441) GitOrigin-RevId: bd82692715db071418f428e2ecb591a3963f8117 Branch: v8.0 https://github.com/mongodb/mongo/commit/00967103602d31fde524e483fa33d30180dba4ef xgen-internal-githook commented on Fri, 7 Jun 2024 10:59:31 +0000: Author: {'name': 'Ruoxin Xu', 'email': 'ruoxin.xu@mongodb.com', 'username': 'RuoxinXu'} Message: SERVER-37164 Prevent a sparse index being used on $gte and $lte queries with null (#23085) GitOrigin-RevId: 4c9bd9b5c1e8ac5bede2f4a8f9b987127056317d Branch: master https://github.com/mongodb/mongo/commit/24a329a09049babb684b8d4b4e4b8fabea5f67b4

Steps to Reproduce