Sorting multiple fields produces wrong order

Hello,

I am new to mongoDB. From documentation, it says the query result can be sorted in multiple fields. I tried to sort by the first field (‘65534’) in date format, then the second field (‘65533’) in number format. Here is the query I issued and the result produced:

db.getCollection('test_msg').find({},{'65534':1,'65533':1}).sort({'65534':1,'65533':1})

/* 1 */
{
    "_id" : ObjectId("5e9fa79a7b6a0000a5005962"),
    "65534" : ISODate("2020-04-22T02:10:34.628Z"),
    "65533" : NumberLong(0)
}

/* 2 */
{
    "_id" : ObjectId("5e9fa79a7b6a0000a5005964"),
    "65534" : ISODate("2020-04-22T02:10:34.907Z"),
    "65533" : NumberLong(0)
}

/* 3 */
{
    "_id" : ObjectId("5e9fa79b7b6a0000a5005967"),
    "65534" : ISODate("2020-04-22T02:10:35.177Z"),
    "65533" : NumberLong(0)
}

/* 4 */
{
    "_id" : ObjectId("5e9fa79b7b6a0000a500596c"),
    "65534" : ISODate("2020-04-22T02:10:35.452Z"),
    "65533" : NumberLong(0)
}

/* 5 */
{
    "_id" : ObjectId("5e9fa79b7b6a0000a500596e"),
    "65534" : ISODate("2020-04-22T02:10:35.456Z"),
    "65533" : NumberLong(0)
}

/* 6 */
{
    "_id" : ObjectId("5e9fa79b7b6a0000a5005971"),
    "65534" : ISODate("2020-04-22T02:10:35.459Z"),
    "65533" : NumberLong(0)
}

/* 7 */
{
    "_id" : ObjectId("5e9fa79b7b6a0000a5005975"),
    "65534" : ISODate("2020-04-22T02:10:35.733Z"),
    "65533" : NumberLong(0)
}

/* 8 */
{
    "_id" : ObjectId("5e9fa79c7b6a0000a5005979"),
    "65534" : ISODate("2020-04-22T02:10:36.576Z"),
    "65533" : NumberLong(0)
}

/* 9 */
{
    "_id" : ObjectId("5e9fa79c7b6a0000a5005980"),
    "65534" : ISODate("2020-04-22T02:10:36.857Z"),
    "65533" : NumberLong(0)
}

/* 10 */
**{**
**    "_id" : ObjectId("5e9fa79b7b6a0000a5005969"),**
**    "65534" : ISODate("2020-04-22T02:10:35.181Z"),**
**    "65533" : NumberLong(1)**
**}**

/* 11 */
{
    "_id" : ObjectId("5e9fa79c7b6a0000a500597d"),
    "65534" : ISODate("2020-04-22T02:10:36.580Z"),
    "65533" : NumberLong(1)
}

/* 12 */
{
    "_id" : ObjectId("5e9fa79c7b6a0000a5005984"),
    "65534" : ISODate("2020-04-22T02:10:36.861Z"),
    "65533" : NumberLong(1)
}

/* 13 */
{
    "_id" : ObjectId("5e9fa79c7b6a0000a5005986"),
    "65534" : ISODate("2020-04-22T02:10:36.862Z"),
    "65533" : NumberLong(2)
}

You can spot well that the returned item #10 is not in the order as the sorting criteria: its date field value is earlier than the former item #9. Well, I found the output is like sorting only using the numeric field ‘65533’.

I tried creating an index {‘65534’:1,‘65533’:1} but it did not help. [Well, I don’t think it helps other than performance matter; am I right?]

I tested on Community edition 4.2.6, 4.2.5, 4.0.18 but all come to the results not in my expectation.

Could any expert tell whether I have a wrong understanding of the documentation or mongodb behavior? What is the correct way to produce a my expected sorting result of multiple fields in my case?

Thank you first for your advice.

Amon

Yes, what you observed is correct.

The result actually looks like as if the sort is done with { "65533": 1, "65534": 1 } and not as actually performed with { "65534": 1, "65533": 1 }.

Also, the result is same with both sort patterns.

{ "65533" : NumberLong(0), "65534" : ISODate("2020-04-22T02:10:34.628Z") }
{ "65533" : NumberLong(0), "65534" : ISODate("2020-04-22T02:10:34.907Z") }
{ "65533" : NumberLong(0), "65534" : ISODate("2020-04-22T02:10:35.177Z") }
{ "65533" : NumberLong(0), "65534" : ISODate("2020-04-22T02:10:35.452Z") }
{ "65533" : NumberLong(0), "65534" : ISODate("2020-04-22T02:10:35.456Z") }
{ "65533" : NumberLong(0), "65534" : ISODate("2020-04-22T02:10:35.459Z") }
{ "65533" : NumberLong(0), "65534" : ISODate("2020-04-22T02:10:35.733Z") }
{ "65533" : NumberLong(0), "65534" : ISODate("2020-04-22T02:10:36.576Z") }
{ "65533" : NumberLong(0), "65534" : ISODate("2020-04-22T02:10:36.857Z") }

{ "65533" : NumberLong(1), "65534" : ISODate("2020-04-22T02:10:35.181Z") }
{ "65533" : NumberLong(1), "65534" : ISODate("2020-04-22T02:10:36.580Z") }
{ "65533" : NumberLong(1), "65534" : ISODate("2020-04-22T02:10:36.861Z") }

{ "65533" : NumberLong(2), "65534" : ISODate("2020-04-22T02:10:36.862Z") }


[ EDIT ADD ]

I renamed the field names from “65533” and “65534” to “num” and “dt” respectively, and found the sorting happens correctly:

{ "dt" : ISODate("2020-04-22T02:10:34.628Z"), "num" : NumberLong(0) }
{ "dt" : ISODate("2020-04-22T02:10:34.907Z"), "num" : NumberLong(0) }
{ "dt" : ISODate("2020-04-22T02:10:35.177Z"), "num" : NumberLong(0) }
{ "dt" : ISODate("2020-04-22T02:10:35.181Z"), "num" : NumberLong(1) }
{ "dt" : ISODate("2020-04-22T02:10:35.452Z"), "num" : NumberLong(0) }
{ "dt" : ISODate("2020-04-22T02:10:35.456Z"), "num" : NumberLong(0) }
{ "dt" : ISODate("2020-04-22T02:10:35.459Z"), "num" : NumberLong(0) }
{ "dt" : ISODate("2020-04-22T02:10:35.733Z"), "num" : NumberLong(0) }
{ "dt" : ISODate("2020-04-22T02:10:36.576Z"), "num" : NumberLong(0) }
{ "dt" : ISODate("2020-04-22T02:10:36.580Z"), "num" : NumberLong(1) }
{ "dt" : ISODate("2020-04-22T02:10:36.857Z"), "num" : NumberLong(0) }
{ "dt" : ISODate("2020-04-22T02:10:36.861Z"), "num" : NumberLong(1) }
{ "dt" : ISODate("2020-04-22T02:10:36.862Z"), "num" : NumberLong(2) }


References:

Welcome to the community @Amon_Tse!

Based on your output, I assume you are using Robo3T (although similar behaviour can be reproduced in the mongo shell).

The issue you are seeing is because you are using key values that look like numbers, and JavaScript is quirky when it comes to the order of keys in an object. Keys that look like numbers will end up sorted first, so the server ends up being sent a different sort order than you intended.

For example:

> var sort = {'65534':1,'65533':1}
> sort
{ "65533" : 1, "65534" : 1 }

In JavaScript you would either want to use alphanumeric key names (which don’t get sorted) or an order-preserving data structure like Map (which is how the Node.js driver implemented ordered options: NODE-578). All official drivers or languages include support for creating ordered objects where order is significant.

The mongo shell embeds a JavaScript interpreter, but unfortunately does not currently have a workaround for this edge case outside of avoiding numeric-like field names. Some relevant issues to upvote & watch are SERVER-11358 and SERVER-28569.

If you are using a MongoDB admin UI which doesn’t have a solution for this, I would report the behaviour as a bug.

I noticed that this bug also exists in Compass, so created COMPASS-4258.

Regards,
Stennie

I found that bug had been logged https://jira.mongodb.org/browse/SERVER-11358 since 2013 and it is still opened!