[SERVER-21929] $in doesn't find fields of type undef as null Created: 17/Dec/15  Updated: 06/Oct/21  Resolved: 14/Jun/21

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: 5.0.0-rc2, 5.1.0-rc0

Type: Bug Priority: Major - P3
Reporter: John Page Assignee: David Storch
Resolution: Done Votes: 0
Labels: mql-semantics, query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Depends
Related
related to SERVER-58637 Temporarily disable null_query_semant... Closed
Backwards Compatibility: Minor Change
Operating System: ALL
Backport Requested:
v5.0, v4.9, v4.4, v4.2
Sprint: Query Execution 2021-06-14, Query Execution 2021-06-28
Participants:
Linked BF Score: 160

 Description   

If you have a values which is undefined versus null - $in doesnt work

 
db.a.insert({x:1,v:null})
db.a.insert({x:2,v:""})
db.a.insert({x:3})
db.a.insert({x:4,v:undefined})
 
db.a.find(v:null)

Finds all except wher v is ""
but

db.a.find(v:{$in:[null,""]})

fails to find the record where x = 4

A workaround is

db.u.find({ $or : [ {v:{$type:6}},{v:{$in:[null,""]}}]})

Which is ugly



 Comments   
Comment by Vivian Ge (Inactive) [ 06/Oct/21 ]

Updating the fixversion since branching activities occurred yesterday. This ticket will be in rc0 when it’s been triggered. For more active release information, please keep an eye on #server-release. Thank you!

Comment by Githook User [ 15/Jun/21 ]

Author:

{'name': 'David Storch', 'email': 'david.storch@mongodb.com', 'username': 'dstorch'}

Message: SERVER-21929 Make $in with null always match undefined

(cherry picked from commit d75f3a8bb66ef1ef41bfd16eecd42c31f91c818b)
Branch: v5.0
https://github.com/mongodb/mongo/commit/aa50d1c7ee75512a5e860b2cede3b91bb957ae4b

Comment by David Storch [ 14/Jun/21 ]

I'm marking this as a minor breaking change, because it changes the semantics of $in queries if the user has stored the deprecated undefined BSON type. Use of undefined is strongly discouraged, and this type has been deprecated for many years, so this shouldn't have much of an impact in practice.

I will quickly describe exactly what changed. In versions without this commit, an $in query involving the null value will not matched undefined, but it will match missing. After this commit, such predicates will match undefined in addition to null and missing:

MongoDB Enterprise > db.c.drop()
]true
MongoDB Enterprise > db.c.insert({a: 1})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.c.insert({})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.c.insert({a: undefined})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.c.insert({a: null})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.c.find({a: {$in: [42, null]}})
{ "_id" : ObjectId("60c77f53e2df5540820698ef") }
{ "_id" : ObjectId("60c77f55e2df5540820698f0"), "a" : undefined }
{ "_id" : ObjectId("60c77f59e2df5540820698f1"), "a" : null }

A similar change applies for $lookup. Namely, a null from the left-hand side will now join with undefined in addition to null and missing:

MongoDB Enterprise > db.left.drop()
false
MongoDB Enterprise > db.right.drop()
false
MongoDB Enterprise > db.left.insert({a: null})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.right.insert({b: 1})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.right.insert({})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.right.insert({b: null})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.right.insert({b: undefined})
WriteResult({ "nInserted" : 1 })
 
MongoDB Enterprise > db.left.aggregate([{$lookup: {from: "right", as: "joinedWith", localField: "a", foreignField: "b"}}]).pretty()
{
	"_id" : ObjectId("60c77fa3e2df5540820698f2"),
	"a" : null,
	"joinedWith" : [
		{
			"_id" : ObjectId("60c77faee2df5540820698f4")
		},
		{
			"_id" : ObjectId("60c77fb0e2df5540820698f5"),
			"b" : null
		},
		{
			"_id" : ObjectId("60c77fb3e2df5540820698f6"),
			"b" : undefined
		}
	]
}

Comment by Githook User [ 14/Jun/21 ]

Author:

{'name': 'David Storch', 'email': 'david.storch@mongodb.com', 'username': 'dstorch'}

Message: SERVER-21929 Make $in with null always match undefined
Branch: master
https://github.com/mongodb/mongo/commit/d75f3a8bb66ef1ef41bfd16eecd42c31f91c818b

Comment by John Page [ 17/Dec/15 ]

Version was 3.2

Generated at Thu Feb 08 03:58:50 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.