|
You are of course correct. I had not recognized that using $min and $max operators clobbers the indexBounds set by the find spec in the find() call, but in retrospect of course they must. Thank you for the explanation, sorry about the false bug report!
|
|
Hi Carl,
I think the behavior you have reported so far falls into the works as designed category. I'll try to respond to each behavior you have pointed out:
db.test.find({g:1,d:ISODate('2013-01-01')}).hint({g:1,d:-1,s:-1,i:1,t:1}).sort({g:1,d:-1,s:-1,i:1,t:1})._addSpecial('$returnKey',true).min({g:1,d:ISODate('2013-01-01'),s:700,i:0,t:0})
|
…
|
{ "g" : 2, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 750, "i" : 0, "t" : 0 } // This record should not have been returned because g should be pinned to 1 and 750 > 700
|
With this query you've told mongo - scan the index range from the min key specified until the end of the index is reached. For each index entry corresponding to a document matching your query, return the index entry.
The index entry above is returned because it compares greater than the key provided to min(). ('g' appears in the index spec before 's', so the key is greater because 'g' > 1 even though 's' > 700). And the document this index entry points to matches the query because the document has a 'g' value of 2 and a correct 'd' value.
{ "g" : 2, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 750, "i" : 0, "t" : 0 } // This record should not have been returned because g should be pinned to 1 and 750 > 400
|
{ "g" : 2, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 750, "i" : 0, "t" : 0 } // This record should not have been returned because g should be pinned to 1 and 750 > 300
|
{ "g" : 2, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 350, "i" : 0, "t" : 0 } // This record should not have been returned because g should be pinned to 1 and 350 > 300
|
The above are returned for similar reasons.
// The following query is behaving incorrectly. nscanned should be 3
|
db.test.find({g:1}).min({g:1,d:ISODate('2013-01-01'),s:650,i:0,t:0}).explain()
|
The above nscanned results primarily because min() / max() parameters right now explicitly override the index bounds that would normally be used for a query. If you would prefer to scan fewer index entries, you could add a max() to your query.
|
|
Here it is broken down logically.
// All documents in the collection. I have assigned them a numeric identifier starting with 1 to refer back to them later
|
db.test.find()
|
{ "_id" : ObjectId("513940560518ed58a08d64bd"), "g" : [ 1 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 1000, "i" : 0, "t" : 0 } // 1
|
{ "_id" : ObjectId("513940650518ed58a08d64be"), "g" : [ 1 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 900, "i" : 0, "t" : 0 } // 2
|
{ "_id" : ObjectId("513940690518ed58a08d64bf"), "g" : [ 1 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 800, "i" : 0, "t" : 0 } // 3
|
{ "_id" : ObjectId("5139406d0518ed58a08d64c0"), "g" : [ 1 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 700, "i" : 0, "t" : 0 } // 4
|
{ "_id" : ObjectId("513940700518ed58a08d64c1"), "g" : [ 1 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 600, "i" : 0, "t" : 0 } // 5
|
{ "_id" : ObjectId("513940740518ed58a08d64c2"), "g" : [ 1 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 500, "i" : 0, "t" : 0 } // 6
|
{ "_id" : ObjectId("5139407b0518ed58a08d64c3"), "g" : [ 2 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 400, "i" : 0, "t" : 0 } // 7
|
{ "_id" : ObjectId("5139407e0518ed58a08d64c4"), "g" : [ 2 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 300, "i" : 0, "t" : 0 } // 8
|
{ "_id" : ObjectId("513940800518ed58a08d64c5"), "g" : [ 2 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 200, "i" : 0, "t" : 0 } // 9
|
{ "_id" : ObjectId("513940830518ed58a08d64c6"), "g" : [ 2 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 100, "i" : 0, "t" : 0 } // 10
|
{ "_id" : ObjectId("513941c50518ed58a08d64c7") } // 11
|
{ "_id" : ObjectId("513941f00518ed58a08d64c8"), "g" : [ 1, 2 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 750, "i" : 0, "t" : 0 } // 12
|
{ "_id" : ObjectId("513941f60518ed58a08d64c9"), "g" : [ 1, 2 ], "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 350, "i" : 0, "t" : 0 } // 13
|
|
// A symbolic representation of the index {g:1,d:-1,s:-1,i:1,t:1} using the numeric identifiers from above
|
1,20130101,1000,0,0 => [ 1 ]
|
1,20130101,900,0,0 => [ 2 ]
|
1,20130101,800,0,0 => [ 3 ]
|
1,20130101,750,0,0 => [ 12 ]
|
1,20130101,700,0,0 => [ 4 ]
|
1,20130101,600,0,0 => [ 5 ]
|
1,20130101,500,0,0 => [ 6 ]
|
1,20130101,350,0,0 => [ 13 ]
|
2,20130101,750,0,0 => [ 12 ]
|
2,20130101,400,0,0 => [ 7 ]
|
2,20130101,350,0,0 => [ 13 ]
|
2,20130101,300,0,0 => [ 8 ]
|
2,20130101,200,0,0 => [ 9 ]
|
2,20130101,100,0,0 => [ 10 ]
|
null => [ 11 ]
|
|
// This query is behaving correctly.
|
db.test.find().min({g:1,d:ISODate('2013-01-01'),s:650,i:0,t:0}).explain()
|
{
|
"cursor" : "BtreeCursor g_1_d_-1_s_-1_i_1_t_1",
|
"isMultiKey" : true,
|
"n" : 8,
|
"nscannedObjects" : 9,
|
"nscanned" : 9,
|
"nscannedObjectsAllPlans" : 9,
|
"nscannedAllPlans" : 9,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 0,
|
"indexBounds" : {
|
"start" : {
|
"g" : 1,
|
"d" : ISODate("2013-01-01T00:00:00Z"),
|
"s" : 650,
|
"i" : 0,
|
"t" : 0
|
},
|
"end" : {
|
"g" : {
|
"$maxElement" : 1
|
},
|
"d" : {
|
"$minElement" : 1
|
},
|
"s" : {
|
"$minElement" : 1
|
},
|
"i" : {
|
"$maxElement" : 1
|
},
|
"t" : {
|
"$maxElement" : 1
|
}
|
}
|
},
|
"server" : "Dog:27017"
|
}
|
|
// From the explain output nscanned: 9 we can intuit it is looking at the following index entries (symbolic index representation repeated from above)
|
1,20130101,1000,0,0 => [ 1 ]
|
1,20130101,900,0,0 => [ 2 ]
|
1,20130101,800,0,0 => [ 3 ]
|
1,20130101,750,0,0 => [ 12 ]
|
1,20130101,700,0,0 => [ 4 ]
|
1,20130101,600,0,0 => [ 5 ] // scanned
|
1,20130101,500,0,0 => [ 6 ] // scanned
|
1,20130101,350,0,0 => [ 13 ] // scanned
|
2,20130101,750,0,0 => [ 12 ] // scanned
|
2,20130101,400,0,0 => [ 7 ] // scanned
|
2,20130101,350,0,0 => [ 13 ] // scanned
|
2,20130101,300,0,0 => [ 8 ] // scanned
|
2,20130101,200,0,0 => [ 9 ] // scanned
|
2,20130101,100,0,0 => [ 10 ] // scanned
|
null => [ 11 ]
|
|
// The following query is behaving incorrectly. nscanned should be 3
|
db.test.find({g:1}).min({g:1,d:ISODate('2013-01-01'),s:650,i:0,t:0}).explain()
|
{
|
"cursor" : "BtreeCursor g_1_d_-1_s_-1_i_1_t_1",
|
"isMultiKey" : true,
|
"n" : 4,
|
"nscannedObjects" : 9,
|
"nscanned" : 9,
|
"nscannedObjectsAllPlans" : 9,
|
"nscannedAllPlans" : 9,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 0,
|
"indexBounds" : {
|
"start" : {
|
"g" : 1,
|
"d" : ISODate("2013-01-01T00:00:00Z"),
|
"s" : 650,
|
"i" : 0,
|
"t" : 0
|
},
|
"end" : {
|
"g" : {
|
"$maxElement" : 1
|
},
|
"d" : {
|
"$minElement" : 1
|
},
|
"s" : {
|
"$minElement" : 1
|
},
|
"i" : {
|
"$maxElement" : 1
|
},
|
"t" : {
|
"$maxElement" : 1
|
}
|
}
|
},
|
"server" : "Dog:27017"
|
}
|
|
// Again, the index representation from above but annoted for this query
|
1,20130101,1000,0,0 => [ 1 ]
|
1,20130101,900,0,0 => [ 2 ]
|
1,20130101,800,0,0 => [ 3 ]
|
1,20130101,750,0,0 => [ 12 ]
|
1,20130101,700,0,0 => [ 4 ]
|
1,20130101,600,0,0 => [ 5 ] // scanned, and should have been
|
1,20130101,500,0,0 => [ 6 ] // scanned, and should have been
|
1,20130101,350,0,0 => [ 13 ] // scanned, and should have been
|
2,20130101,750,0,0 => [ 12 ] // scanned, but should not have been
|
2,20130101,400,0,0 => [ 7 ] // scanned, but should not have been
|
2,20130101,350,0,0 => [ 13 ] // scanned, but should not have been
|
2,20130101,300,0,0 => [ 8 ] // scanned, but should not have been
|
2,20130101,200,0,0 => [ 9 ] // scanned, but should not have been
|
2,20130101,100,0,0 => [ 10 ] // scanned, but should not have been
|
null => [ 11 ]
|
|
|
|
Here are the full queries from another test. This is in a fresh installation of mongo 2.2.3, using database test, collection test. The output below is annotated where it is incorrect.
db.test.ensureIndex({i:1,t:1})
|
db.test.ensureIndex({d:-1,s:-1,i:1,t:1})
|
db.test.ensureIndex({g:1,d:-1,s:-1,i:1,t:1})
|
db.test.ensureIndex({i:1,t:1,d:-1})
|
db.test.stats()
|
{
|
"ns" : "test.test",
|
"count" : 0,
|
"size" : 0,
|
"storageSize" : 4096,
|
"numExtents" : 1,
|
"nindexes" : 5,
|
"lastExtentSize" : 4096,
|
"paddingFactor" : 1,
|
"systemFlags" : 1,
|
"userFlags" : 0,
|
"totalIndexSize" : 40880,
|
"indexSizes" : {
|
"_id_" : 8176,
|
"i_1_t_1" : 8176,
|
"d_-1_s_-1_i_1_t_1" : 8176,
|
"g_1_d_-1_s_-1_i_1_t_1" : 8176,
|
"i_1_t_1_d_-1" : 8176
|
},
|
"ok" : 1
|
}
|
db.test.insert({g:[1],d:ISODate('2013-01-01'),s:1000,i:0,t:0})
|
db.test.insert({g:[1],d:ISODate('2013-01-01'),s:900,i:0,t:0})
|
db.test.insert({g:[1],d:ISODate('2013-01-01'),s:800,i:0,t:0})
|
db.test.insert({g:[1],d:ISODate('2013-01-01'),s:700,i:0,t:0})
|
db.test.insert({g:[1],d:ISODate('2013-01-01'),s:600,i:0,t:0})
|
db.test.insert({g:[1],d:ISODate('2013-01-01'),s:500,i:0,t:0})
|
db.test.insert({g:[2],d:ISODate('2013-01-01'),s:400,i:0,t:0})
|
db.test.insert({g:[2],d:ISODate('2013-01-01'),s:300,i:0,t:0})
|
db.test.insert({g:[2],d:ISODate('2013-01-01'),s:200,i:0,t:0})
|
db.test.insert({g:[2],d:ISODate('2013-01-01'),s:100,i:0,t:0})
|
db.test.insert({g:[1,2],d:ISODate('2013-01-01'),s:750,i:0,t:0})
|
db.test.insert({g:[1,2],d:ISODate('2013-01-01'),s:350,i:0,t:0})
|
db.test.find({g:1,d:ISODate('2013-01-01')}).hint({g:1,d:-1,s:-1,i:1,t:1}).sort({g:1,d:-1,s:-1,i:1,t:1})._addSpecial('$returnKey',true).min({g:1,d:ISODate('2013-01-01'),s:1100,i:0,t:0})
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 1000, "i" : 0, "t" : 0 }
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 900, "i" : 0, "t" : 0 }
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 800, "i" : 0, "t" : 0 }
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 750, "i" : 0, "t" : 0 }
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 700, "i" : 0, "t" : 0 }
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 600, "i" : 0, "t" : 0 }
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 500, "i" : 0, "t" : 0 }
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 350, "i" : 0, "t" : 0 }
|
db.test.find({g:1,d:ISODate('2013-01-01')}).hint({g:1,d:-1,s:-1,i:1,t:1}).sort({g:1,d:-1,s:-1,i:1,t:1})._addSpecial('$returnKey',true).min({g:1,d:ISODate('2013-01-01'),s:700,i:0,t:0})
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 700, "i" : 0, "t" : 0 }
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 600, "i" : 0, "t" : 0 }
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 500, "i" : 0, "t" : 0 }
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 350, "i" : 0, "t" : 0 }
|
{ "g" : 2, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 750, "i" : 0, "t" : 0 } // This record should not have been returned because g should be pinned to 1 and 750 > 700
|
db.test.find({g:1,d:ISODate('2013-01-01')}).hint({g:1,d:-1,s:-1,i:1,t:1}).sort({g:1,d:-1,s:-1,i:1,t:1})._addSpecial('$returnKey',true).min({g:1,d:ISODate('2013-01-01'),s:400,i:0,t:0})
|
{ "g" : 1, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 350, "i" : 0, "t" : 0 }
|
{ "g" : 2, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 750, "i" : 0, "t" : 0 } // This record should not have been returned because g should be pinned to 1 and 750 > 400
|
db.test.find({g:1,d:ISODate('2013-01-01')}).hint({g:1,d:-1,s:-1,i:1,t:1}).sort({g:1,d:-1,s:-1,i:1,t:1})._addSpecial('$returnKey',true).min({g:1,d:ISODate('2013-01-01'),s:300,i:0,t:0})
|
{ "g" : 2, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 750, "i" : 0, "t" : 0 } // This record should not have been returned because g should be pinned to 1 and 750 > 300
|
{ "g" : 2, "d" : ISODate("2013-01-01T00:00:00Z"), "s" : 350, "i" : 0, "t" : 0 } // This record should not have been returned because g should be pinned to 1 and 350 > 300
|
|
|
Carl, can you provide a complete test, with full query and incorrectly returned document, describing your issue?
|
|
I just noticed that I projected i and g, making the problem less obvious from looking at the results. Please consider these updated examples with the full documents returned.
db.challenge_157_daily_scores.find(
...
{
... 'g': 1,
... 'd':ISODate("2013-03-07T00:00:00Z")
... }
).hint(
...
{
... g:1,
... d:-1,
... s:-1,
... i:1,
... t:1
... }
).sort(
...
{
... g:1,
... d:-1,
... s:-1,
... i:1,
... t:1
... }
)._addSpecial('$returnKey',true).min(
...
{
... g:1,
... d:ISODate("2013-03-07T00:00:00Z"),
... s:10000,
... i:1,
... t:'u'
... }
);
{ "g" : 1, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 1719, "i" : 24579031, "t" : "u" }
{ "g" : 1, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 1472, "i" : 24579049, "t" : "u" }
{ "g" : 1, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 1298, "i" : 24579022, "t" : "u" }
{ "g" : 1, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 1228, "i" : 24579013, "t" : "u" }
{ "g" : 1, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 1159, "i" : 24579033, "t" : "u" }
{ "g" : 1, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 814, "i" : 24579045, "t" : "u" }
{ "g" : 1, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 747, "i" : 24579014, "t" : "u" }
{ "g" : 1, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 487, "i" : 24579032, "t" : "u" }
{ "g" : 1, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 412, "i" : 24579038, "t" : "u" }
{ "g" : 1, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 164, "i" : 24579019, "t" : "u" }
Looks good!
db.challenge_157_daily_scores.find(
...
{
... 'g': 1,
... 'd':ISODate("2013-03-07T00:00:00Z")
... }
).hint(
...
{
... g:1,
... d:-1,
... s:-1,
... i:1,
... t:1
... }
).sort(
...
{
... g:1,
... d:-1,
... s:-1,
... i:1,
... t:1
... }
)._addSpecial('$returnKey',true).min(
...
{
... g:1,
... d:ISODate("2013-03-07T00:00:00Z"),
... s:1,
... i:1,
... t:'u'
... }
);
{ "g" : 2, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 1719, "i" : 24579031, "t" : "u" }
{ "g" : 2, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 1472, "i" : 24579049, "t" : "u" }
{ "g" : 2, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 1159, "i" : 24579033, "t" : "u" }
{ "g" : 2, "d" : ISODate("2013-03-07T00:00:00Z"), "s" : 814, "i" : 24579045, "t" : "u" }
Looks bad!
db.challenge_157_daily_scores.find(
{
'g': 1,
'd':ISODate("2013-03-07T00:00:00Z")
}
).hint(
{
g:1,
d:-1,
s:-1,
i:1,
t:1
}
).sort(
{
g:1,
d:-1,
s:-1,
i:1,
t:1
}
)._addSpecial('$returnKey',true).min(
{
g:1,
d:ISODate("2013-03-07T00:00:00Z"),
s:1000,
i:1,
t:'u'
}
);
Running over.
|
|
The ellipses everywhere are just from copy pasting from my shell, if it really bugs you I can edit them out.
|
Generated at Thu Feb 08 03:18:46 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.