[SERVER-40691] $nin:[[],...] queries are not indexed Created: 17/Apr/19  Updated: 29/Oct/23  Resolved: 28/Feb/22

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 4.0.9, 4.1.9
Fix Version/s: 6.0.0-rc0, 4.2.19, 4.4.13, 5.0.7

Type: Bug Priority: Major - P3
Reporter: Tom Scott Assignee: Jess Balint
Resolution: Fixed Votes: 1
Labels: query-44-grooming, query-director-triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Problem/Incident
is caused by SERVER-38949 Incorrect index bounds for {$ne: ["St... Closed
Related
is related to SERVER-39764 Negation of $in with embedded array c... Closed
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v5.3, v5.2, v5.0, v4.4, v4.2
Steps To Reproduce:
  1. Create an index for an Array field
  2. Run a query for $nin: [[], null] on that field
  3. Observe that a No query solutions occurs, even though an index was created, because the query no longer respects the index.
Sprint: Query Optimization 2021-05-17, Query Optimization 2021-06-14, Query Optimization 2021-06-28, Query Optimization 2021-07-12, Query Optimization 2021-07-26, QO 2021-09-06, Query Optimization 2021-08-09, QO 2021-09-20, QO 2021-10-04, QO 2021-10-18, QO 2021-11-01, QO 2021-11-15, QO 2021-11-29, QO 2021-12-13, QO 2021-12-27, QO 2022-01-10, QO 2022-01-24, QO 2022-02-07, QO 2022-02-21, QO 2022-03-07, QO 2021-08-23
Participants:
Case:

 Description   

Since v4.0.9, $nin queries containing arrays are no longer possible when the notablescan setting is enabled on the server. The server will always throw a "No query solutions" error, due to SERVER-38949.

original description

Since v4.0.9, $nin queries are no longer possible when the notablescan setting is enabled on the server. The server will always throw a "No query solutions" error, because indexes made for the fields being queried are not respected for a $nin query, resulting in a full-table scan and the error. This is most likely due to SERVER-38949

 

 



 Comments   
Comment by Githook User [ 24/Feb/22 ]

Author:

{'name': 'Jess Balint', 'email': 'jbalint@gmail.com', 'username': 'jbalint'}

Message: SERVER-40691 $nin:[[],...] queries are not indexed

(cherry picked from commit df25c71b8674a78e17468f48bcda5285decb9246)
Branch: v4.2
https://github.com/mongodb/mongo/commit/e68a7d47305e14e090cba9ce3d92533053299996

Comment by Githook User [ 24/Feb/22 ]

Author:

{'name': 'Jess Balint', 'email': 'jbalint@gmail.com', 'username': 'jbalint'}

Message: SERVER-40691 $nin:[[],...] queries are not indexed

(cherry picked from commit 6889d8d80d0e69cc5c3de68eb05fe89e9b93cff4)
Branch: v4.4
https://github.com/mongodb/mongo/commit/df25c71b8674a78e17468f48bcda5285decb9246

Comment by Githook User [ 23/Feb/22 ]

Author:

{'name': 'Jess Balint', 'email': 'jbalint@gmail.com', 'username': 'jbalint'}

Message: SERVER-40691 $nin:[[],...] queries are not indexed
Branch: v5.0
https://github.com/mongodb/mongo/commit/6889d8d80d0e69cc5c3de68eb05fe89e9b93cff4

Comment by Githook User [ 22/Feb/22 ]

Author:

{'name': 'Jess Balint', 'email': 'jbalint@gmail.com', 'username': 'jbalint'}

Message: SERVER-40691 $nin:[[],...] queries are not indexed #3420
Branch: master
https://github.com/mongodb/mongo/commit/54b918d73c63483faecedb807722a707ba0e64c2

Comment by Asya Kamsky [ 17/Apr/19 ]

I think the fix for SERVER-38949 could have excluded specifically empty array in $nin list from prohibition to use an index. This ticket should be to consider whether it makes sense to do that.

Comment by Asya Kamsky [ 17/Apr/19 ]

Note that the following workaround will use the index in your specific case (a is that field):

{$and:[{a:{$ne:null}},{a:{$ne:[]}}]}

Comment by Eric Sedor [ 17/Apr/19 ]

To clarify, No query solutions is going to be thrown when the $nin array contains an array (e.g., $nin: [[],null] vs. $nin:[1,2]. This is due to the index prohibition on $ne:[[any]].

Comment by Asya Kamsky [ 17/Apr/19 ]

tubbo can you provide the actual document and query you are using?

The bug you reference fixed a correctness problem, so if this is causing an issue for you, it may be that you were already not getting correct results, and therefore might need to rewrite the query.

Comment by Eric Sedor [ 17/Apr/19 ]

Thanks for the added step, tubbo; An outright exception for $nin may be unlikely, as the purpose of notablescan is to protect against the possible impact of unindexed queries.

One potential workaround is to contrive a query on a compound index (e.g., query for {a:<val>,b:{ $nin: [[], null]}} with the goal of using index {a:1,b:1}).

I am looking into the permanence of the index ban on $nin.

Generated at Thu Feb 08 04:55:43 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.