$exists with sparse index not working

[Patrick Scott]

I'm using mongo 2.0.1 and I have a sparse index on a field that is
rarely non-null. If I try to issue a query of find({myField:
{$exists:false}}) I get no results back even though there are plenty
of documents without the field.

I recently compacted the collection if that helps any.

Thanks,
Patrick

[Patrick Scott]

Deleting the index and rebuilding appears to make it work for $exists:
true but not for $exists: false, I still get no documents.

> --
> You received this message because you are subscribed to the Google Groups "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
>

[Robert Stam]

A sparse index only contains entries for document where the field
*does* exist, hence it cannot be used to find the documents where the
field does *not* exist.

Details at:

http://www.mongodb.org/display/DOCS/Indexes#Indexes-SparseIndexes

If you need to do $exists false queries then use a regular index
instead of a sparse index.

On Thu, Dec 15, 2011 at 2:41 PM, Patrick Scott

[Patrick Scott]

I'm ok with it not using the index for a query, I have a different one
that I use as a hint but I think it is strange that documents without
the field are not being returned.

[Sam Millman]

Most likely mongo is using the sparse index since it is detecting that as the only index on that query, mongo will use that index if it does not get another index first.

[Sam Millman]

Hence the reason it only returns $exists true

[Patrick Scott]

Well, the full query has more fields plus a hint on the index to use
which is unrelated to the sparse index. Also, this had been working
perfectly until I upgraded to 2.0.1 and compacted the collection.

[Sam Millman]

Hmm interesting, can you show us this full query and an explain for the find()?

[Patrick Scott]

After some more investigating, I think the rebuild of my sparse index
fixed the issue. Before the rebuild, whenever my query contained that
field, it returned empty. Now, with the correct index hint, I get the
correct behavior.

Maybe something happened during compaction that screwed up the sparse index.

Thanks for the help,
Patrick

[Patrick Scott]

I may have found the underlying problem and I just want a little clarification.

My query is essentially an atomic update:
db.myCollection.update({field1: "something", sparseField:
{$exists:false}}, {$set: {sparseField: "someVal"}}).

I'm using morphia to create a query with a hint index that I pass to
the update operation. Essentially I'm trying to pass the hint index to
the query portion of an update and I have a feeling like that isn't
supported. I tried to recreate it from the shell but it seems like I
can't.

Are hints supported in updates?

On Thu, Dec 15, 2011 at 7:45 PM, Patrick Scott

[Scott Hernandez]

Update doesn't take a hint for the query, no.

Feel free to add a jira feature request: http://jira.mongodb.org/

I think this issue should be solved in another way via another bug
fix, but for now this would be the way. Another work around could be
to do the query to get the _id values and then do an $in query with
those values to do the update (in batches). Or you can drop the index,
do the update, recreate the index.

If you plan to fill in values for all those fields then a unique index
won't help, or be useful over a normal one.

On Fri, Dec 16, 2011 at 2:42 PM, Patrick Scott