Show
Create the following three collections
db.t01.insertMany(
[
{ _id: 0 },
{ _id: 1 },
{ _id: 2 , obj: { _id: 3 , obj: {} } },
{ _id: 4 },
{ _id: 5 },
{ _id: 6 }
])
db.t02.insertMany(
[
{ _id: 0 },
{ _id: 1 },
{ _id: 2 , obj: { _id: 3 , date: null , obj: {} } },
{ _id: 4 },
{ _id: 5 },
{ _id: 6 }
])
db.t03.insertMany(
[
{ _id: 0 },
{ _id: 1 },
{ _id: 2 , obj: { _id: 3 , obj: {}, date: null } },
{ _id: 4 },
{ _id: 5 },
{ _id: 6 }
])
Run the following queries both in Classic and in SBE
db.t01.aggregate([{$project: { "obj.date" : new Date( "2019-08-05T22:26:32.772Z" ), "obj.obj" : 1 }},{$sort: {obj: - 1 }}])
db.t02.aggregate([{$project: { "obj.date" : new Date( "2019-08-05T22:26:32.772Z" ), "obj.obj" : 1 }},{$sort: {obj: - 1 }}])
db.t03.aggregate([{$project: { "obj.date" : new Date( "2019-08-05T22:26:32.772Z" ), "obj.obj" : 1 }},{$sort: {obj: - 1 }}])
Results in classic are always the same
[
{ _id: 0 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 1 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 4 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 5 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 6 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{
_id: 2 ,
obj: { obj: {}, date: ISODate( "2019-08-05T22:26:32.772Z" ) }
}
]
Results in SBE for each query are
t01
[
{ _id: 0 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 1 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 4 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 5 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 6 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{
_id: 2 ,
obj: { obj: {}, date: ISODate( "2019-08-05T22:26:32.772Z" ) }
}
]
t02
[
{
_id: 2 ,
obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ), obj: {} }
},
{ _id: 0 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 1 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 4 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 5 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 6 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } }
]
t03
[
{ _id: 0 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 1 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 4 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 5 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{ _id: 6 , obj: { date: ISODate( "2019-08-05T22:26:32.772Z" ) } },
{
_id: 2 ,
obj: { obj: {}, date: ISODate( "2019-08-05T22:26:32.772Z" ) }
}
]
From my investigation this happens because in SBE when querying collections t01 and t03 we compare {
{ "obj" : {}, "date" : 1565043992772 }
vs
{ "date" : 1565043992772 }
but when we query t02 we compare
{ "date" : 1565043992772 , "obj" : {}}
vs
{ "date" : 1565043992772 }
The sort key in classic is in all relations
{ "obj" : {}, "date" : 1565043992772 }