[SERVER-26655] $gt operation on array with index Created: 17/Oct/16 Updated: 06/Dec/22 Resolved: 25/Jan/21 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | alexnikleo | Assignee: | Backlog - Query Optimization |
| Resolution: | Duplicate | Votes: | 6 |
| Labels: | mql-semantics, query-44-grooming | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||||||||||||||
| Operating System: | ALL | ||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||
| Description |
|
Following example works incorrect The first `find` fetches zero values, the second `find` fetches exactly what it should fetch. |
| Comments |
| Comment by David Storch [ 25/Jan/21 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
asya apologies for the delayed response! I did a bit of digging and it looks like this was fixed and backported by ted.tuckman under | ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 11/Nov/20 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
I was not able to reproduce this in version 4.4 - which suggests that this was fixed between 4.0 and 4.4. | ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Vokail [ 22/Feb/19 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
Any plan for fixing this ? I was able to reproduce the problem on Mongodb 4.0.4, Os Windows 10, storage engine Wiredtiger | ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 02/Dec/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
jimrandomh if you are talking about array values, then you are correct. In current MQL semantics operations apply both to top level value as well as values inside an array, so when there is a multikey index (index on a field which contains array) then we always end up having to scan the entire index.
| ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by James Babcock [ 28/Nov/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
As far as I can tell, reading this thread and the StackOverflow thread, there is currently no way to search for "has field and it is not empty" which is correct, works regardless of the array element type, uses an index, and avoids scanning the entire index. We're giving up and denormalizing to work around this bug. | ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 01/Jun/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
Answering "does this array exist" should not be using comparison to array - that's not a valid test for that. What would be correct are any of the following:
| ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Niko Fink [ 22/May/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
It would be great to have a documented single best way to answer the "does this array exist" question, regardless of whether an index is used. There is a tutorial on querying for missing values, but neither does it say anything about indices nor is there any mention of doing the exact opposite, which is what we actually want here. And as it seems, there is no actual way of answering this question right now as long as indices are / should be involved, but please correct me if I'm wrong here. As the question is pretty common and the wrong answers on stackoverflow are very visible - being the first result for a "mongo array not empty" google search while the first (and also accepted and most voted) answer is wrong - could we please have some official guidance on this? | ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 22/May/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
Unfortunately, the linked SO questions includes a lot of good and bad advice but fundamentally when the question is "does this array exist" the answer should never be "use $gt with array" - $gt is an operator that compares scalar values meaningfully, it's not at all clear what its semantics when comparing array values should be.
| ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Niko Fink [ 17/May/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
There is some discussion together with tests and currently possible solutions on Stackoverflow: | ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 26/Dec/17 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
Additional test case that is incorrect when there's an index on "field":
This is with 3.6.0 server. I think there are two problems here, one is indexing null-ish value, but the other is doing type bracketing when "undefined" is involved in indexed "find". | ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Ian Whalen (Inactive) [ 14/Jul/17 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
The plan is to consider this problem again when we start planning for 3.8. | ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by David Storch [ 17/Oct/16 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi alexnikleo, Thanks for reporting this issue! This is certainly a bug in that the semantics of comparison-to-array are not consistently enforced by the query system. What exactly those semantics should be is a topic that has come up before (see, for example, Best, | ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Ramon Fernandez Marina [ 17/Oct/16 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
I'm able to reproduce the behavior you describe: the find() command on line 12 does not return data, while the find() command on line 14 returns the following:
| ||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Ramon Fernandez Marina [ 17/Oct/16 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
Thanks for opening a bug report alexnikleo, I'm adding your sample code below for convenience:
|