[SERVER-10749] Query results differ depending on the utilized index Created: 11/Sep/13  Updated: 10/Dec/14  Resolved: 16/Sep/13

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 2.4.3
Fix Version/s: None

Type: Bug Priority: Critical - P2
Reporter: Stefan Wójcik Assignee: Unassigned
Resolution: Duplicate Votes: 2
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-3372 Allow indexing fields of arbitrary le... Closed
duplicates SERVER-5290 fail to insert docs with fields too l... Closed
duplicates SERVER-2677 Allow count to work with query hints Closed
Operating System: ALL
Participants:

 Description   

I haven't spotted any indexing errors, but today I noticed a very odd behavior:

> db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).count()
2
> db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).hint({ _id: 1 }).count()
2
 
 
> db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).toArray().length
2
> db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).hint({ _id: 1 }).toArray().length
14

There are two issues I see here:
1. (minor) count doesn't respect the hint (had to resort to toArray().length)
2. (major) the default index utilized for this query is broken (doesn't return all the docs that match the query).

A bit more insight into the data:

Indexes on the collection:

> db.saved_search.getIndexes()
[
	{
		"v" : 1,
		"key" : {
			"_id" : 1
		},
		"ns" : "closeio.saved_search",
		"name" : "_id_"
	},
	{
		"v" : 1,
		"key" : {
			"organization" : 1,
			"user" : 1,
			"query" : 1,
			"is_shared" : 1,
			"date_created" : -1
		},
		"ns" : "closeio.saved_search",
		"name" : "organization_1_user_1_query_1_is_shared_1_date_created_-1",
		"background" : false,
		"dropDups" : false
	}
]

One of the returned docs:

{
		"_id" : "save_m0n9GBfilJ3rovhDZVET9bPyM1nMu0a31wtWaofJtDy",
		"date_created" : ISODate("2013-08-22T07:18:11.583Z"),
		"date_updated" : ISODate("2013-09-03T14:25:18.482Z"),
		"is_shared" : false,
		"name" : "Synchs streak",
		"organization" : DBRef("organization", "orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8"),
		"query" : "custom.Stage:Sync/Implemented not opportunity_status:\"Synch/ Implemented\"",
		"user" : DBRef("user", "user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD")
	}

One of the docs that weren't returned (but should've been):

{
		"_id" : "save_OgHsF7KrU8kD5J1SoTU7jLtutAZQNNIZJ7NmDOmW9NK",
		"is_shared" : false,
		"date_updated" : ISODate("2013-09-11T14:20:09.108Z"),
		"query" : "country:uk and description:ebay not ( \"custom.Lead Owner\":\"Bagdat Baimagambetov\" \"custom.Lead Owner\":\"Brian OLeary\" \"custom.Lead Owner\":\"Chris Marley\" \"custom.Lead Owner\":\"Duncan Anderson\" \"custom.Lead Owner\":\"Gina Karlikoff\" \"custom.Lead Owner\":\"Jim Gregory\" \"custom.Lead Owner\":\"John Nuclear\" \"custom.Lead Owner\":\"Liam Delahunty\" \"custom.Lead Owner\":\"Louis McCarthy\" \"custom.Lead Owner\":\"Matteo Pedrioli\" \"custom.Lead Owner\":\"Patrice Meiner\" \"custom.Lead Owner\":\"Rory Kierans\" \"custom.Lead Owner\":\"Rory Reilly\" \"custom.Lead Owner\":\"Sujeet Gorahava\" \"custom.Lead Owner\":\"trial - jim\" \"custom.Lead Owner\":Trial-Dave \"custom.Lead Owner\":trial-joana \"custom.Lead Owner\":trial-matthew \"custom.Lead Owner\":triál-dave ) not lead_status:\"No Contact Details\" not lead_status:\"Won and then Lost\" not opportunity_status:\"eBay Won\" not opportunity_status:\"Won Web Shop\"",
		"user" : DBRef("user", "user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD"),
		"date_created" : ISODate("2013-09-11T14:20:09.108Z"),
		"organization" : DBRef("organization", "orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8"),
		"name" : "UK leads"
	}



 Comments   
Comment by Daniel Pasette (Inactive) [ 16/Sep/13 ]

The major problem here is that the index key is too long to be indexed for the document which is not returned and thus it is not in the index at all. When you hint to use the _id index, it forces a table scan and is able to find all the documents. See: SERVER-5290 and SERVER-3372.

When it encounters a long key mongod will log an error message something like:

Mon Sep 16 03:32:05.949 [conn1]  dbref.system.indexes Btree::insert: key too large to index, skipping dbref.a.$organization_1_user_1_query_1_is_shared_1_date_created_-1 1054 { : { $ref: "organization", $id: "orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8" }, : { $ref: "user", $id: "user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD" }, : "country:uk and description:ebay not ( "custom.Lead Owner":"Bagdat Baimagambetov" "custom.Lead Owner":"Brian OLeary" "custom.Lead Owner":"Chris Marley"...", : false, : new Date(1378909209108) }
Mon Sep 16 03:32:05.949 [conn1] warning: not all entries were added to the index, probably some keys were too large

The minor problem you found is due to the fact that count() is a command, not an operation on the cursor. Commands do not support hint(), so the command is using the index with missing entries. See SERVER-2677.

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