[SERVER-24209] Limit() phase does not appear to work as expected with complex query Created: 19/May/16  Updated: 02/Jun/16  Resolved: 19/May/16

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

Type: Bug Priority: Major - P3
Reporter: Steven Hand Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File gen_sample_collection.json    
Issue Links:
Duplicate
duplicates SERVER-13732 Predicates in top-level implicit AND ... Closed
Operating System: ALL
Participants:

 Description   

The limit() function is not properly applied if the query is complicated.



 Comments   
Comment by Steven Hand [ 19/May/16 ]

Thanks scotthernandez. I'll close this as a duplicate of SERVER-13732

Comment by Scott Hernandez (Inactive) [ 19/May/16 ]

This is not an issue with limit, but simply how "$or" clauses work with shared "and" clauses when selecting indexes to use.

If limit is really not returning just the specified count (20) please show an example of that bug, where limit does not work.

See the underlying issue here: SERVER-13732

Comment by Steven Hand [ 19/May/16 ]

A workaround is to add the "a" field to each of the "$or" predicates

> db.sample.find({$or:[{a:0,b:{$gt:76}},{a:0,b:76,c:{$lt:3094}}]}).limit(20).explain(true).executionStats.totalDocsExamined
20

Comment by Steven Hand [ 19/May/16 ]

mgenerate input file

Comment by Steven Hand [ 19/May/16 ]

Given a collection with the following schema:

> db.sample.find().limit(3).pretty()
{
	"_id" : ObjectId("573de1ba69610a3802f7125b"),
	"a" : 0,
	"c" : 76,
	"b" : 3094
}
{
	"_id" : ObjectId("573de1ba69610a3803f7125b"),
	"a" : 0,
	"c" : 24,
	"b" : 3480
}
{
	"_id" : ObjectId("573de1ba69610a3805f7125b"),
	"a" : 2,
	"c" : 84,
	"b" : 6964
}
> db.sample.find().count()
1000000
>  db.sample.createIndex({a:1,b:1,c:1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}

I get the following results:

db.sample.find({a:0,$or:[{b:{$gt:76}},{b:76,c:{$lt:3094}}]}).limit(20).explain(true).executionStats.totalDocsExamined
2529

when I would have expected the entries plucked from the index to be limited to 20

Generated at Thu Feb 08 04:05:41 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.