[SERVER-30338] $sort + $limit optimizer failing in some cases Created: 26/Jul/17  Updated: 21/Sep/17  Resolved: 26/Jul/17

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.4.6
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Ivan Fioravanti Assignee: Kelsey Schubert
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Tested on Windows and Linux with MongoDB 3.4.6


Attachments: File SortLimitBug.bson.gz     File SortLimitBug.metadata.json.gz    
Issue Links:
Duplicate
duplicates SERVER-28195 $skip followed by $limit in aggregati... Closed
Operating System: ALL
Steps To Reproduce:

Import SortLimitBug dump in a new database.

Run following aggregation and check Id of last record return (67)

db.SortLimitBug.aggregate((
[
            { "$project" :
              { "TM30" : 1, "OM30":1}
            },
            {
                "$sort" : {
                    "TM30" : NumberInt(-1)
                }
            },
            {
                "$match" : {
                    "OM30" : {
                        "$lt" : 5.0
                    }
                }
            }, 
            
            {
                "$skip" : NumberInt(0)
            }, 
            {
                "$limit" : NumberInt(25)
            }         
        ]))

Run second page: Check Id of first record: is 67 instead of 72

db.SortLimitBug.aggregate((
[
            { "$project" :
              { "TM30" : 1, "OM30":1}
            },
            {
                "$sort" : {
                    "TM30" : NumberInt(-1)
                }
            },
            {
                "$match" : {
                    "OM30" : {
                        "$lt" : 5.0
                    }
                }
            }, 
            
            {
                "$skip" : NumberInt(25)
            }, 
            {
                "$limit" : NumberInt(25)
            }         
        ]))
 

Run same queries without $limit and records will be correct

Participants:

 Description   

We have discovered an issue that we think related to the $sort + $limit optimizer.
We have attached dump of a sample collection.

Second query with a limit of 25 has as first element the last element of the first page (25th) instead of the 26th correct one.

Same queries performed without limit works properly.



 Comments   
Comment by Kelsey Schubert [ 21/Sep/17 ]

Hi ivan.fioravanti@4ward.it,

This is likely just the result of your particular system. For example, when I ran the same operations as you described in the ticket, I saw a different sort order than the one you are describing. To ensure a consistent ordering, please include a unique field in your sort order.

For MongoDB-related support discussion please post on the mongodb-user group or Stack Overflow with the mongodb tag. A question like this involving more discussion would be best posted on the mongodb-users group.

Kind regards,
Kelsey

Comment by Ivan Fioravanti [ 27/Jul/17 ]

But why without $limits it works properly an moreover with $limits results is always the same? It this is by design I expect to see different sorted elements in result, while here order is always wrong in the same way and always on first element of the second page.

Comment by Kelsey Schubert [ 26/Jul/17 ]

Hi ivan.fioravanti@4ward.it,

Thank for the very clear reproduction steps in your report. The behavior you're describing is expected, please review my comment on SERVER-28195 for additional details.

Kind regards,
Thomas

Generated at Thu Feb 08 04:23:28 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.