[SERVER-74954] Incorrect result when contained $or rewrites $elemMatch extra condition Created: 16/Mar/23  Updated: 29/Oct/23  Resolved: 28/Jun/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 4.4.19, 6.0.5, 6.3.0-rc1
Fix Version/s: 7.1.0-rc0, 5.0.20, 6.0.9, 7.0.2

Type: Bug Priority: Major - P3
Reporter: Asya Kamsky Assignee: Svilen Mihaylov (Inactive)
Resolution: Fixed Votes: 0
Labels: query-director-triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Related
related to SERVER-78260 Contained $or rewrite optimization in... Closed
is related to SERVER-78260 Contained $or rewrite optimization in... Closed
Assigned Teams:
Query Optimization
Backwards Compatibility: Minor Change
Operating System: ALL
Backport Requested:
v7.0, v6.0, v5.0, v4.4
Sprint: QO 2023-05-15, QO 2023-05-29, QO 2023-06-12, QO 2023-06-26, QO 2023-07-10
Participants:
Case:

 Description   

Sample data:

[
  { c: 1, p: [ { id: 1 } ] },
  { c: 2, p: [ { id: 1 } ] },
  { c: 3, p: [ { id: 1 } ] },
  { c: 4, p: [ { id: 1 } ] }
]

Index must be present to trigger the bug:

{ "p.id": 1, c: 1}

Query in question:

db.test.find(
{$and:[
      {$or:[{c:{$lt:2}},{c:{$gt:3}}]},
      {p:{$elemMatch:{id:{$eq:1,$exists:true}}}}
]}
)

Without the index, it matches two documents. With index but without (redundant) $exists:true inside $elemMatch it also returns two documents. But with index and with redundant exists it fails to return any documents.



 Comments   
Comment by Githook User [ 28/Aug/23 ]

Author:

{'name': 'Svilen Mihaylov', 'email': '60888594+svilen-mihaylov@users.noreply.github.com', 'username': 'svilen-mihaylov'}

Message: SERVER-74954 Incorrect result when contained $or rewrites $elemMatch extra condition.
Branch: v7.0
https://github.com/mongodb/mongo/commit/c578c38b94ffe9fa59cb5deb13473dd42bfa7cb8

Comment by Githook User [ 26/Jul/23 ]

Author:

{'name': 'Svilen Mihaylov', 'email': '60888594+svilen-mihaylov@users.noreply.github.com', 'username': 'svilen-mihaylov'}

Message: SERVER-74954 Incorrect result when contained $or rewrites $elemMatch extra condition.
Branch: v5.0
https://github.com/mongodb/mongo/commit/17bb703dcde076802f805ae43ba3b6d0654ad298

Comment by Githook User [ 25/Jul/23 ]

Author:

{'name': 'Svilen Mihaylov', 'email': '60888594+svilen-mihaylov@users.noreply.github.com', 'username': 'svilen-mihaylov'}

Message: SERVER-74954 Incorrect result when contained $or rewrites $elemMatch extra condition.
Branch: v6.0
https://github.com/mongodb/mongo/commit/819a7de47c0ad1a6c0fb463354ad01fbfb7a32ef

Comment by Githook User [ 03/Jul/23 ]

Author:

{'name': 'Svilen Mihaylov', 'email': '60888594+svilen-mihaylov@users.noreply.github.com', 'username': 'svilen-mihaylov'}

Message: SERVER-74954 Incorrect result when contained $or rewrites $elemMatch extra condition.
Branch: EVG-17874-taskgen-test
https://github.com/mongodb/mongo/commit/57fac8ebd41314e09604e8f08ca35686a704b034

Comment by Githook User [ 28/Jun/23 ]

Author:

{'name': 'Svilen Mihaylov', 'email': '60888594+svilen-mihaylov@users.noreply.github.com', 'username': 'svilen-mihaylov'}

Message: SERVER-74954 Incorrect result when contained $or rewrites $elemMatch extra condition.
Branch: master
https://github.com/mongodb/mongo/commit/57fac8ebd41314e09604e8f08ca35686a704b034

Comment by Asya Kamsky [ 16/Mar/23 ]

Here is explain winning plan which shows some funky rewrite happening that doesn't match actual data:

    winningPlan: {
      stage: 'FETCH',
      filter: {
        p: {
          '$elemMatch': {
            '$and': [ { id: { '$eq': 1 } }, { id: { '$exists': true } } ]
          }
        }
      },
      inputStage: {
        stage: 'OR',
        inputStages: [
          {
            stage: 'FETCH',
            filter: { id: { '$exists': true } },   /* what? */
            inputStage: {
              stage: 'IXSCAN',
              keyPattern: { 'p.id': 1, c: 1 },
              indexName: 'p.id_1_c_1',
              isMultiKey: true,
              multiKeyPaths: { 'p.id': [ 'p' ], c: [] },
              isUnique: false,
              isSparse: false,
              isPartial: false,
              indexVersion: 2,
              direction: 'forward',
              indexBounds: { 'p.id': [ '[1, 1]' ], c: [ '[-inf.0, 2)' ] }
            }
          },
          {
            stage: 'FETCH',
            filter: { id: { '$exists': true } },      /* what? */
            inputStage: {
              stage: 'IXSCAN',
              keyPattern: { 'p.id': 1, c: 1 },
              indexName: 'p.id_1_c_1',
              isMultiKey: true,
              multiKeyPaths: { 'p.id': [ 'p' ], c: [] },
              isUnique: false,
              isSparse: false,
              isPartial: false,
              indexVersion: 2,
              direction: 'forward',
              indexBounds: { 'p.id': [ '[1, 1]' ], c: [ '(3, inf.0]' ] }
            }
          }
        ]
      }
    },
 

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