[SERVER-31876] $ne has inconsistent behavior Created: 08/Nov/17 Updated: 27/Oct/23 Resolved: 22/Mar/19 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 3.4.9 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Johnny | Assignee: | Backlog - Query Team (Inactive) |
| Resolution: | Works as Designed | Votes: | 2 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||
| Assigned Teams: |
Query
|
||||||||||||||||
| Operating System: | ALL | ||||||||||||||||
| Steps To Reproduce: |
|
||||||||||||||||
| Participants: | |||||||||||||||||
| Description |
|
`$ne` has very inconsistent behavior when used with arrays. This behavior also differs when using `$elemMatch`, while the `$elemMatch` docs state that with one query, the results should be identical. |
| Comments |
| Comment by Asya Kamsky [ 22/Mar/19 ] | |||||||||
|
Currently the query system is working as designed on examples mentioned in this ticket. We should however clarify the null and inequality semantics in our documentation (perhaps with additional examples). | |||||||||
| Comment by Jeremy Todd [ 18/Dec/17 ] | |||||||||
|
Indeed, this could simply be clarified in the docs. Is it by design that $eq -> $ne becomes a top-level negation? I now see arguments either way. My actual use case looked more like this. I had a rather involved query that I had written years ago, something more like:
array5.field5 could be 7, 8 or 9. I changed the "7" to "{$ne:7}" without rethinking the rest of the query. I was quite surprised when the behavior of the entire query changed (e.g. now I get results where array5 doesn't even exist), since presumably the $ne negation happened at a higher level. On the other hand, my earlier statement about $eq:x and $ne:x partitioning the results is important too. I'm not sure how you would trade those two (or if I've fully characterized the problem) but anyway this is what led to the bug report. | |||||||||
| Comment by Asya Kamsky [ 18/Dec/17 ] | |||||||||
|
I think the difference is whether the switch from $eq to $ne can be pulled out to a top level negation. In cases of $elemMatch they cannot be - perhaps additional documentation explanation or maybe just a few more examples would help clarify this? | |||||||||
| Comment by Jeremy Todd [ 17/Dec/17 ] | |||||||||
|
Hi Asya, Sorry about that, I came back to this without looking at it closely enough. Indeed my statement holds for {'a.b':null}and {'a.b':{$ne:null}}. It doesn't hold for {a:{$elemMatch:{b:{$eq:null}}}} and {a:{$elemMatch:{b:{$ne:null}}}}. As Johnny mentioned in the stackoverflow post, it seems that {'a.b':null}applies {b:null}element-wise to the elements of array a. It seems that {'a.b':{$ne:null}} applies {b:null}element-wise and then applies the "$not" operator to the query as a whole, which was unexpected. If this is by design then indeed I guess the mongo designers agree that my statement should hold, since it's implemented that way. In my case I had a complex query with {$eq:v} buried in it. I needed to change that to {$ne:v} and assumed the rest of the query would behave the same way, but it didn't. $elemMatch seems to consistently work element-wise, but if the above is by design then the docs need clarification under "single query condition" (since $elemMatch is sometimes needed even with a single query condition). Regards, | |||||||||
| Comment by Asya Kamsky [ 16/Dec/17 ] | |||||||||
Is there an example where that is not the case? I tried it with examples given in this ticket and the statement you made holds. Asya | |||||||||
| Comment by Jeremy Todd [ 05/Dec/17 ] | |||||||||
|
Hi, I posted the question to Stack Overflow about this, thanks for taking a look. There are indeed a number of confusing aspects, but I think the most important is this one: I was expecting that queries of the form "x: null" and "x: {$ne: null}" would partition the documents in a collection, i.e. a document matches "x: null" or "x: {$ne: null}" (but it does not match neither and it does not match both). If x is a field of the document then the above seems to be true. I think this is extremely important and could not possibly change at this point without breaking a lot of deployments, even though the documentation is unclear on it. In the case of more complex structures (arrays, nested arrays etc), as Johnny described, the above doesn't seem to be true, although clearly there are some details which aren't fully understood (at least by me at this point). Regards, | |||||||||
| Comment by Johnny [ 05/Dec/17 ] | |||||||||
|
@Asya Kamsky Ah yes, I see that now. But you do agree that the cases not using $elemMatch are incorrect? I assume for the second document/case since b is undefined, this is internally the same as (b : null), which is why it doesn't match (b : ($ne: null)). But note this goes against what is said in $ne's documentation: "includes documents that do not contain the field." The third document/case also is very odd, as it works if you remove (d: 1). | |||||||||
| Comment by Asya Kamsky [ 05/Dec/17 ] | |||||||||
|
Please note that
The format needed to match the third document correctly is:
| |||||||||
| Comment by Mark Agarunov [ 09/Nov/17 ] | |||||||||
|
Hello jjaco16, Thank you for the detailed example. I've managed to reproduce this issue and I've set the fixVersion to "Needs Triage" for this ticket to be scheduled against our currently planned work. Updates will be posted on this ticket as they are available. Thanks, | |||||||||
| Comment by Johnny [ 08/Nov/17 ] | |||||||||
|
Sorry, didn't realize I couldn't edit issue after creating. Please see below for complete reproduction with better formatting. (Or if you can edit this comment into the issue, that'd be better). The following all run on mongo 3.4.9 terminal from: https://docs.mongodb.com/manual/tutorial
You can see there are several issues here which don't make sense, and using elemMatch on a single <query> gives different results, against what the docs claim. There is a stackoverflow post with more examples here: https://stackoverflow.com/q/47112990/3745896 |