[SERVER-13484] Implied $and in find query doesn't work consistently Created: 03/Apr/14  Updated: 10/Dec/14  Resolved: 04/Apr/14

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Gerry F Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to DOCS-3208 Clarify $and reference page to reflec... Closed
Operating System: ALL
Steps To Reproduce:

In a mongo shell:

db.doubleID.insert(_id:2,column1:2)
db.doubleID.find({ $or: [ { _id: { $lte: 1.0 } }, { _id: { $lte: "1" } } ], $or: [ { _id: { $gte: 1.0 } }, { _id: { $gte: "1" } } ] })
db.doubleID.find({ $or: [ { _id: { $gte: 1.0 } }, { _id: { $gte: "1" } } ], $or: [ { _id: { $lte: 1.0 } }, { _id: { $lte: "1" } } ] })

The first find returns 1 row, the second find returns 0 rows.

Explicitly stating the $and seems to work as expected:

db.doubleID.find({ $and: [ {$or: [ { _id: { $lte: 1.0 } }, { _id: { $lte: "1" } } ]}, {$or: [ { _id: { $gte: 1.0 } }, { _id: { $gte: "1" } } ]}] })

This bug is not specific to the _id column, this also returns one row:

db.doubleID.find({ $or: [ { column1: { $lte: 1.0 } }, { column1: { $lte: "1" } } ], $or: [ { column1: { $gte: 1.0 } }, { column1: { $gte: "1" } } ] })

Participants:

 Description   

Changing the order of the clauses in a query object can change the result returned.

In the sample below, I reversed the order of the two $or clauses.

It looks like this is in 2.4.6 through 2.4.9, I haven't tested other server versions yet.

http://docs.mongodb.org/master/reference/operator/query/and/#op._S_and documents an implicit AND. The behaviour should probably be changed to match the documentation.



 Comments   
Comment by J Rassi [ 17/Apr/14 ]

Filed DOCS-3208.

Comment by Gerry F [ 04/Apr/14 ]

Can we update the documentation to clarify that $or is a field? I mis-interpreted $or as an operation in reading the above quoted paragraph.

Comment by J Rassi [ 04/Apr/14 ]

You're encountering a JavaScript syntax issue. Your first two examples create a JavaScript object using object literal syntax where two property assignments share the same name, which results in an object with only one property. Only one $or clause ever makes it into the query object.

Consider the JavaScript statement var obj = {a: 1, a: 2}. You can think of it as being evaluated as follows (see EMCA-262 11.1.5 for gritty details):

var obj = new Object();
obj.a = 1;
obj.a = 2;

This results in an object with one property: name "a", value 2.

> var obj = {a: 1, a: 2}
> obj
{ "a" : 2 }

Similarly:

> var query = { $or: [ { _id: { $lte: 1.0 } }, { _id: { $lte: "1" } } ], $or: [ { _id: { $gte: 1.0 } }, { _id: { $gte: "1" } } ] }
> query
{
	"$or" : [
		{
			"_id" : {
				"$gte" : 1
			}
		},
		{
			"_id" : {
				"$gte" : "1"
			}
		}
	]
}
>

In fact, the primary reason for the existence of the $and operator is to support this use case of having multiple predicates on the same "key". See the following excerpt from the $and documentation:

If, however, a query requires an AND operation on the same field such as { price: { $ne: 1.99 } } AND { price: { $exists: true } }, then either use the $and operator for the two separate expressions or combine the operator expressions for the field { price: { $ne: 1.99, $exists: true } }.

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