[SERVER-856] Geospatial result paging fails when sorting with additional keys Created: 31/Mar/10  Updated: 04/Dec/15  Resolved: 04/Dec/15

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

Type: Bug Priority: Major - P3
Reporter: Timbo White Assignee: Unassigned
Resolution: Done Votes: 13
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Linux 32 bit


Issue Links:
Related
related to SERVER-2997 limits do not seem to work correctly ... Closed
Participants:

 Description   

Using limit() and skip() to page result sets yields unexpected results when utilizing geospatial querying and an additional sort key. Objects are not correctly sorted per the additional sort key, and some of the same objects appear in subsequent result sets.

To reproduce:

use foo
db.foo.ensureIndex({loc: "2d"})
db.foo.ensureIndex({random: 1 })
for( var i = 1; i <= 100; i++ ){db.foo.save( { loc: [1, 1], random: Math.floor(Math.random()*100) } )}
 
/* query 5 objects per set */
 
/* page 1 */
db.foo.find({loc: {$near: [1, 1]}}).limit(5).skip((1 - 1) * 5).sort({random: 1})
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad931c"), "loc" : [ 1, 1 ], "random" : 40 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad931e"), "loc" : [ 1, 1 ], "random" : 55 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad931f"), "loc" : [ 1, 1 ], "random" : 63 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad931b"), "loc" : [ 1, 1 ], "random" : 71 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad931d"), "loc" : [ 1, 1 ], "random" : 75 }
 
 
/* page 2 */
db.foo.find({loc: {$near: [1, 1]}}).limit(5).skip((2 - 1) * 5).sort({random: 1})
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad931f"), "loc" : [ 1, 1 ], "random" : 63 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad9324"), "loc" : [ 1, 1 ], "random" : 67 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad931b"), "loc" : [ 1, 1 ], "random" : 71 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad931d"), "loc" : [ 1, 1 ], "random" : 75 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad9322"), "loc" : [ 1, 1 ], "random" : 99 }
 
/* page 3 */
db.foo.find({loc: {$near: [1, 1]}}).limit(5).skip((3 - 1) * 5).sort({random: 1})
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad9324"), "loc" : [ 1, 1 ], "random" : 67 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad931b"), "loc" : [ 1, 1 ], "random" : 71 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad931d"), "loc" : [ 1, 1 ], "random" : 75 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad9328"), "loc" : [ 1, 1 ], "random" : 91 }
{ "_id" : ObjectId("4bb2d5e2284b5ab1d9ad9322"), "loc" : [ 1, 1 ], "random" : 99 }



 Comments   
Comment by David Storch [ 04/Dec/15 ]

Looks like this is fixed in 3.0:

> db.foo.find({loc: {$near: [1, 1]}}).limit(5).skip((1 - 1) * 5).sort({random: 1})
{ "_id" : ObjectId("5661dc529e5322149cb56390"), "loc" : [ 1, 1 ], "random" : 0 }
{ "_id" : ObjectId("5661dc529e5322149cb56377"), "loc" : [ 1, 1 ], "random" : 1 }
{ "_id" : ObjectId("5661dc529e5322149cb5637c"), "loc" : [ 1, 1 ], "random" : 1 }
{ "_id" : ObjectId("5661dc529e5322149cb5636e"), "loc" : [ 1, 1 ], "random" : 2 }
{ "_id" : ObjectId("5661dc529e5322149cb56367"), "loc" : [ 1, 1 ], "random" : 3 }
> db.foo.find({loc: {$near: [1, 1]}}).limit(5).skip((2 - 1) * 5).sort({random: 1})
{ "_id" : ObjectId("5661dc529e5322149cb56380"), "loc" : [ 1, 1 ], "random" : 4 }
{ "_id" : ObjectId("5661dc529e5322149cb5637e"), "loc" : [ 1, 1 ], "random" : 5 }
{ "_id" : ObjectId("5661dc529e5322149cb56379"), "loc" : [ 1, 1 ], "random" : 6 }
{ "_id" : ObjectId("5661dc529e5322149cb56382"), "loc" : [ 1, 1 ], "random" : 6 }
{ "_id" : ObjectId("5661dc529e5322149cb5633d"), "loc" : [ 1, 1 ], "random" : 7 }
> db.foo.find({loc: {$near: [1, 1]}}).limit(5).skip((3 - 1) * 5).sort({random: 1})
{ "_id" : ObjectId("5661dc529e5322149cb56378"), "loc" : [ 1, 1 ], "random" : 8 }
{ "_id" : ObjectId("5661dc529e5322149cb56334"), "loc" : [ 1, 1 ], "random" : 9 }
{ "_id" : ObjectId("5661dc529e5322149cb56375"), "loc" : [ 1, 1 ], "random" : 9 }
{ "_id" : ObjectId("5661dc529e5322149cb56396"), "loc" : [ 1, 1 ], "random" : 9 }
{ "_id" : ObjectId("5661dc529e5322149cb5634a"), "loc" : [ 1, 1 ], "random" : 10 }

Closing as "Gone Away", since I haven't tracked down the exact change that fixed the bug.

Comment by Vladimir [ 19/Oct/12 ]

rob jennings, the problem is that I need sort by distance and in addition result must be sorted with another one field.

Comment by rob jennings [ 18/Oct/12 ]

Vladamir, as per the docs "$within can be used instead of $near to find items within a shape. Results are not sorted by distance, which may result in faster queries when this sorting is not required" It would greatly simplify things in my use case to page results of $near

Comment by Vladimir [ 09/Apr/12 ]

Alex, is that possible to make sort by distance with $within query? I just want to combine distance sort with other fields.

Comment by Thiago D [ 15/Mar/12 ]

Thank you, Alex. This solved my problem.

Comment by Alex Sharp [ 15/Mar/12 ]

Our solution was to drop the use of $near and use $within: {$center: []} instead.

Comment by Tiziano [ 15/Mar/12 ]

I've got the same issue... Is in plan to fix this?

Comment by Thiago D [ 04/Mar/12 ]

We are experiencing it too, someone knows some way to workaround this?

Comment by Alex Sharp [ 02/Mar/12 ]

We're experiencing the exact same issue. Can anyone comment on this, and if it's a priority? Seems like a pretty major bug.

Generated at Thu Feb 08 02:55:22 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.