[SERVER-10608] {$exists: false} will not use index Created: 23/Aug/13  Updated: 02/Jan/19  Resolved: 29/Jan/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.2.3
Fix Version/s: 2.5.5

Type: Bug Priority: Major - P3
Reporter: James Hartig Assignee: Benety Goh
Resolution: Done Votes: 1
Labels: indexes, query_triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File existsindex.js     File server10608.js    
Issue Links:
Depends
depends on SERVER-11446 don't punt all not/nor to collscan. Closed
Related
related to SERVER-10005 $exists:false prevents use of a spars... Closed
Backwards Compatibility: Fully Compatible
Operating System: OS X
Steps To Reproduce:

Here's an example collection's index (besides _id):
{
"v" : 1,
"key" :

{ "c" : 1 }

,
"ns" : "test.test",
"name" : "c_1",
"background" : true,
"sparse" : true
}

There are 6 items:
db.test.insert(

{c: 5, b: 1}

);
db.test.insert(

{c: 5}

);
db.test.insert(

{c: 4}

);
db.test.insert(

{c: 3}

);
db.test.insert(

{c: 5}

);
db.test.insert(

{c: 1}

);

db.test.find({c: 5, b: {$exists: false}}).explain()
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 2,
"nscannedObjects" : 6,
"nscanned" : 6,
"nscannedObjectsAllPlans" : 6,
"nscannedAllPlans" : 6,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {

},
"server" : "test:27017"
}

Even if I add an index on

{c: 1, b: 1}

it won't use that index either.

With hint (where did the "b": 1 go on the first item?):
db.test.find({c: 5, b: {$exists: false}}).hint(

{c: 1}

)

{ "_id" : ObjectId("5216bcf5f2aa2ebe3c6ed298"), "c" : 5 } { "_id" : ObjectId("5216bcf5f2aa2ebe3c6ed295"), "c" : 5 }
Participants:

 Description   

If you're doing a query with exists, it will not use the index. SERVER-393 was supposed to fix this afaik, but it is still forcing queries to not use the index, even if there is a more suitable one (including the exists field). If I use $hint to force it to use an index, then it just ignores the $exists.

Additionally, if I use $hint, then the documents returned do NOT contain that field even if they should've. It's like I added a projection with that field being excluded. This might be a different bug though.

See steps to reproduce for a test case.



 Comments   
Comment by Ramon Fernandez Marina [ 02/Jan/19 ]

ohadr, will you please open a new SERVER ticket and provide details of the issue you're running into? We'll need to see the output of your queries run with explain(). Note though that MongoDB 3.2 is no longer supported, so my recommendation is to upgrade to a supported version (preferably 4.0) and report back if you think something is not working correctly.

Thanks,
Ramón.

Comment by OhadR [ 27/Dec/18 ]

is this still relevant to mongodb v.3 and above? i have mongo 3.2, and i invoke a query with "$exists" on a key which is indexed. it takes a lot of time, and when i make the call with explain(true), i see that it traverses over all the docs in the collection.

is it a bug? if so, should this bug be reopened, or should i open a new bug for the 3.2 version?

Comment by Benety Goh [ 14/Mar/14 ]

This fix was made to the new query framework introduced in 2.6. There's been a lot of code movement since 2.2. I'm not sure if a cherry-pick will help.

Comment by James Hartig [ 14/Mar/14 ]

Any updates on a backport to 2.2 or any help with a manual cherry-pick?

Comment by Samantha Ritter (Inactive) [ 29/Jan/14 ]

Discussed this with Benety, and the case I was trying is a slightly different case than this ticket deals with.

Comment by James Hartig [ 05/Dec/13 ]

If you cannot backport the fix, which commits are necessary to cherry-pick to 2.2 so we can manually apply the fix? It seems like the bea51dd commit just added a test and the real fix was in 0498f81, correct?

Comment by Githook User [ 04/Dec/13 ]

Author:

{u'username': u'benety', u'name': u'Benety Goh', u'email': u'benety@mongodb.com'}

Message: SERVER-10608 added test cases for exists operator
Branch: master
https://github.com/mongodb/mongo/commit/bea51dd281f49b3f0bf0fb6fa627d01205b72de4

Comment by James Hartig [ 04/Dec/13 ]

Can the negation fix be backported then in addition? The problem is that, at Grooveshark, we cannot upgrade from 2.0 to 2.4+ without first going through 2.2 according to the migration docs, and we cannot upgrade to 2.2 without the server crashing since this bug causes some of our queries to not use any indexes. Without this fix, or a clean migration path from 2.0 to 2.4+, we are stuck on 2.0 unless we want to incur downtime.

Comment by Benety Goh [ 04/Dec/13 ]

case 2 in test script depends on negation fix

Comment by James Hartig [ 05/Nov/13 ]

The fix is slated for 2.5.x release. Can it get backported to 2.2.x?

Comment by Benety Goh [ 01/Nov/13 ]

includes both $exists: true and $exists: false cases on non-indexed field

Comment by James Hartig [ 23/Aug/13 ]

Managed to test this on 2.0.8 and it uses the index fine, so this looks like a regression in 2.2.x.

Comment by James Hartig [ 23/Aug/13 ]

For an immediate fix for {$exists: false} use null instead.

db.test.find(

{c: 5, b: null}

).explain()
{
"cursor" : "BtreeCursor c_1",
"isMultiKey" : false,
"n" : 2,
"nscannedObjects" : 3,
"nscanned" : 3,
"nscannedObjectsAllPlans" : 6,
"nscannedAllPlans" : 6,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" :

{ "c" : [ [ 5, 5 ] ] }

,
"server" : "test:27017"
}

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