[SERVER-14039] $nearSphere query with 2d index, skip, and limit returns incomplete results Created: 23/May/14  Updated: 02/Feb/15  Resolved: 24/Jul/14

Status: Closed
Project: Core Server
Component/s: Geo
Affects Version/s: 2.6.3, 2.7.0
Fix Version/s: 2.6.4

Type: Bug Priority: Major - P3
Reporter: A. Jesse Jiryu Davis Assignee: Siyuan Zhou
Resolution: Done Votes: 2
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
is duplicated by SERVER-14329 2d queries with skip and limit don't ... Closed
is duplicated by SERVER-14493 mongodb 2.6.3 when use geosearch ,ski... Closed
Related
related to SERVER-14515 Remove default limit from geoNear ope... Closed
Operating System: ALL
Steps To Reproduce:

db.test.drop();
db.test.ensureIndex({ "loc": "2d" });
db.test.insert({
    "loc": { "long": 33, "lat": 38 },  // Far away
    "type": "restaurant"
});
 
db.test.insert({"loc": { "long": -122, "lat": 38 }});
db.test.insert({"loc": { "long": -122, "lat": 37 }});
 
jsTest.log('$nearSphere with skip(1) and limit(2):');
var results = db.test.find({"loc": {"$nearSphere": [-122, 38.5]}}).skip(1).limit(2);
print("count() is: ", results.count(true));
print("but only one actual result:");
print(tojson(results.toArray()));
 
jsTest.log('Regular query with skip(1) and limit(2):');
var results = db.test.find().skip(1).limit(2);
print("count() is: ", results.count(true));
print("and two results:");
print(tojson(results.toArray()));

Participants:

 Description   
Issue Status as of Aug 6, 2014

ISSUE SUMMARY
A $nearSphere query with a 2d index, a skip(m), and a limit(n) returns m results less than expected. If m and n are equal, zero results are returned.

USER IMPACT
Query results are incomplete or empty.

WORKAROUNDS
Replace the original value used in limit with the sum of the values used in limit + skip. For example, if using .skip(5).limit(4), specify .skip(5).limit(9) instead.

AFFECTED VERSIONS
MongoDB 2.6 production releases up to 2.6.3 are affected by this issue.

FIX VERSION
The fix is included in the 2.6.4 production release.

RESOLUTION DETAILS
When calculating how many documents to return, add the number of documents previously skipped.

Original description

A $nearSphere query with a 2d index, a skip, and a limit returns one less result than expected. If the limit is 1, then zero results are returned.

Same thing happens if the limit is negative. Not observed with 2dsphere index.

Discovered by Stefan Wójcik while adding geo tests to PyMongo.



 Comments   
Comment by Thomas Rueckstiess [ 24/Jul/14 ]

Hi hostirosti,

As you may have noticed, we were able to fix this bug quicker than expected and managed to get it into the 2.6.4 release, which will likely be early August.

Regards,
Thomas

Comment by Githook User [ 24/Jul/14 ]

Author:

{u'username': u'visualzhou', u'name': u'Siyuan Zhou', u'email': u'siyuan.zhou@mongodb.com'}

Message: SERVER-14039 Wrong limit after skip with $nearSphere, 2d index
Branch: v2.6
https://github.com/mongodb/mongo/commit/729d76e8a0ea32b153734131f5076724556a6360

Comment by Thomas Rueckstiess [ 23/Jul/14 ]

Hi Robert,

We're currently evaluating whether this can be backported to 2.6, in which case we would add the fix to one of the upcoming 2.6.x releases. When we decided that it's feasible, we will add the appropriate Fix Version (e.g. 2.6.x) to the ticket. The earliest possible 2.6 version this can go into would be 2.6.5, which is about 6-8 weeks out, however we cannot guarantee that it will be in 2.6.5 at this stage.

In the mean time, as a workaround you can change the limit to get the expected results. Replace your original limit with (limit + skip). For example, if you'd like .skip(5).limit(4), instead specify .skip(5).limit(9).

I hope this workaround is suitable for you until a fix is available.

Regards,
Thomas

Comment by Robert K. [ 23/Jul/14 ]

Is there an ETA when this will be fixed?

Comment by Thomas Rueckstiess [ 08/Jul/14 ]

This only occurs with 2d indices, not with 2dsphere. Confirmed for both $near and $nearSphere.

Versions up to 2.4.10 are not affected, seems to be a regression starting in 2.6.0.

2.4.10

MongoDB shell version: 2.4.10
connecting to: test
> db.mgendata.find({loc: {"$near": [114.3, 30.5]}}).skip(1).limit(3)
{ "_id" : ObjectId("53bc0ae9fe4dcec964d382ee"), "loc" : [  104.88962107123359,  32.70472581753306 ] }
{ "_id" : ObjectId("53bc0aeafe4dcec966d3835e"), "loc" : [  118.42378866552883,  18.917725130049575 ] }
{ "_id" : ObjectId("53bc0aeafe4dcec966d3834a"), "loc" : [  125.71413803671265,  25.823276292123225 ] }
>

2.6.0

MongoDB shell version: 2.6.0
connecting to: test
> db.mgendata.find({loc: {"$near": [114.3, 30.5]}}).skip(1).limit(3)
{ "_id" : ObjectId("53bc0ae9fe4dcec964d382ee"), "loc" : [ 104.88962107123359, 32.70472581753306 ] }
{ "_id" : ObjectId("53bc0aeafe4dcec966d3835e"), "loc" : [ 118.42378866552883, 18.917725130049575 ] }
>

2.6.3 is also still affected.

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