[SERVER-8903] Query parameters are not being honored Created: 07/Mar/13  Updated: 09/Mar/13  Resolved: 09/Mar/13

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.0.4, 2.2.2
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Carl Youngblood Assignee: Aaron Staple
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

Please see queries in description.

Participants:

 Description   

> db.challenge_157_daily_scores.find(
...

{ ... 'g': 1, ... 'd':ISODate("2013-03-07T00:00:00Z") ... }

,
...

{ ... 'i':1, ... 'g':1 ... }

).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:100000, ... i:1, ... t:'u' ... }

);

{ "g" : 1, "i" : 24579031 } { "g" : 1, "i" : 24579049 } { "g" : 1, "i" : 24579022 } { "g" : 1, "i" : 24579013 } { "g" : 1, "i" : 24579033 } { "g" : 1, "i" : 24579045 } { "g" : 1, "i" : 24579014 } { "g" : 1, "i" : 24579032 } { "g" : 1, "i" : 24579038 } { "g" : 1, "i" : 24579019 }

Looks good!

> db.challenge_157_daily_scores.find(
...

{ ... 'g': 1, ... 'd':ISODate("2013-03-07T00:00:00Z") ... }

,
...

{ ... 'i':1, ... 'g':1 ... }

).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, "i" : 24579031 } { "g" : 2, "i" : 24579049 } { "g" : 2, "i" : 24579033 } { "g" : 2, "i" : 24579045 }

Looks bad! g should be 1.

> db.challenge_157_daily_scores.find(
...

{ ... 'g': 1, ... 'd':ISODate("2013-03-07T00:00:00Z") ... }

,
...

{ ... 'i':1, ... 'g':1 ... }

).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' ... }

);

{ "g" : 1, "i" : 24579045 } { "g" : 1, "i" : 24579014 } { "g" : 1, "i" : 24579032 } { "g" : 1, "i" : 24579038 } { "g" : 1, "i" : 24579019 } { "g" : 2, "i" : 24579031 } { "g" : 2, "i" : 24579049 } { "g" : 2, "i" : 24579033 }

Is it running over somehow? Why doesn't it stop before g = 2.



 Comments   
Comment by Carl Youngblood [ 09/Mar/13 ]

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!

Comment by Aaron Staple [ 08/Mar/13 ]

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.

Comment by Carl Youngblood [ 08/Mar/13 ]

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 ]
 

Comment by Carl Youngblood [ 08/Mar/13 ]

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

Comment by Aaron Staple [ 07/Mar/13 ]

Carl, can you provide a complete test, with full query and incorrectly returned document, describing your issue?

Comment by Carl Youngblood [ 07/Mar/13 ]

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.

Comment by Carl Youngblood [ 07/Mar/13 ]

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.