[SERVER-14083] Regression from 2.4.x -> 2.6.x in order of $or Created: 29/May/14  Updated: 10/Dec/14  Resolved: 29/May/14

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

Type: Bug Priority: Major - P3
Reporter: Daniel Doubrovkine Assignee: David Storch
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

 
db.collection.insert({ position: 1 })
db.collection.insert({ position: 2 })
 
var ids = db.collection.find({}, { _id: 1 }).sort({ _id: -1 }).map(function(doc) { return { _id: doc._id } })
db.collection.find({ $or : ids })
 
---
 
## 2.4.10
 
joe@li245-114:~$ mongo
MongoDB shell version: 2.4.10
connecting to: test
> db.collection.insert({ position: 1 })
> db.collection.insert({ position: 2 })
> 
> var ids = db.collection.find({}, { _id: 1 }).sort({ _id: -1 }).map(function(doc) { return { _id: doc._id } })
> db.collection.find({ $or : ids })
{ "_id" : ObjectId("538748f9b637c258c6964136"), "position" : 2 }
{ "_id" : ObjectId("538748f9b637c258c6964135"), "position" : 1 }
 
 
## 2.6.0
 
> db.collection.insert({ position: 1 })
WriteResult({ "nInserted" : 1 })
> db.collection.insert({ position: 2 })
WriteResult({ "nInserted" : 1 })
> 
> var ids = db.collection.find({}, { _id: 1 }).sort({ _id: -1 }).map(function(doc) { return { _id: doc._id } })
> db.collection.find({ $or : ids })
{ "_id" : ObjectId("538749a82aa9f7bc4f6dd565"), "position" : 1 }
{ "_id" : ObjectId("538749a82aa9f7bc4f6dd566"), "position" : 2 }
 

Participants:

 Description   

We used to rely on $or to return an ordered result set. 2.6.? no longer does that.

I understand that was never documented as a feature, but it's pretty clear that lots of people do that , see http://stackoverflow.com/questions/3142260/order-of-responses-to-mongodb-in-query for example.



 Comments   
Comment by David Storch [ 29/May/14 ]

Hi dblock, fair point, this should be documented. I opened DOCS-3502 for this purpose.

Comment by Daniel Doubrovkine [ 29/May/14 ]

David, I agree that this works as designed. However, I know at least a handful of companies relying on this behavior that has often been described in email threads from 10gen developers, so you're going to see some surprised people. At the least, I would document this in the changelog or something like that?

Comment by David Storch [ 29/May/14 ]

Hi dblock,

Thanks for the bug report with detailed repro steps!

In general, MongoDB does not guarantee that the results are returned in any particular order, unless the query has a .sort(...). (One exception is geoNear queries, which return results sorted by descending distance.) A regular .find() query with .sort() is allowed to return its result set in any order. To answer the query

{a: {$gt: 1}, b: {$gt: 1}}

the query planner might decide to scan index {b: 1} which means that you would get results back in order of ascending "b". But relying on this ordering is bad because someday the planner might decide to use index {a: 1} instead.

So the bottom line is "never expect a sort without a .sort()". Read on if you're curious about the internal details regarding why this particular use no longer provides the same sort order.

The different ordering between 2.4.x and 2.6.x was the result of query normalization that was added in 2.6.0. Previous versions of MongoDB would sometimes behave differently for logically equivalent queries based on the ordering of the query predicates: for example, query {a: 5, b: 7} might behave differently than {b: 7, a: 5}. Queries within a logical equivalence class are now normalized to the same canonical form. One consequence is that the ordering of $or clauses is no longer significant, which causes the behavior change described by this ticket.

I hope this explanation is helpful, and please feel free to reach out with further questions or concerns.

Best,
Dave

Comment by Daniel Doubrovkine [ 29/May/14 ]

Ruby workaround w/ Mongoid: https://gist.github.com/dblock/d5ed835f0147467a6a27

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