[SERVER-13939] Unexpected behavior of query planner with sparse indices Created: 14/May/14  Updated: 10/Dec/14  Resolved: 15/May/14

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

Type: Question Priority: Major - P3
Reporter: Sergey Isaev Assignee: Unassigned
Resolution: Done Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-12684 incorrect result and index not used f... Closed
Related
related to SERVER-13986 Creating a sparse index makes the not... Closed
related to SERVER-12735 Improve negation-related tree normali... Backlog
Participants:

 Description   

I am running mongodb version 2.6.1. I have a sparse index on testField. This query:

db.testCollection.find({ testField: "123"})

uses my index. This query:

db.testCollection.find( {testField: { $exists: true } })

does this as well. But this query:

db.testCollection.find({ testField: {$ne: null } })

perform a full collection scan. According to the documentation: "If a sparse index results in an incomplete result set for queries and sort operations, MongoDB will not use that index". But i don't understand why is the third query result set can't be obtained from the sparse index in my db.
Is this a bug or am i doing something wrong?



 Comments   
Comment by J Rassi [ 15/May/14 ]

As of MongoDB 2.6, the query engine does consider assignment of sparse indexes to negated expressions. This was part of a design decision made to prevent queries against sparse indexes from returning partial results (e.g. a query {a: {$ne: 5}} would omit the result {} if it used the sparse index {a: 1}). There are many special cases of negated expressions that could technically be assigned a sparse index (e.g. {a: {$ne: null}}, {a: {$not: {$exists: false}}}); some of these will be handled in a future release by SERVER-12735.

As a workaround, add an additional {a: {$exists: true}} clause to the query predicate (which can be assigned to the sparse index) or add a hint() for the index.

Resolving as a dup of SERVER-12684 + SERVER-12735.

Comment by Anatoliy Koperin [ 14/May/14 ]

It does not reproduced in the version 2.4.10
example of creating an index:

 db.testCollection.createIndex({"testField" : 1}, {name: "testIndex", sparse: true})

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