[SERVER-14369] Bug when sorting on a date attribute which is hash indexed Created: 26/Jun/14  Updated: 10/Dec/14  Resolved: 26/Jun/14

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

Type: Bug Priority: Major - P3
Reporter: João Soares Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-13899 "Whole index scan" query solutions ca... Closed
Operating System: ALL
Steps To Reproduce:

Create the sample collection with the hashed index an a date:

use sample_collection
db.sample_collection.insert({ key: 'value1', timestamp: new Date(2014,0,3) })
db.sample_collection.insert({ key: 'value2', timestamp: new Date(2014,0,1) })
db.sample_collection.insert({ key: 'value3', timestamp: new Date(2014,0,5) })
db.sample_collection.insert({ key: 'value4', timestamp: new Date(2014,0,2) })
db.sample_collection.insert({ key: 'value5', timestamp: new Date(2014,0,4) })

Create the hashed index

db.sample_collection.ensureIndex({ timestamp: "hashed" })

{ "_id" : ObjectId("53ac8730176132b326c61fa7"), "key" : "value1", "timestamp" : ISODate("2014-01-03T05:00:00Z") }
{ "_id" : ObjectId("53ac8731176132b326c61fab"), "key" : "value5", "timestamp" : ISODate("2014-01-04T05:00:00Z") }
{ "_id" : ObjectId("53ac8730176132b326c61fa9"), "key" : "value3", "timestamp" : ISODate("2014-01-05T05:00:00Z") }
{ "_id" : ObjectId("53ac8730176132b326c61faa"), "key" : "value4", "timestamp" : ISODate("2014-01-02T05:00:00Z") }
{ "_id" : ObjectId("53ac8730176132b326c61fa8"), "key" : "value2", "timestamp" : ISODate("2014-01-01T05:00:00Z") }

Now just make a query with a sort by timestamp:

db.sample_collection.find({}).sort({timestamp: 1 })

You can even see which values are actually being used for the sorting if you project out the _id and just select the timestamp:

db.sample_collection.find({}, { _id: 0, timestamp: 1 }).sort({ timestamp: 1 })

{ "timestamp" : NumberLong("-6917732993018730184") }
{ "timestamp" : NumberLong("-165404006621635642") }
{ "timestamp" : NumberLong("337081226167495347") }
{ "timestamp" : NumberLong("8104989996664184399") }
{ "timestamp" : NumberLong("9096888038977474173") }

Participants:

 Description   

The bug is pretty straight forward to explain an replicate.
Basically if you have a collection with a date attribute and you have a hashed index for that attribute, when o try to make simple find query sorting by that attribute MongoDB just sorts using the hashed index instead of sorting by the date itself.



 Comments   
Comment by João Soares [ 26/Jun/14 ]

Sure thing, it fixes the problem. I should have checked better for a duplicate. Thanks for the quick response, you can resolve this.

Comment by J Rassi [ 26/Jun/14 ]

Sure, not a problem. Assuming the issue will disappear after upgrading, I'll resolve this as a dup of SERVER-13899. If you continue to encounter the issue after upgrading, please re-open this ticket.

Comment by João Soares [ 26/Jun/14 ]

Jason Rassi: you are right I'm actually using 2.6.1, so I can only confirm it fails at 2.6.1, sorry about that. I'll upgrade and check if it fails at 2.6.3 and let you know.

Comment by J Rassi [ 26/Jun/14 ]

(I ask as this looks suspiciously similar to SERVER-13899, which was fixed for 2.6.2)

Comment by J Rassi [ 26/Jun/14 ]

jasoares: I can't reproduce this issue. You listed "affects version" as 2.6.3, but I suspect you're running an older version of the server; can you post the output of running db.version()?

Comment by João Soares [ 26/Jun/14 ]

I just forgot to add the explain output:

db.sample_collection.find({}).sort({timestamp: 1 }).explain()
{
	"cursor" : "BtreeCursor timestamp_hashed",
	"isMultiKey" : false,
	"n" : 5,
	"nscannedObjects" : 5,
	"nscanned" : 5,
	"nscannedObjectsAllPlans" : 5,
	"nscannedAllPlans" : 5,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"timestamp" : [
			[
				{
					"$minElement" : 1
				},
				{
					"$maxElement" : 1
				}
			]
		]
	},
	"server" : "jasoares-mbp.local:27010",
	"filterSet" : false,
	"millis" : 0
}

Comment by João Soares [ 26/Jun/14 ]

Thanks Thomas. By the way I've been on MongoDB World for the last 2 days in NY and spoke about this with Mathias Stearn and he assured me this was a bug.

Comment by Thomas Rueckstiess [ 26/Jun/14 ]

No problem. For the future, you can use {code} ... {code} for codeblocks.

Comment by João Soares [ 26/Jun/14 ]

I was attempting to proper format the steps to reproduce with monospaced but it didn't work and now I'm unable to edit it, can someone with more permissions edit it or just give me permission to do it? Sorry about it not being that clear atm.

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