[SERVER-4346] Sorting on array fields works correctly when direction is descending, incorrect when ascending Created: 22/Nov/11  Updated: 20/Jul/15  Resolved: 28/Nov/11

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.0.1, 2.0.2
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: atom smith Assignee: Aaron Staple
Resolution: Done Votes: 0
Labels: query, sort
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

uname -a: Linux 3.0.0-12-generic #20-Ubuntu SMP Fri Oct 7 14:56:25 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux


Issue Links:
Duplicate
is duplicated by SERVER-19497 Sort is not done on embedded field Closed
Operating System: Linux
Participants:

 Description   

sorting by

{array : 1}

does not appear to do anything, it returns the same results as just running find(). This issue does not appear to exist in 1.8.4

> db.things.insert({ array: [ [0, ""], [3, "3"] ] })
> db.things.insert({ array: [ [0, ""], [1, "1"] ] })
> db.things.insert({ array: [ [0, ""], [4, "4"] ] })
> db.things.insert({ array: [ [0, ""], [5, "5"] ] })
> db.things.insert({ array: [ [0, ""], [2, "2"] ] })
> db.things.find()
{ "_id" : ObjectId("4ecb2dbc0ac2d0b3fe518048"), "array" : [ [ 0, "" ], [ 3, "3" ] ] }
{ "_id" : ObjectId("4ecb2dc20ac2d0b3fe518049"), "array" : [ [ 0, "" ], [ 1, "1" ] ] }
{ "_id" : ObjectId("4ecb2dc70ac2d0b3fe51804a"), "array" : [ [ 0, "" ], [ 4, "4" ] ] }
{ "_id" : ObjectId("4ecb2dcb0ac2d0b3fe51804b"), "array" : [ [ 0, "" ], [ 5, "5" ] ] }
{ "_id" : ObjectId("4ecb2dcf0ac2d0b3fe51804c"), "array" : [ [ 0, "" ], [ 2, "2" ] ] }
> db.things.find().sort({ array: 1 })
{ "_id" : ObjectId("4ecb2dbc0ac2d0b3fe518048"), "array" : [ [ 0, "" ], [ 3, "3" ] ] }
{ "_id" : ObjectId("4ecb2dc20ac2d0b3fe518049"), "array" : [ [ 0, "" ], [ 1, "1" ] ] }
{ "_id" : ObjectId("4ecb2dc70ac2d0b3fe51804a"), "array" : [ [ 0, "" ], [ 4, "4" ] ] }
{ "_id" : ObjectId("4ecb2dcb0ac2d0b3fe51804b"), "array" : [ [ 0, "" ], [ 5, "5" ] ] }
{ "_id" : ObjectId("4ecb2dcf0ac2d0b3fe51804c"), "array" : [ [ 0, "" ], [ 2, "2" ] ] }
> db.things.find().sort({ array: -1 })
{ "_id" : ObjectId("4ecb2dcb0ac2d0b3fe51804b"), "array" : [ [ 0, "" ], [ 5, "5" ] ] }
{ "_id" : ObjectId("4ecb2dc70ac2d0b3fe51804a"), "array" : [ [ 0, "" ], [ 4, "4" ] ] }
{ "_id" : ObjectId("4ecb2dbc0ac2d0b3fe518048"), "array" : [ [ 0, "" ], [ 3, "3" ] ] }
{ "_id" : ObjectId("4ecb2dcf0ac2d0b3fe51804c"), "array" : [ [ 0, "" ], [ 2, "2" ] ] }
{ "_id" : ObjectId("4ecb2dc20ac2d0b3fe518049"), "array" : [ [ 0, "" ], [ 1, "1" ] ] }



 Comments   
Comment by Aaron Staple [ 28/Nov/11 ]

please reopen if you have additional questions

Comment by Aaron Staple [ 22/Nov/11 ]

Here's a quick explanation - I'm going to simplify your example a little bit just to make it easier. Say your docs are:

{a:[0,3]} {a:[0,1]} {a:[0,4]} {a:[0,5]} {a:[0,2]}

For each of these docs there are two values of a (the two array values) that could be used for sorting. In a given query we only use one of these values per document to do sorting. In an ascending sort we use the smallest a value for each document. In all of these documents the smallest value is 0, and since they all have the same smallest value no reordering will occur. When doing a descending sort the highest value of a for each document is used to perform the sort. The high values of a for each document will produce the ordering you asked about initially.

This might seem like a strange sorting policy to use - the reason we have adopted it is for consistency with indexed sorts. This policy allows us to return documents from an index matching your sort spec without reordering them.

Comment by atom smith [ 22/Nov/11 ]

I am still a little confused by this implementation, why would an ascending / descending sort not be in opposite order of one another?

Comment by Aaron Staple [ 22/Nov/11 ]

Hi Atom,

Our sort is not the same as a javascript sort. We sort based on a single key within the array - in particular the key used for sorting is the first key of the array that would be seen if we were processing the query based on an index with a key spec equivalent to your sort key spec. We don't sort based on the entire array.

With bounded length arrays though you can get behavior similar to what you are asking for with a simpler example by doing something like this:

> u.save(

{a:[1,3]}

)
> u.save(

{a:[1,2]}

)
> u.save(

{a:[1,1]}

)
> u.find().sort(

{'a.0':1,'a.1':1}

)

{ "_id" : ObjectId("4ecb42d1d603d10575b6734f"), "a" : [ 1, 1 ] } { "_id" : ObjectId("4ecb42cdd603d10575b6734e"), "a" : [ 1, 2 ] } { "_id" : ObjectId("4ecb42cad603d10575b6734d"), "a" : [ 1, 3 ] }

This strategy won't work exactly in your case because you have nested arrays and there are some additional complications with those. In the specific example you provided you can get the ordering you want by doing db.things.find().sort(

{'array.1.0':1}

) but I don't know enough about your use case to suggest a more general solution.

Comment by atom smith [ 22/Nov/11 ]

I assumed that this worked like JavaScript's sort, which gave me the save results I was getting in 1.8.4:

[
  [ [ 0, "" ], [ 3, "3" ] ],
  [ [ 0, "" ], [ 1, "1" ] ],
  [ [ 0, "" ], [ 4, "4" ] ],
  [ [ 0, "" ], [ 5, "5" ] ],
  [ [ 0, "" ], [ 2, "2" ] ]
].sort();

Comment by Eliot Horowitz (Inactive) [ 22/Nov/11 ]

What exactly do you want?

What if you had an array like

[ 0 , 1 , 1 ]
[ 0 , 1 , 0 ]
[ 0 , 0 , 1 ]
[ 0 , 2 ]
[ 0 , 0 , 5 ]

You want ordered by 1st, then by 2nd, then by third, etc?

If so - there isn't a way to do that currently.

Comment by atom smith [ 22/Nov/11 ]

In that case, is there any way to sort it in the way I want to?

Comment by Eliot Horowitz (Inactive) [ 22/Nov/11 ]

Also - if you create an index on array in 1.8.4 - the results are the same as they are in 2.0.
The issue fixed for 2.0 was that the behavior without an index was different than with an index.

Comment by Eliot Horowitz (Inactive) [ 22/Nov/11 ]

Sorting an array from lowest to highest means something like "sort by the lowest value in the array".
So both results (1.8 and 2.0) are fine, as in the ascending case, the lowest values are all "".

Comment by atom smith [ 22/Nov/11 ]

I was expecting that ascending / descending be opposite of one another, as it is in 1.8.4:

> db.things.find().sort({array:1})
{ "_id" : ObjectId("4ecb3811cb1fd72301239bb1"), "array" : [ [ 0, "" ], [ 1, "1" ] ] }
{ "_id" : ObjectId("4ecb3812cb1fd72301239bb4"), "array" : [ [ 0, "" ], [ 2, "2" ] ] }
{ "_id" : ObjectId("4ecb3811cb1fd72301239bb0"), "array" : [ [ 0, "" ], [ 3, "3" ] ] }
{ "_id" : ObjectId("4ecb3811cb1fd72301239bb2"), "array" : [ [ 0, "" ], [ 4, "4" ] ] }
{ "_id" : ObjectId("4ecb3811cb1fd72301239bb3"), "array" : [ [ 0, "" ], [ 5, "5" ] ] }
> db.things.find().sort({array:-1})
{ "_id" : ObjectId("4ecb3811cb1fd72301239bb3"), "array" : [ [ 0, "" ], [ 5, "5" ] ] }
{ "_id" : ObjectId("4ecb3811cb1fd72301239bb2"), "array" : [ [ 0, "" ], [ 4, "4" ] ] }
{ "_id" : ObjectId("4ecb3811cb1fd72301239bb0"), "array" : [ [ 0, "" ], [ 3, "3" ] ] }
{ "_id" : ObjectId("4ecb3812cb1fd72301239bb4"), "array" : [ [ 0, "" ], [ 2, "2" ] ] }
{ "_id" : ObjectId("4ecb3811cb1fd72301239bb1"), "array" : [ [ 0, "" ], [ 1, "1" ] ] }

Comment by Eliot Horowitz (Inactive) [ 22/Nov/11 ]

This looks correct to me.

sorting an array means it will sort by the lowest of highest element.

What were you expecting differently?

Comment by atom smith [ 22/Nov/11 ]

not sure how to fix that formatting.

Generated at Thu Feb 08 03:05:43 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.