[SERVER-7261] field range with $not should properly account for type bracketing (query with $not returns different results after adding an index) Created: 04/Oct/12  Updated: 06/Dec/22  Resolved: 12/Jul/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 2.2.0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Joe Faber Assignee: Backlog - Query Team (Inactive)
Resolution: Done Votes: 6
Labels: query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

MacOS 10.8.2


Issue Links:
Duplicate
is duplicated by SERVER-3596 type bracketing used for $not query i... Closed
is duplicated by SERVER-8026 When an index is present, $not operat... Closed
Related
related to SERVER-1212 tighter index bounds for negated regu... Closed
Assigned Teams:
Query
Operating System: OS X
Participants:

 Description   

A query $gt:0 matches all numbers greater than 0. Non numbers do not match. $not:{$gt:0} should match the opposite values of $gt:0, which means all numbers <=0 and all non numbers. I believe the matcher implements this behavior, but the FieldRange constructor employs type bracketing and only matches numbers <= 0.

Here is a test:

c = db.c;
c.drop();
 
c.save( {} );
 
// Does not match.                                                                             
printjson( c.find( { a:{ $lt:0 } } ).toArray() );
printjson( c.find( { a:{ $lt:0 } } ).explain() );
// Does match.                                                                                 
printjson( c.find( { a:{ $not:{ $gt:0 } } } ).toArray() );
printjson( c.find( { a:{ $not:{ $gt:0 } } } ).explain() );
 
c.ensureIndex( { a:1 } );
 
// Does not match.                                                                             
printjson( c.find( { a:{ $lt:0 } } ).toArray() );
// Includes numbers < 0.                                                                       
printjson( c.find( { a:{ $lt:0 } } ).explain() );
// Now does not match.                                                                         
printjson( c.find( { a:{ $not:{ $gt:0 } } } ).toArray() );
// Includes numbers < 0.  But should also include non numbers.                                 
printjson( c.find( { a:{ $not:{ $gt:0 } } } ).explain() );

-------------------------------------------------

Without an index, a $not $gt query will return docs where the queried field is missing. Adding an index on that field causes the query to return only documents where the field is present. See example below.

$ ./mongo
MongoDB shell version: 2.2.0
connecting to: test
> db.version()
2.2.0
> db.foo.drop();
true
> db.foo.insert(

{name: 'a', data: 1}

);
> db.foo.insert(

{name: 'b'}

);
> db.foo.find({data: {$not: {$gt: 2}}});

{ "_id" : ObjectId("506ddd6228c47c8b340cc0a9"), "name" : "a", "data" : 1 } { "_id" : ObjectId("506ddd6228c47c8b340cc0aa"), "name" : "b" }

> db.foo.ensureIndex(

{data: 1}

);
> db.foo.find({data: {$not: {$gt: 2}}});

{ "_id" : ObjectId("506ddd6228c47c8b340cc0a9"), "name" : "a", "data" : 1 }

>



 Comments   
Comment by David Storch [ 12/Jul/19 ]

It looks like this problem has been fixed:

MongoDB Enterprise > c = db.c;
test.c
MongoDB Enterprise > c.drop();
true
MongoDB Enterprise >
MongoDB Enterprise > c.save( {} );
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > printjson( c.find( { a:{ $lt:0 } } ).toArray() );
[ ]
MongoDB Enterprise > printjson( c.find( { a:{ $not:{ $gt:0 } } } ).toArray() );
[ { "_id" : ObjectId("5d28e6f2e18380715b42bd7c") } ]
MongoDB Enterprise > c.ensureIndex( { a:1 } );
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
MongoDB Enterprise > printjson( c.find( { a:{ $lt:0 } } ).toArray() );
[ ]
MongoDB Enterprise > printjson( c.find( { a:{ $not:{ $gt:0 } } } ).toArray() );
[ { "_id" : ObjectId("5d28e6f2e18380715b42bd7c") } ]

I only tested the master branch, so I'm not sure exactly when it was fixed, but I'm guessing it's been fixed for some time now. Closing as Gone Away.

Comment by Chad Kreimendahl [ 13/Jun/13 ]

Nevermind... it appears that SERVER-8026 described our problem precisely. I'd assume this would be higher priority given that a query returns different data depending on whether or not its indexed. Seems like it's more major than just planning bucket A.. especially now that it's 10 months unresolved.

Also, please add that this is a part of all operating systems (unix, windows, osx, etc). We can replicate this on windows 8, windows server 2012, debian, red hat, etc.

Comment by Chad Kreimendahl [ 12/Jun/13 ]

We're seeing a very similar issue that's manifesting itself in another unique way. Will file another bug and post it here. Basically, indexed fields operate contrary to documentation. Removing the index makes the field work as intended.

Comment by Aaron Staple [ 10/Oct/12 ]

Tentatively assigning to planning bucket a.

Comment by Joe Faber [ 05/Oct/12 ]

Downloaded the 2.2.1 RC and it seems to be a problem there as well:

$ ./mongo
MongoDB shell version: 2.2.1-rc0
connecting to: test
> db.version()
2.2.1-rc0
> db.foo.drop();
true
> db.foo.insert(

{name: 'a', data: 1}

);
> db.foo.insert(

{name: 'b'}

);
> db.foo.find({data: {$not: {$gt: 2}}});
{ "_id" : ObjectId("506eeefe1ecbcd6111672248"), "name" : "a", "data" : 1 }
{ "_id" : ObjectId("506eeefe1ecbcd6111672249"), "name" : "b" }
> db.foo.ensureIndex(

{data: 1}

);
> db.foo.find({data: {$not: {$gt: 2}}});
{ "_id" : ObjectId("506eeefe1ecbcd6111672248"), "name" : "a", "data" : 1 }
>
bye

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