I'm on latest version (2.6.3 I believe) of mongodb.
I have the following aggregate:
{
"$project": {
"summary": 1,
"priority": 1,
"sortDate": {
"$ifNull": [
"$targetDate",
"$deadlineDate"
]
}
}
},
{
"$sort": {
"priority": -1,
"sortDate": 1
}
}
Some objects in the collection have both targetDate and deadlineDate undefined.
Now here is the weird thing. If I run this, I get all eleven back:
The one with priority 3
One with priority 2 and no sortDate
Another one with priority 2 and no sortDate
The ones with priority 2 and sortDate (in correct order)
... etc priority 1
I understand why undefined is before the valued ones, even if I don't like it. But, if I add
{ "$limit": 2 }
to the aggregation pipeline, I'd expect to get back 1 and 2 from the above list. I get back 1 and 3 instead!!!! How?
This causes further weirdness down the line; if I use skip/limit to page, some items will appear on multiple pages, causing the clientside object tracker to freak out and crash, but I definitely nailed this sorting anomaly as the root cause of all those problems.
Let me clear up a couple of misunderstandings that I feel might be lurking based on the comments:
$limit is a pipeline stage that always cuts down the number of documents in the pipeline to at most the specified number. There's nothing tricky about it affecting what is "searched" versus "returned". It's just a pipeline stage that transforms one set of documents into another set by getting rid of all the ones > n in the order the documents are presented to it, when the limit parameter is n. Note that the order might not be very meaningful. We'll talk about the tricky part in a second :D
The result returned by the aggregation query with $limit isn't wrong; it's just different. Both have the same sort key, so you really can't complain if the two pipelines returned 123 and 132, respectively, and then limit 2 gives you 12 and 13, respectively. I understand how, if you went on to use $match, etc. later on on other fields, the results could be surprising, but you can't say that the output is wrong.
As for an explanation of why we see 13 instead of 12, my guess is that it has to do with $sort+$limit coalescence. The limit affects how the sort occurs, effectively making it quit earlier. My guess is that, since it quits earlier, it never considers 2, which, when it is considered without a $limit, is sorted before 3 (they have the same sort key, so it doesn't matter where it's put).
If you post example documents and a complete aggregation pipeline, perhaps there's more that could be said to help you, but this is my guess based on what I see.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments