[SERVER-13406] Query misses results with .sort() Created: 29/Mar/14  Updated: 11/Jul/16  Resolved: 31/Mar/14

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 2.6.0-rc2
Fix Version/s: 2.6.0-rc3

Type: Bug Priority: Critical - P2
Reporter: Cailin Nelson Assignee: hari.khalsa@10gen.com
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Operating System: ALL
Participants:

 Description   

Please see the queries below performed on 2.6.0-rc2. When the query includes a .sort() no records are returned. When the .sort() is removed, the expected records are returned. The .explain() on the sorted version looks quite strange to me.

> db.version()
2.6.0-rc2
> db.config.users.find({},{un:1,pe:1,cre:1})
{ "_id" : ObjectId("53362b393004cde05e1ae35c"), "un" : "cailin.nelson@10gen.com", "cre" : ISODate("2014-03-29T02:08:57.062Z"), "pe" : "cailin.nelson@10gen.com" }
{ "_id" : ObjectId("533633aa300495bc76c058cb"), "un" : "cowboy@johnandcailin.com", "cre" : ISODate("2014-03-29T02:44:58.941Z"), "pe" : "cowboy@johnandcailin.com" }
{ "_id" : ObjectId("533633b2300495bc76c058d1"), "un" : "alberta@johnandcailin.com", "cre" : ISODate("2014-03-29T02:45:06.299Z"), "pe" : "alberta@johnandcailin.com" }
> db.config.users.find({$or:[{un: /cailin/},{pe:/cailin/}]},{un:1}).sort({cre:-1}).limit(10).skip(0).count()
3
> db.config.users.find({$or:[{un: /cailin/},{pe:/cailin/}]},{un:1}).sort({cre:-1}).limit(10).skip(0)
> db.config.users.find({$or:[{un: /cailin/},{pe:/cailin/}]},{un:1}).sort({cre:-1}).limit(10).skip(0).explain()
{
	"cursor" : "BtreeCursor createdIdx reverse",
	"isMultiKey" : false,
	"n" : 0,
	"nscannedObjects" : 0,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 0,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"cre" : [
			[
				/cailin/,
				/cailin/
			],
			[
				{
					
				},
				""
			]
		]
	},
	"server" : "cailinmac:27017",
	"filterSet" : false
}
> db.config.users.find({$or:[{un: /cailin/},{pe:/cailin/}]},{un:1}).limit(10).skip(0)
{ "_id" : ObjectId("533633b2300495bc76c058d1"), "un" : "alberta@johnandcailin.com" }
{ "_id" : ObjectId("53362b393004cde05e1ae35c"), "un" : "cailin.nelson@10gen.com" }
{ "_id" : ObjectId("533633aa300495bc76c058cb"), "un" : "cowboy@johnandcailin.com" }
> 

If I downgrade to 2.4.6, then the expected results are returned with the .sort()

[scripts] (feature_mmsp_1468)$ mongo
MongoDB shell version: 2.4.6
connecting to: test
> db.version()
2.4.6
> use mmsdbconfig
switched to db mmsdbconfig
> db.config.users.find({},{un:1,pe:1,cre:1})
{ "_id" : ObjectId("53362b393004cde05e1ae35c"), "un" : "cailin.nelson@10gen.com", "cre" : ISODate("2014-03-29T02:08:57.062Z"), "pe" : "cailin.nelson@10gen.com" }
{ "_id" : ObjectId("533633aa300495bc76c058cb"), "un" : "cowboy@johnandcailin.com", "cre" : ISODate("2014-03-29T02:44:58.941Z"), "pe" : "cowboy@johnandcailin.com" }
{ "_id" : ObjectId("533633b2300495bc76c058d1"), "un" : "alberta@johnandcailin.com", "cre" : ISODate("2014-03-29T02:45:06.299Z"), "pe" : "alberta@johnandcailin.com" }
> db.config.users.find({$or:[{un: /cailin/},{pe:/cailin/}]},{un:1}).sort({cre:-1}).limit(10).skip(0)
{ "_id" : ObjectId("533633b2300495bc76c058d1"), "un" : "alberta@johnandcailin.com" }
{ "_id" : ObjectId("533633aa300495bc76c058cb"), "un" : "cowboy@johnandcailin.com" }
{ "_id" : ObjectId("53362b393004cde05e1ae35c"), "un" : "cailin.nelson@10gen.com" }



 Comments   
Comment by Cailin Nelson [ 31/Mar/14 ]

Thanks!

Comment by Githook User [ 31/Mar/14 ]

Author:

{u'username': u'hkhalsa', u'name': u'Hari Khalsa', u'email': u'hkhalsa@10gen.com'}

Message: SERVER-13406 don't extract invalid index assignments from OR subplan when it indexed sorts
Branch: v2.6
https://github.com/mongodb/mongo/commit/9de22c408e7080c8c35257302c416ca4941fa0ec

Comment by Githook User [ 31/Mar/14 ]

Author:

{u'username': u'hkhalsa', u'name': u'Hari Khalsa', u'email': u'hkhalsa@10gen.com'}

Message: SERVER-13406 add debugging for OR sub-planner
Branch: v2.6
https://github.com/mongodb/mongo/commit/eea49e2e48202c8a05d3ffc8b91143d6da2f7126

Comment by Githook User [ 31/Mar/14 ]

Author:

{u'username': u'hkhalsa', u'name': u'Hari Khalsa', u'email': u'hkhalsa@10gen.com'}

Message: SERVER-13406 don't extract invalid index assignments from OR subplan when it indexed sorts
Branch: master
https://github.com/mongodb/mongo/commit/91b9d4ff3e913f74ee75610db2fa6d448b4df0bf

Comment by Githook User [ 31/Mar/14 ]

Author:

{u'username': u'hkhalsa', u'name': u'Hari Khalsa', u'email': u'hkhalsa@10gen.com'}

Message: SERVER-13406 add debugging for OR sub-planner
Branch: master
https://github.com/mongodb/mongo/commit/5d3070ecd0171700ea30ff7bd698d8b677b50bb6

Comment by Cailin Nelson [ 29/Mar/14 ]

> db.version();
2.6.0-rc2
> db.config.users.find({$or:[{un: /cailin/},{pe:/cailin/}]},{un:1}).sort({cre:-1}).limit(10).skip(0).explain(true)
{
	"cursor" : "BtreeCursor createdIdx reverse",
	"isMultiKey" : false,
	"n" : 0,
	"nscannedObjects" : 0,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 0,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"cre" : [
			[
				/cailin/,
				/cailin/
			],
			[
				{
					
				},
				""
			]
		]
	},
	"allPlans" : [
		{
			"cursor" : "BtreeCursor createdIdx reverse",
			"isMultiKey" : false,
			"n" : 0,
			"nscannedObjects" : 0,
			"nscanned" : 1,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"cre" : [
					[
						/cailin/,
						/cailin/
					],
					[
						{
							
						},
						""
					]
				]
			}
		}
	],
	"server" : "cailinmac:27017",
	"filterSet" : false,
	"stats" : {
		"type" : "LIMIT",
		"works" : 2,
		"yields" : 0,
		"unyields" : 0,
		"invalidates" : 0,
		"advanced" : 0,
		"needTime" : 0,
		"needFetch" : 0,
		"isEOF" : 1,
		"children" : [
			{
				"type" : "PROJECTION",
				"works" : 1,
				"yields" : 0,
				"unyields" : 0,
				"invalidates" : 0,
				"advanced" : 0,
				"needTime" : 0,
				"needFetch" : 0,
				"isEOF" : 1,
				"children" : [
					{
						"type" : "FETCH",
						"works" : 1,
						"yields" : 0,
						"unyields" : 0,
						"invalidates" : 0,
						"advanced" : 0,
						"needTime" : 0,
						"needFetch" : 0,
						"isEOF" : 1,
						"alreadyHasObj" : 0,
						"forcedFetches" : 0,
						"matchTested" : 0,
						"children" : [
							{
								"type" : "IXSCAN",
								"works" : 1,
								"yields" : 0,
								"unyields" : 0,
								"invalidates" : 0,
								"advanced" : 0,
								"needTime" : 0,
								"needFetch" : 0,
								"isEOF" : 1,
								"keyPattern" : "{ cre: 1 }",
								"boundsVerbose" : "field #0['cre']: [/cailin/, /cailin/], ({}, \"\"]",
								"isMultiKey" : 0,
								"yieldMovedCursor" : 0,
								"dupsTested" : 0,
								"dupsDropped" : 0,
								"seenInvalidated" : 0,
								"matchTested" : 0,
								"keysExamined" : 1,
								"children" : [ ]
							}
						]
					}
				]
			}
		]
	}
}
> db.config.users.find({$or:[{un: /cailin/},{pe:/cailin/}]},{un:1}).limit(10).skip(0).explain(true)
{
	"clauses" : [
		{
			"cursor" : "BtreeCursor usernameIdx",
			"isMultiKey" : false,
			"n" : 3,
			"nscannedObjects" : 3,
			"nscanned" : 3,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"un" : [
					[
						"",
						{
							
						}
					],
					[
						/cailin/,
						/cailin/
					]
				]
			}
		},
		{
			"cursor" : "BtreeCursor primaryEmailIdx",
			"isMultiKey" : false,
			"n" : 3,
			"nscannedObjects" : 3,
			"nscanned" : 3,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"pe" : [
					[
						"",
						{
							
						}
					],
					[
						/cailin/,
						/cailin/
					]
				]
			}
		}
	],
	"cursor" : "QueryOptimizerCursor",
	"n" : 3,
	"nscannedObjects" : 6,
	"nscanned" : 6,
	"nscannedObjectsAllPlans" : 6,
	"nscannedAllPlans" : 6,
	"scanAndOrder" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"allPlans" : [
		{
			"clauses" : [
				{
					"cursor" : "BtreeCursor usernameIdx",
					"isMultiKey" : false,
					"n" : 3,
					"nscannedObjects" : 3,
					"nscanned" : 3,
					"scanAndOrder" : false,
					"indexOnly" : false,
					"nChunkSkips" : 0,
					"indexBounds" : {
						"un" : [
							[
								"",
								{
									
								}
							],
							[
								/cailin/,
								/cailin/
							]
						]
					}
				},
				{
					"cursor" : "BtreeCursor primaryEmailIdx",
					"isMultiKey" : false,
					"n" : 3,
					"nscannedObjects" : 3,
					"nscanned" : 3,
					"scanAndOrder" : false,
					"indexOnly" : false,
					"nChunkSkips" : 0,
					"indexBounds" : {
						"pe" : [
							[
								"",
								{
									
								}
							],
							[
								/cailin/,
								/cailin/
							]
						]
					}
				}
			],
			"cursor" : "QueryOptimizerCursor",
			"n" : 3,
			"nscannedObjects" : 6,
			"nscanned" : 6,
			"scanAndOrder" : false,
			"nChunkSkips" : 0
		}
	],
	"server" : "cailinmac:27017",
	"filterSet" : false,
	"stats" : {
		"type" : "LIMIT",
		"works" : 8,
		"yields" : 0,
		"unyields" : 0,
		"invalidates" : 0,
		"advanced" : 3,
		"needTime" : 4,
		"needFetch" : 0,
		"isEOF" : 1,
		"children" : [
			{
				"type" : "PROJECTION",
				"works" : 8,
				"yields" : 0,
				"unyields" : 0,
				"invalidates" : 0,
				"advanced" : 3,
				"needTime" : 0,
				"needFetch" : 0,
				"isEOF" : 1,
				"children" : [
					{
						"type" : "FETCH",
						"works" : 8,
						"yields" : 0,
						"unyields" : 0,
						"invalidates" : 0,
						"advanced" : 3,
						"needTime" : 4,
						"needFetch" : 0,
						"isEOF" : 1,
						"alreadyHasObj" : 0,
						"forcedFetches" : 0,
						"matchTested" : 0,
						"children" : [
							{
								"type" : "OR",
								"works" : 8,
								"yields" : 0,
								"unyields" : 0,
								"invalidates" : 0,
								"advanced" : 3,
								"needTime" : 4,
								"needFetch" : 0,
								"isEOF" : 1,
								"dupsTested" : 6,
								"dupsDropped" : 3,
								"locsForgotten" : 0,
								"matchTested_0" : 0,
								"matchTested_1" : 0,
								"children" : [
									{
										"type" : "IXSCAN",
										"works" : 4,
										"yields" : 0,
										"unyields" : 0,
										"invalidates" : 0,
										"advanced" : 3,
										"needTime" : 0,
										"needFetch" : 0,
										"isEOF" : 1,
										"keyPattern" : "{ un: 1 }",
										"boundsVerbose" : "field #0['un']: [\"\", {}), [/cailin/, /cailin/]",
										"isMultiKey" : 0,
										"yieldMovedCursor" : 0,
										"dupsTested" : 0,
										"dupsDropped" : 0,
										"seenInvalidated" : 0,
										"matchTested" : 3,
										"keysExamined" : 3,
										"children" : [ ]
									},
									{
										"type" : "IXSCAN",
										"works" : 4,
										"yields" : 0,
										"unyields" : 0,
										"invalidates" : 0,
										"advanced" : 3,
										"needTime" : 0,
										"needFetch" : 0,
										"isEOF" : 1,
										"keyPattern" : "{ pe: 1 }",
										"boundsVerbose" : "field #0['pe']: [\"\", {}), [/cailin/, /cailin/]",
										"isMultiKey" : 0,
										"yieldMovedCursor" : 0,
										"dupsTested" : 0,
										"dupsDropped" : 0,
										"seenInvalidated" : 0,
										"matchTested" : 3,
										"keysExamined" : 3,
										"children" : [ ]
									}
								]
							}
						]
					}
				]
			}
		]
	}
}
> db.config.users.find({$or:[{un: /cailin/},{pe:/cailin/}]}).sort({cre:-1}).limit(10).skip(0).explain(true)
{
	"cursor" : "BtreeCursor createdIdx reverse",
	"isMultiKey" : false,
	"n" : 0,
	"nscannedObjects" : 0,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 0,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"cre" : [
			[
				/cailin/,
				/cailin/
			],
			[
				{
					
				},
				""
			]
		]
	},
	"allPlans" : [
		{
			"cursor" : "BtreeCursor createdIdx reverse",
			"isMultiKey" : false,
			"n" : 0,
			"nscannedObjects" : 0,
			"nscanned" : 1,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"cre" : [
					[
						/cailin/,
						/cailin/
					],
					[
						{
							
						},
						""
					]
				]
			}
		}
	],
	"server" : "cailinmac:27017",
	"filterSet" : false,
	"stats" : {
		"type" : "LIMIT",
		"works" : 2,
		"yields" : 0,
		"unyields" : 0,
		"invalidates" : 0,
		"advanced" : 0,
		"needTime" : 0,
		"needFetch" : 0,
		"isEOF" : 1,
		"children" : [
			{
				"type" : "FETCH",
				"works" : 1,
				"yields" : 0,
				"unyields" : 0,
				"invalidates" : 0,
				"advanced" : 0,
				"needTime" : 0,
				"needFetch" : 0,
				"isEOF" : 1,
				"alreadyHasObj" : 0,
				"forcedFetches" : 0,
				"matchTested" : 0,
				"children" : [
					{
						"type" : "IXSCAN",
						"works" : 1,
						"yields" : 0,
						"unyields" : 0,
						"invalidates" : 0,
						"advanced" : 0,
						"needTime" : 0,
						"needFetch" : 0,
						"isEOF" : 1,
						"keyPattern" : "{ cre: 1 }",
						"boundsVerbose" : "field #0['cre']: [/cailin/, /cailin/], ({}, \"\"]",
						"isMultiKey" : 0,
						"yieldMovedCursor" : 0,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"seenInvalidated" : 0,
						"matchTested" : 0,
						"keysExamined" : 1,
						"children" : [ ]
					}
				]
			}
		]
	}
}
> db.config.users.find({$or:[{un: /cailin/},{pe:/cailin/}]}).limit(10).skip(0).explain(true)
{
	"clauses" : [
		{
			"cursor" : "BtreeCursor usernameIdx",
			"isMultiKey" : false,
			"n" : 3,
			"nscannedObjects" : 3,
			"nscanned" : 3,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"un" : [
					[
						"",
						{
							
						}
					],
					[
						/cailin/,
						/cailin/
					]
				]
			}
		},
		{
			"cursor" : "BtreeCursor primaryEmailIdx",
			"isMultiKey" : false,
			"n" : 3,
			"nscannedObjects" : 3,
			"nscanned" : 3,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"pe" : [
					[
						"",
						{
							
						}
					],
					[
						/cailin/,
						/cailin/
					]
				]
			}
		}
	],
	"cursor" : "QueryOptimizerCursor",
	"n" : 3,
	"nscannedObjects" : 6,
	"nscanned" : 6,
	"nscannedObjectsAllPlans" : 6,
	"nscannedAllPlans" : 6,
	"scanAndOrder" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"allPlans" : [
		{
			"clauses" : [
				{
					"cursor" : "BtreeCursor usernameIdx",
					"isMultiKey" : false,
					"n" : 3,
					"nscannedObjects" : 3,
					"nscanned" : 3,
					"scanAndOrder" : false,
					"indexOnly" : false,
					"nChunkSkips" : 0,
					"indexBounds" : {
						"un" : [
							[
								"",
								{
									
								}
							],
							[
								/cailin/,
								/cailin/
							]
						]
					}
				},
				{
					"cursor" : "BtreeCursor primaryEmailIdx",
					"isMultiKey" : false,
					"n" : 3,
					"nscannedObjects" : 3,
					"nscanned" : 3,
					"scanAndOrder" : false,
					"indexOnly" : false,
					"nChunkSkips" : 0,
					"indexBounds" : {
						"pe" : [
							[
								"",
								{
									
								}
							],
							[
								/cailin/,
								/cailin/
							]
						]
					}
				}
			],
			"cursor" : "QueryOptimizerCursor",
			"n" : 3,
			"nscannedObjects" : 6,
			"nscanned" : 6,
			"scanAndOrder" : false,
			"nChunkSkips" : 0
		}
	],
	"server" : "cailinmac:27017",
	"filterSet" : false,
	"stats" : {
		"type" : "LIMIT",
		"works" : 8,
		"yields" : 0,
		"unyields" : 0,
		"invalidates" : 0,
		"advanced" : 3,
		"needTime" : 4,
		"needFetch" : 0,
		"isEOF" : 1,
		"children" : [
			{
				"type" : "FETCH",
				"works" : 8,
				"yields" : 0,
				"unyields" : 0,
				"invalidates" : 0,
				"advanced" : 3,
				"needTime" : 4,
				"needFetch" : 0,
				"isEOF" : 1,
				"alreadyHasObj" : 0,
				"forcedFetches" : 0,
				"matchTested" : 0,
				"children" : [
					{
						"type" : "OR",
						"works" : 8,
						"yields" : 0,
						"unyields" : 0,
						"invalidates" : 0,
						"advanced" : 3,
						"needTime" : 4,
						"needFetch" : 0,
						"isEOF" : 1,
						"dupsTested" : 6,
						"dupsDropped" : 3,
						"locsForgotten" : 0,
						"matchTested_0" : 0,
						"matchTested_1" : 0,
						"children" : [
							{
								"type" : "IXSCAN",
								"works" : 4,
								"yields" : 0,
								"unyields" : 0,
								"invalidates" : 0,
								"advanced" : 3,
								"needTime" : 0,
								"needFetch" : 0,
								"isEOF" : 1,
								"keyPattern" : "{ un: 1 }",
								"boundsVerbose" : "field #0['un']: [\"\", {}), [/cailin/, /cailin/]",
								"isMultiKey" : 0,
								"yieldMovedCursor" : 0,
								"dupsTested" : 0,
								"dupsDropped" : 0,
								"seenInvalidated" : 0,
								"matchTested" : 3,
								"keysExamined" : 3,
								"children" : [ ]
							},
							{
								"type" : "IXSCAN",
								"works" : 4,
								"yields" : 0,
								"unyields" : 0,
								"invalidates" : 0,
								"advanced" : 3,
								"needTime" : 0,
								"needFetch" : 0,
								"isEOF" : 1,
								"keyPattern" : "{ pe: 1 }",
								"boundsVerbose" : "field #0['pe']: [\"\", {}), [/cailin/, /cailin/]",
								"isMultiKey" : 0,
								"yieldMovedCursor" : 0,
								"dupsTested" : 0,
								"dupsDropped" : 0,
								"seenInvalidated" : 0,
								"matchTested" : 3,
								"keysExamined" : 3,
								"children" : [ ]
							}
						]
					}
				]
			}
		]
	}
}
> 

Comment by Scott Hernandez (Inactive) [ 29/Mar/14 ]

Please include the explain in verbose mode (pass in true/1) for:

  • with/out sort query
  • without the projection

http://docs.mongodb.org/manual/reference/method/cursor.explain/

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