[SERVER-78752] An incorrect plan can be written to the SBE plan cache for certain $or queries, leads to incorrect query results Created: 06/Jul/23  Updated: 29/Oct/23  Resolved: 20/Jul/23

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

Type: Bug Priority: Blocker - P1
Reporter: Reginald Chounoune Assignee: David Storch
Resolution: Fixed Votes: 0
Labels: auto-reverted
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Text File old-and-new-query-plans.txt     HTML File summary_oldMethod     HTML File summary_withNewMethod    
Issue Links:
Backports
Depends
Problem/Incident
Related
related to SERVER-78962 Check on feasibility to perform boole... Backlog
related to SERVER-79092 Optimize the expression search for pa... Closed
Assigned Teams:
Query Execution
Backwards Compatibility: Fully Compatible
Operating System: ALL
Sprint: QE 2023-07-24
Participants:
Case:
Linked BF Score: 167

 Description   

After analysis the following root cause is two fold:

1) The Parameterization of a query abstracts dependencies between two $or branches in such a way that dependencies are lost. A query containing an $or statement with predicates on the same field of the same value are considered to be the same as a query where the 2 filters did not have the same value. Both queries would result in the same QueryShape which is semantically incorrect as the dependency are used in query optimizations. These two queries should have two different planhashes

2) While checking for equality of 2 $or branches containing and IndexScan the equality function was missing to check the the IETs and 2 falsely considered equal IndexScans were merged together leading to a correctness issue. 

Hi Team,

I have identified an issue with the Slot-based Execution Engine where the Query Planner is selecting the wrong plan for a find() command (see details below), resulting in data inconsistency. In summary, it's implicitly adding a limit(1) to a query, causing the query to always return 1 document instead of the actual number of matched documents (2 expected). Before we found the actual culprit, one workaround was to do a test failover in Atlas also which would resolve the issue temporarily. 

Scenario:

  • We created the corresponding aggregation pipeline for the same find() query, and it returned 2 documents as expected.
  • If we switch the position of any field in the query filter, it returns 2 documents as expected.
  • We looked at the PlanCache of the collection and found two queries identical to the problematic one, except that one of them has "limit(1)" which is the one the query planner kept selecting although we had not explicitly used limit in the query. - the culprit for this issue.
  • Because of this cached plan, the number of returned documents was always 1.
  • We ran the PlanCache.clearPlansByQuery() command to remove the query, and that solved the issue.

I have also attached the Plan cache output which contains the bad plans along with the new plans (after we cleared the cache). Each section is labeled "old cache query plans" and "new cached query plans" respectively.

Please let me know if you need any additional information. Thank you for your help.

Best regards,
Reginald



 Comments   
Comment by Githook User [ 20/Jul/23 ]

Author:

{'name': 'David Storch', 'email': 'david.storch@mongodb.com', 'username': 'dstorch'}

Message: SERVER-78752 Include IETs into IXScan comparisons for $or collapsing, reuse parameters during the parameterization of queries

This reintroduces reverted commit
b1453cb73fbdc3b849dd8b9761c6c1b255452e2e and also includes
the necessary fixes to the JS test tags.
Branch: master
https://github.com/mongodb/mongo/commit/5f6e1e8f75b0ad77c7c0b58f26df625563ce5660

Comment by Githook User [ 20/Jul/23 ]

Author:

{'name': 'David Storch', 'email': 'david.storch@mongodb.com', 'username': 'dstorch'}

Message: SERVER-78752 Prevent sbe_plan_cache_duplicate_or_clauses.js from running against Classic Engine
Branch: v7.0
https://github.com/mongodb/mongo/commit/5c49af1ff554ab43b21d2be37842535c73cc101b

Comment by Githook User [ 19/Jul/23 ]

Author:

{'name': 'David Storch', 'email': 'david.storch@mongodb.com', 'username': 'dstorch'}

Message: SERVER-78752 Add missing JS test tags
Branch: v7.0
https://github.com/mongodb/mongo/commit/27da922959c12df48ae2ecf8aad0f37f42f71451

Comment by xgen-buildbaron-user [ 19/Jul/23 ]

Ticket re-opened due to revert. replica_sets_terminate_primary_jscore_passthrough began a consistent failure of jstests/core/sbe_plan_cache_duplicate_or_clauses.js

Comment by Githook User [ 19/Jul/23 ]

Author:

{'name': 'auto-revert-processor', 'email': 'dev-prod-dag@mongodb.com', 'username': ''}

Message: Revert "SERVER-78752 Include IETs into IXScan comparisons for $or collapsing, reuse parameters during the parameterization of queries."

This reverts commit b1453cb73fbdc3b849dd8b9761c6c1b255452e2e.
Branch: master
https://github.com/mongodb/mongo/commit/0198a6cc01046e14239a7922a26fd64e7fbc99c4

Comment by Githook User [ 19/Jul/23 ]

Author:

{'name': 'Peter Volk', 'email': 'peter.volk@mongodb.com', 'username': 'HCSPete'}

Message: SERVER-78752 Include IETs into IXScan comparisons for $or collapsing, reuse parameters during the parameterization of queries.

(cherry picked from commit b1453cb73fbdc3b849dd8b9761c6c1b255452e2e)
Branch: v7.0
https://github.com/mongodb/mongo/commit/ae26047bfb71295aa7570c00d2d1dbe8230b79fd

Comment by Githook User [ 19/Jul/23 ]

Author:

{'name': 'Peter Volk', 'email': 'peter.volk@mongodb.com', 'username': 'HCSPete'}

Message: SERVER-78752 Include IETs into IXScan comparisons for $or collapsing, reuse parameters during the parameterization of queries.
Branch: master
https://github.com/mongodb/mongo/commit/b1453cb73fbdc3b849dd8b9761c6c1b255452e2e

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