[SERVER-12684] incorrect result and index not used for $ne with sparse index Created: 11/Feb/14  Updated: 10/Dec/14  Resolved: 18/Feb/14

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

Type: Bug Priority: Major - P3
Reporter: Asya Kamsky Assignee: David Storch
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-13939 Unexpected behavior of query planner ... Closed
Related
related to SERVER-13986 Creating a sparse index makes the not... Closed
is related to SERVER-12532 Negate index bounds for $not instead ... Closed
Backwards Compatibility: Major Change
Operating System: ALL
Participants:

 Description   

The way indexed queries against sparse index work changed from 2.4.x to 2.5.5 - in pre-2.5 the results of $eq and $ne of same value would add up to the number of entries in the index, in 2.5.5+ it's the total number of documents (same as with non-sparse index).

Not that this only impacts $ne - $gt shows the same behavior as before.

db.test.insert({a:1})
db.test.insert({a:1,b:1})
db.test.insert({b:1})
db.test.ensureIndex({a:1},{sparse:true})

Results in 2..5.5

> db.test.find().count()
3
> db.test.find({a:1}).count()
2
> db.test.find({a:{$ne:1}}).count()
1
> db.test.find({a:{$gt:1}}).count()
0

And in 2.4.8

> db.test.find().count()
3
> db.test.find({a:1}).count()
2
> db.test.find({a:{$ne:1}}).count()
0
> db.test.find({a:{$gt:1}}).count()
0



 Comments   
Comment by David Storch [ 18/Feb/14 ]

Re-closing this as "Works as Designed" after discussing with the query team. In general we should follow the rule that query results from collection scans should be the same as query results from indexed solutions. Version 2.4 broke this rule with negations over sparse indices, but also for negations in general.

Here's an example from 2.4.8:

> t = db.t
test.t
> t.drop()
false
> t.save({a: 4})
> t.save({a: "str"})
> t.find({a: {$not: {$gt: 3}}})
{ "_id" : ObjectId("5303a4ea02a2eb9f1e778469"), "a" : "str" }
> t.ensureIndex({a: 1})
> t.find({a: {$not: {$gt: 3}}})
>

The unindexed query returns the document {a: "str"}, but after adding an index no results are returned for the same query. This is now fixed in 2.6-pre:

> t = db.t
test.t
> t.drop()
true
> t.save({a: 4})
WriteResult({ "nInserted" : 1 })
> t.save({a: "str"})
WriteResult({ "nInserted" : 1 })
> t.find({a: {$not: {$gt: 3}}})
{ "_id" : ObjectId("5303a5f49cd478fc1a7345b8"), "a" : "str" }
> t.ensureIndex({a: 1})
> t.find({a: {$not: {$gt: 3}}})
{ "_id" : ObjectId("5303a5f49cd478fc1a7345b8"), "a" : "str" }

Comment by Asya Kamsky [ 14/Feb/14 ]

If this is works as expected, will backwards breaking get picked up?

Or should I open a docs ticket?

Comment by Asya Kamsky [ 14/Feb/14 ]

So that means that in 2.5.5 the user will get different results if they hint the sparse index than if they don't? That's more than just backwards breaking, that's rather confusing and it also means that:

a !=1 will not return the same number of returns as a>1 || a<1

test@local(2.5.6-pre-) > db.sparse.find({$or:[{a:{$gt:1}},{a:{$lt:1}}]}).count()
0
test@local(2.5.6-pre-) > db.sparse.find({a:{$gt:1}}).count()
0
test@local(2.5.6-pre-) > db.sparse.find({a:{$lt:1}}).count()
0
test@local(2.5.6-pre-) > db.sparse.find({a:{$ne:1}}).count()
11
test@local(2.5.6-pre-) > db.sparse.find({a:{$ne:1}}).hint("a_1").count()
0

Comment by hari.khalsa@10gen.com [ 13/Feb/14 ]

The old behavior is wrong. We can't look for nulls in a sparse index and the semantics of $not require that we retrieve the nulls.

Comment by David Storch [ 12/Feb/14 ]

This will be fixed as part of SERVER-12532

Comment by Asya Kamsky [ 11/Feb/14 ]

I think the result is because we no longer use an index for $ne.

(2.4.8) > db.test.find({a:{$ne:1}}).explain()
{
	"cursor" : "BtreeCursor a_1 multi",
	"isMultiKey" : false,
	"n" : 0,
	"nscannedObjects" : 0,
	"nscanned" : 1,
...
(2.5.5) > db.test.find({a:{$ne:1}}).explain()
{
	"cursor" : "BasicCursor",
	"n" : 1,
	"nscannedObjects" : 3,

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