{ $type : 10 } not working

[Anders Jonsson]

Hi,

1.8.2-rc2 (haven't gotten around to updating to 1.8.3 as we're not in
production yet)

I've come across a small issue. Our documents contains an array called
"Flags". "Flags" was accidently set to null in some cases which means
that we can't use $addToSet on those documents.
I figured I'd just update those documents and unset Flags from the
shell.

According to http://www.mongodb.org/display/DOCS/Querying+and+nulls i
should query for { Flags : {$type : 10}} if I only want the documents
where Flags is null (should only be a couple of thousand).

> db.Visits.find({ Flags : {$type : 10} }, { Flags : 1 }).limit(1) //I chose to just return Flags, because the documents are quite large
{ "_id" : ObjectId("4e0b1940a40f1719f8ee8549") }

Ok, so that didn't work. I ran a count() on that query and it matches
14M of 14,5M documents, so it matches all documents where Flags is
null or Flags does not exist
Am I doing something wrong?

[Robert Stam]

It seems to work for me. Here's a test in the mongo shell:

> db.test.remove()
> db.test.insert({x:1})
> db.test.insert({x:2,Flags:null})
> db.test.insert({x:3,Flags:[]})
> db.test.find()
{ "_id" : ObjectId("4e5cf7a0be46eb42e4f1dd9f"), "x" : 1 }
{ "_id" : ObjectId("4e5cf7aabe46eb42e4f1dda0"), "x" : 2, "Flags" :
null }
{ "_id" : ObjectId("4e5cf7b5be46eb42e4f1dda1"), "x" : 3, "Flags" :
[ ] }
> db.test.find({Flags:{$type:10}})
{ "_id" : ObjectId("4e5cf7aabe46eb42e4f1dda0"), "x" : 2, "Flags" :
null }
> db.test.find({Flags:{$type:10}},{Flags:1})
{ "_id" : ObjectId("4e5cf7aabe46eb42e4f1dda0"), "Flags" : null }
>

The find commands seem to be returning the right document.

[Anders Jonsson]

Weird.. your example works for me.

I tried using .hint({_id : 1}) (which will obviously result in
horrible performance). That seemed to do the trick even though it's
slow.

Then I tried something new with your example

> db.test.ensureIndex({Flags : 1})

> db.test.find({Flags:{$type:10}})

{ "_id" : ObjectId("4e5cfa3c1deff9892c6aa1d1"), "x" : 1 }
{ "_id" : ObjectId("4e5cfa3d1deff9892c6aa1d2"), "x" : 2, "Flags" :
null }
{ "_id" : ObjectId("4e5cfa3d1deff9892c6aa1d3"), "x" : 3, "Flags" :
[ ] }

That can't be right..

[Robert Stam]

So you have an index on your Flags element?

[Robert Stam]

If so, you are probably encountering this issue:

https://jira.mongodb.org/browse/SERVER-1735

Which is marked as fixed in 1.9.1.

In particular, note this comment about the results of the query after
this fix:

{a:{$type:10}} - match if a is null, but not if a is missing or
present and nonnull

[Anders Jonsson]

Yup, thats probably it. I'll try upgrading to 2.0 rc tomorrow.
I tried searching jira before i posted, but didn't find that issue.

Thanks!

[Anders Jonsson]

I tried upgrading now and that made things even more interesting.
Now I can find all documents where Flags : [] correctly, and thats a
positive. { $type : 10 } may be working, but it's incredibly slow. I
let the query run for 10 minutes before I gave up and killed it. And
that's with a limit(1).

Note: I have reindexed the collection. After that I dropped the index
and created a new one. Didn't help

I tried running your example, and that works fine, but that collection
only contains a few documents. My collection contains 14,5M documents

I'll try making a test case with a few hundred thousand documents.
Hopefully that's enought to recreate the issue.

[Nat]

Why don't you search for null explicitly? It will be able to use index.

--
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.

[Anders Jonsson]

Ok, now I don't know what to think. I inserted 12M documents into my
testcollection.. A find with { $type : 10 } is incredibly fast there
but it's still slow in my other collection..

I'm out of ideas. I guess i'll let the query run with .explain() and
see if I get any answers when (and if) it returns

/Anders

[Anders Jonsson]

Because null includes the documents where the field doesn't exist.
Thats not what I want.

/Anders

[Nat]

You mean you could have some entries in Flags array as null?

[Anders Jonsson]

In some documents, the array Flags has been set to null by mistake.
That means that I can't use $addToSet on it, because null is not an
array. I could fix this by querying for null and then set Flags to [].
That would (after all 14M documents have been updated) fix this issue,
but I want to get to the bottom of this issue, so that it doesn't
cause any problems for us later on. Plus: an extra, empty, array in
all our documents is a waste of space

/Anders

[Nat]

If so, $unset should do the trick and it should consume less space.

[Anders Jonsson]

Yes, $unset works, but in that case I can't update in batches, since a
find by null will also find the documents that I just used $unset on.
I've eliminated the null-value fields now with a huge update anyway,
just to get rid of them. I'd still like to get to the bottom of the
horrible {$type : 10}-performance though.

After I fixed the null-value fields I tried a find with { $type : 10 }
again, just to find out how long it takes.
Here's the resulting explain (and this is without any null values
present in the collection). I don't understand this at all since a
similar query on a new collection (with 12M documents) didnt behave
this way. The index has been rebuilt after I upgraded to 2.0
I also tried a {$type : 10}-query on another field in the same
collection... Lightning fast. So it's something specific to my array
"Flags"

> db.Visits.find({Flags : {$type : 10}}).limit(1).explain()
{
"cursor" : "BtreeCursor Flags_1_CompanyId_1_AccountId_1",
"nscanned" : 13320222,
"nscannedObjects" : 13320222,
"n" : 0,
"millis" : 16402010,
"nYields" : 88032,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" : {
"Flags" : [
[
null,
null
]
],
"CompanyId" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"AccountId" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
}


I really appreciate your help with this

/Anders