Loading...

XML

Word

Printable

JSON

Type: Bug
Resolution: Won't Fix
Priority: Major - P3
Fix Version/s: None
Affects Version/s: None
Component/s: None
Labels:
None

Operating System:
ALL
Steps To Reproduce:
Hide

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}
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}
Linked BF Score:
5
CAR Domain/s:
None

Aha! Reference:
None
Tracking Level:
None
Risk Status:
None
Exec Notes:
None
Goal Name(s):
None
Goal Link:
None

When we run a query in classic with a sort stage the order of the results does not depend in the order of the fields in the documents. When we run the same queries in SBE we see that the order of the results changes when the order of the fields in the documents changes.

Assignee:: Foteini Alvanaki
Reporter:: Foteini Alvanaki
Participants:: Foteini Alvanaki
Votes:: 0 Vote for this issue
Watchers:: 3 Start watching this issue

Created:: Nov 15 2023 03:03:40 PM UTC
Updated:: Nov 16 2023 10:25:14 AM UTC
Resolved:: Nov 16 2023 10:25:14 AM UTC

Details

Description

Attachments

Activity

People

Dates