[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: |
|
||||||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||||||
| Operating System: | ALL | ||||||||||||||||||||
| Backport Requested: |
v5.3, v5.2, v5.0, v4.4, v4.2
|
||||||||||||||||||||
| Steps To Reproduce: |
|
||||||||||||||||||||
| 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: | (copied to CRM) | ||||||||||||||||||||
| 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 original descriptionSince 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
|
| Comments |
| Comment by Githook User [ 24/Feb/22 ] | |
|
Author: {'name': 'Jess Balint', 'email': 'jbalint@gmail.com', 'username': 'jbalint'}Message: (cherry picked from commit df25c71b8674a78e17468f48bcda5285decb9246) | |
| Comment by Githook User [ 24/Feb/22 ] | |
|
Author: {'name': 'Jess Balint', 'email': 'jbalint@gmail.com', 'username': 'jbalint'}Message: (cherry picked from commit 6889d8d80d0e69cc5c3de68eb05fe89e9b93cff4) | |
| Comment by Githook User [ 23/Feb/22 ] | |
|
Author: {'name': 'Jess Balint', 'email': 'jbalint@gmail.com', 'username': 'jbalint'}Message: | |
| Comment by Githook User [ 22/Feb/22 ] | |
|
Author: {'name': 'Jess Balint', 'email': 'jbalint@gmail.com', 'username': 'jbalint'}Message: | |
| Comment by Asya Kamsky [ 17/Apr/19 ] | |
|
I think the fix for | |
| Comment by Asya Kamsky [ 17/Apr/19 ] | |
|
Note that the following workaround will use the index in your specific case (a is that field):
| |
| 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. |