[SERVER-75101] when localField contains numeric component it must not be pushed into projection Created: 21/Mar/23  Updated: 29/Oct/23  Resolved: 01/May/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 7.1.0-rc0, 7.0.0-rc1

Type: Bug Priority: Major - P3
Reporter: Asya Kamsky Assignee: Matt Boros
Resolution: Fixed Votes: 0
Labels: query-director-triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Related
Assigned Teams:
Query Optimization
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v7.0, v6.3, v6.0, v5.0, v4.4
Sprint: QO 2023-05-15
Participants:

 Description   

When dependency analysis sees $lookup it adds "localField" content to fields it needs from the original collection. However, when that field includes a numeric component (which we may or may not intended to support as a numeric index) what happens is we push it into transformBy where it's interpreted as a field called "0" because projection doesn't support numeric references to array positions like queries do.

Create any collection orders and run:

db.orders.explain().aggregate([ { $lookup: { from: "productsxxx", localField: "products.0.productId", foreignField: "_id", as: "firstProduct" } }, {$match:{a:1}}, {$count:"c"}])
{
  explainVersion: '1',
  stages: [
    {
      '$cursor': {
        queryPlanner: {
          namespace: 'test.orders',
          indexFilterSet: false,
          parsedQuery: { a: { '$eq': 1 } },
          queryHash: 'C810717C',
          planCacheKey: 'C810717C',
          maxIndexedOrSolutionsReached: false,
          maxIndexedAndSolutionsReached: false,
          maxScansToExplodeReached: false,
          winningPlan: {
            stage: 'PROJECTION_DEFAULT',
            transformBy: { 'products.0.productId': 1, _id: 0 },
            inputStage: {
              stage: 'COLLSCAN',
              filter: { a: { '$eq': 1 } },
              direction: 'forward'
            }
          },
          rejectedPlans: []
        }
      }
    },
    {
      '$lookup': {
        from: 'productsxxx',
        as: 'firstProduct',
        localField: 'products.0.productId',
        foreignField: '_id'
      }
    },
    {
      '$group': { _id: { '$const': null }, c: { '$sum': { '$const': 1 } } }
    },
    { '$project': { c: true, _id: false } }
  ] // etc

Note transformation {{transformBy: { 'products.0.productId': 1, _id: 0 }}} which will not reach into arrays but rather only match field "0" of "products".



 Comments   
Comment by Githook User [ 05/May/23 ]

Author:

{'name': 'Matt Boros', 'email': 'matt.boros@mongodb.com', 'username': 'mattBoros'}

Message: SERVER-75101 when localField contains numeric component it must not be pushed into projection

(cherry picked from commit 9f160dea3f2d311efb8f981c239e717b79a09f65)
Branch: v7.0
https://github.com/mongodb/mongo/commit/0a7268ab6f6fbd9af152cc938a375077bfd1e816

Comment by Githook User [ 25/Apr/23 ]

Author:

{'name': 'Matt Boros', 'email': 'matt.boros@mongodb.com', 'username': 'mattBoros'}

Message: SERVER-75101 when localField contains numeric component it must not be pushed into projection
Branch: master
https://github.com/mongodb/mongo/commit/9f160dea3f2d311efb8f981c239e717b79a09f65

Comment by Matt Boros [ 17/Apr/23 ]

asya.kamsky@mongodb.com Can you confirm that this isn't an issue with $graphLookup, because the startsWith argument of $graphLookup is an expression, and expressions treat numeric field components like a field name? Never mind, this is resolved.

Comment by Matt Boros [ 12/Apr/23 ]

Ah, I forgot I actually had some work similar to this last year. SERVER-31082 covers the $count issue.

Comment by Matt Boros [ 12/Apr/23 ]

This example shows another issue with the classic optimizer. A $lookup followed by $count can remove the $lookup.

Generated at Thu Feb 08 06:29:19 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.