Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-83272

Fix discrepancy in sorting between classic and SBE

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Won't Fix
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • ALL
    • 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}
    • 5

      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@mongodb.com Foteini Alvanaki
            Reporter:
            foteini.alvanaki@mongodb.com Foteini Alvanaki
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: