[SERVER-19497] Sort is not done on embedded field Created: 20/Jul/15  Updated: 23/Sep/15  Resolved: 20/Jul/15

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.10, 3.0.2
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Daniel Coupal Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-4346 Sorting on array fields works correct... Closed
is duplicated by SERVER-20551 sort on multikey index after $elemMat... Closed
Related
Operating System: ALL
Steps To Reproduce:

replset:PRIMARY> db.cs22636.find().sort({"xyz.a":1})
// This is the natural order that is displayed, instead of the sorted one
{ "_id" : ObjectId("55a902338943286a2df81a06"), "xyz" : [ { "a" : 1323 }, { "b" : 1 } ] }
{ "_id" : ObjectId("55a902398943286a2df81a07"), "xyz" : [ { "a" : 2 }, { "b" : 3 } ] }
{ "_id" : ObjectId("55a9023f8943286a2df81a08"), "xyz" : [ { "a" : 88 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a903778943286a2df81a09"), "xyz" : [ { "a" : 4325 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a9037a8943286a2df81a0a"), "xyz" : [ { "a" : 4325435 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a9037e8943286a2df81a0b"), "xyz" : [ { "a" : 43 }, { "b" : 0 } ] }
 
// Going backward works
replset:PRIMARY> db.cs22636.find().sort({"xyz.a":-1})
{ "_id" : ObjectId("55a9037a8943286a2df81a0a"), "xyz" : [ { "a" : 4325435 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a903778943286a2df81a09"), "xyz" : [ { "a" : 4325 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a902338943286a2df81a06"), "xyz" : [ { "a" : 1323 }, { "b" : 1 } ] }
{ "_id" : ObjectId("55a9023f8943286a2df81a08"), "xyz" : [ { "a" : 88 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a9037e8943286a2df81a0b"), "xyz" : [ { "a" : 43 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a902398943286a2df81a07"), "xyz" : [ { "a" : 2 }, { "b" : 3 } ] }
 
// Adding a first index, and hoping it will use it.
db.cs22636.ensureIndex({"xyz.a":1})
 
// Getting the same results, forward and backward as without index
replset:PRIMARY> db.cs22636.find().sort({"xyz.a":1}).hint({"xyz.a":1})
{ "_id" : ObjectId("55a902338943286a2df81a06"), "xyz" : [ { "a" : 1323 }, { "b" : 1 } ] }
{ "_id" : ObjectId("55a902398943286a2df81a07"), "xyz" : [ { "a" : 2 }, { "b" : 3 } ] }
{ "_id" : ObjectId("55a9023f8943286a2df81a08"), "xyz" : [ { "a" : 88 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a903778943286a2df81a09"), "xyz" : [ { "a" : 4325 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a9037a8943286a2df81a0a"), "xyz" : [ { "a" : 4325435 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a9037e8943286a2df81a0b"), "xyz" : [ { "a" : 43 }, { "b" : 0 } ] }
 
replset:PRIMARY> db.cs22636.find().sort({"xyz.a":-1}).hint({"xyz.a":1})
{ "_id" : ObjectId("55a9037a8943286a2df81a0a"), "xyz" : [ { "a" : 4325435 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a903778943286a2df81a09"), "xyz" : [ { "a" : 4325 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a902338943286a2df81a06"), "xyz" : [ { "a" : 1323 }, { "b" : 1 } ] }
{ "_id" : ObjectId("55a9023f8943286a2df81a08"), "xyz" : [ { "a" : 88 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a9037e8943286a2df81a0b"), "xyz" : [ { "a" : 43 }, { "b" : 0 } ] }
{ "_id" : ObjectId("55a902398943286a2df81a07"), "xyz" : [ { "a" : 2 }, { "b" : 3 } ] }
 
// not putting "b" in the array of values
// results are fine, forward and backward
replset:PRIMARY> db.cs22636c.find().sort({"xyz.a":1})
{ "_id" : ObjectId("55a9a13a978c4d7510ae5a47"), "xyz" : [ { "a" : 2 } ] }
{ "_id" : ObjectId("55a9a157978c4d7510ae5a4b"), "xyz" : [ { "a" : 43 } ] }
{ "_id" : ObjectId("55a9a13f978c4d7510ae5a48"), "xyz" : [ { "a" : 88 } ] }
{ "_id" : ObjectId("55a9a131978c4d7510ae5a46"), "xyz" : [ { "a" : 1323 } ] }
{ "_id" : ObjectId("55a9a147978c4d7510ae5a49"), "xyz" : [ { "a" : 4325 } ] }
{ "_id" : ObjectId("55a9a151978c4d7510ae5a4a"), "xyz" : [ { "a" : 4325435 } ] }
 
replset:PRIMARY> db.cs22636c.find().sort({"xyz.a":-1})
{ "_id" : ObjectId("55a9a151978c4d7510ae5a4a"), "xyz" : [ { "a" : 4325435 } ] }
{ "_id" : ObjectId("55a9a147978c4d7510ae5a49"), "xyz" : [ { "a" : 4325 } ] }
{ "_id" : ObjectId("55a9a131978c4d7510ae5a46"), "xyz" : [ { "a" : 1323 } ] }
{ "_id" : ObjectId("55a9a13f978c4d7510ae5a48"), "xyz" : [ { "a" : 88 } ] }
{ "_id" : ObjectId("55a9a157978c4d7510ae5a4b"), "xyz" : [ { "a" : 43 } ] }
{ "_id" : ObjectId("55a9a13a978c4d7510ae5a47"), "xyz" : [ { "a" : 2 } ] }

Participants:

 Description   

A sort operation is not happening on a sub field.
I reproduced it in 2.6.10, while a customer saw it in 3.0.2.



 Comments   
Comment by J Rassi [ 20/Jul/15 ]

The query results posted are consistent with the server's semantics of sorting on an array value. For sorting on an array in the case of an empty query predicate, the server picks the minimum element of the array as the document's sort key for ascending sorts, and the maximum value of the array as the document's sort key for descending sorts.

As each of the documents in the output above have an array "xyz" with two values, each document has two different values for the path "xyz", specifically "xyz.0.a" and "xyz.1.a". For each document, the value for "xyz.0.a" is an integer, and the value of "xyz.1.a" is null. The null value sorts before integral values in BSON sort order; hence, each document ties for the given ascending sort (since each document has the same minimum value for "xyz.a": null), and the documents appear in the "expected" order for the descending sort (since each document's maximum value for "xyz.a" is an integer).

Resolving this ticket as a dup of SERVER-4346; see that ticket for additional examples and a more detailed explanation of these semantics.

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