Uploaded image for project: 'C# Driver'
  1. C# Driver
  2. CSHARP-3783

GroupJoin with projection yields incorrect query

    • Type: Icon: Bug Bug
    • Resolution: Fixed
    • Priority: Icon: Major - P3 Major - P3
    • 2.14.0
    • Affects Version/s: 2.13.1
    • Component/s: Linq, LINQ3

      The C# driver translates the following IMongoQueryable incorrectly:

      collection.AsQueryable()
                      .Where(x =>
                      x.ParentSerialNumber != null
                      && x.ParentLevel != null)
                      .GroupJoin(
                          collection.AsQueryable(),
                          c => c.ParentSerialNumber,
                          p => p.SerialNumber,
                          (c, p) => new
                          {
                              ChildSerialNumber = c.SerialNumber,
                              ChildLevel = c.Level,
                              ParentSerialNumber = c.ParentSerialNumber,
                              ParentLevel = c.ParentLevel,
                              ParentExists = p.Any(pa => pa.Level == c.ParentLevel),
                              ParentConsistent = p.Any(pa => pa.Level == c.ParentLevel && pa.Status == c.Status)
                          })
                      .Where(x => !x.ParentConsistent)
                      .OrderBy(x => x.ChildLevel)
      

       

      It translates into:

      aggregate([
         {
            "$match":{
               "ParentSerialNumber":{
                  "$ne":null
               },
               "ParentLevel":{
                  "$ne":null
               }
            }
         },
         {
            "$lookup":{
               "from":"collectionName",
               "localField":"ParentSerialNumber",
               "foreignField":"SerialNumber",
               "as":"p"
            }
         },
         {
            "$project":{
               "ChildSerialNumber":"$SerialNumber",
               "ChildLevel":"$Level",
               "ParentSerialNumber":"$ParentSerialNumber",
               "ParentLevel":"$ParentLevel",
               "ParentExists":{
                  "$anyElementTrue":{
                     "$map":{
                        "input":"$p",
                        "as":"pa",
                        "in":{
                           "$eq":[
                              "$$pa.Level",
                              "$$pa.ParentLevel"
                           ]
                        }
                     }
                  }
               },
               "ParentConsistent":{
                  "$anyElementTrue":{
                     "$map":{
                        "input":"$p",
                        "as":"pa",
                        "in":{
                           "$and":[
                              {
                                 "$eq":[
                                    "$$pa.Level",
                                    "$$pa.ParentLevel"
                                 ]
                              },
                              {
                                 "$eq":[
                                    "$$pa.Status",
                                    "$$pa.Status"
                                 ]
                              }
                           ]
                        }
                     }
                  }
               },
               "_id":0
            }
         },
         {
            "$match":{
               "ParentConsistent":{
                  "$ne":true
               }
            }
         },
         {
            "$sort":{
               "ChildLevel":1
            }
         }
      ])
      

      Note the map for ParentExists and ParentConsistent properties.

      The correct query would be:

      aggregate([
         {
            "$match":{
               "ParentSerialNumber":{
                  "$ne":null
               },
               "ParentLevel":{
                  "$ne":null
               }
            }
         },
         {
            "$lookup":{
               "from":"collectionName",
               "localField":"ParentSerialNumber",
               "foreignField":"SerialNumber",
               "as":"p"
            }
         },
         {
            "$project":{
               "ChildSerialNumber":"$SerialNumber",
               "ChildLevel":"$Level",
               "ParentSerialNumber":"$ParentSerialNumber",
               "ParentLevel":"$ParentLevel",
               "ParentExists":{
                  "$anyElementTrue":{
                     "$map":{
                        "input":"$p",
                        "as":"pa",
                        "in":{
                           "$eq":[
                              "$$pa.Level",
                              "$ParentLevel"
                           ]
                        }
                     }
                  }
               },
               "ParentConsistent":{
                  "$anyElementTrue":{
                     "$map":{
                        "input":"$p",
                        "as":"pa",
                        "in":{
                           "$and":[
                              {
                                 "$eq":[
                                    "$$pa.Level",
                                    "$ParentLevel"
                                 ]
                              },
                              {
                                 "$eq":[
                                    "$$pa.Status",
                                    "$Status"
                                 ]
                              }
                           ]
                        }
                     }
                  }
               },
               "_id":0
            }
         },
         {
            "$match":{
               "ParentConsistent":{
                  "$ne":true
               }
            }
         },
         {
            "$sort":{
               "ChildLevel":1
            }
         }
      ])
      

      Any such projection, even if you try to do a Select projection after GroupJoin and with any linq method yields the same incorrect query.

            Assignee:
            james.kovacs@mongodb.com James Kovacs
            Reporter:
            ales.jeusnik@inel.com Ales Jeusnik
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: