[SERVER-3518] Bitwise query operators Created: 02/Aug/11 Updated: 16/Nov/21 Resolved: 14/Jul/15 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | 3.1.6 |
| Type: | New Feature | Priority: | Major - P3 |
| Reporter: | Zaur Nasibov | Assignee: | Qingyang Chen |
| Resolution: | Done | Votes: | 114 |
| Labels: | bitwise, query | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
All |
||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||||||||||||||||||||||||||||||||||||||
| Sprint: | Quint Iteration 6 | ||||||||||||||||||||||||||||||||||||||||||||||||||||
| Participants: |
Alain Cordier, Alex, alex 1829, Alfredo, Ali Gangji, Ali Rahbari, Andrew Reslan [X], Asya Kamsky, Craig Wilson, Dan, David Storch, Fred, Githook User, guipulsar, Hans Stevens, Ian Whalen, Jake W, Jeremy, Jérémy Bethmont, Jon Keating, Joseph Chen, Ken Kopelson, Ken Williams, Lars Plessmann, Michael Korbakov, Mike Urbanski, Nick Fenwick, nifan, nuk nuk san, Omanand Jha Vatsa, Patrick Boos, Pavel Litvinenko, Polo Wang, Qingyang Chen, Ramon Fernandez Marina, Roshan Bhandari, Shannon Weyrick, Szaniszlo Szoke, Vincent Robert, Will Stanley, Zaur Nasibov
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
We've decided to implement a few bit-test query operators: $bitsAllSet - matches if the input bit positions are all 1 These operators can take an array of bit positions, a 64-bit number bitmask, or a BinData bitmask. For the number and BinData bitmasks, the bit positions to check are the positions of 1's in their binary representation. The operators will only match against int32, int64, doubles, and binary data. Other types will not match. Note that bit position 0 means the least significant bit. An example using the array of bit positions syntax:
An example using the bitmask syntax:
|
| Comments |
| Comment by Pavel Litvinenko [ 04/Oct/17 ] | |||||||||
|
I would like to pay attention to this issue, this function is very need in my project | |||||||||
| Comment by Ramon Fernandez Marina [ 05/Oct/15 ] | |||||||||
|
rahbari, this is indeed a limitation of this first implementation. Further work may include the use of indexes, but we haven't created a ticket for that yet – feel free to do so if you wish. | |||||||||
| Comment by Ali Rahbari [ 03/Oct/15 ] | |||||||||
|
I can understand why bitwise operator doesn’t utilize indexes, But I wonder if they are matched against an indexed field, the data in memory is used for comparison but a full table scan from storage is done? | |||||||||
| Comment by Craig Wilson [ 28/Aug/15 ] | |||||||||
|
Hi Nick, We have a google groups list that is a great place to ask these types of questions. To answer you here, the current driver doesn't have strontly-typed support for this yet. It will be coming, likely in version 2.2 (which will line up with server 3.2). Until then, you can always construct this query manually using a BsonDocument...
Craig | |||||||||
| Comment by Nick Fenwick [ 28/Aug/15 ] | |||||||||
|
Not sure if this is on-topic, but how would we filter for this using the Expression syntax in C# using the official driver? e.g. According to Properties on the MongoDB.Driver reference on our project we're using 2.0.1.27. In order to get bitwise support in Mongo I had to uninstall 3.0.6 from our Ubuntu 14.04 server and set up a 3.1 repository and install the mongodb-org-unstable* group of packages. How can we get a C# driver that also supports these new features? | |||||||||
| Comment by Githook User [ 16/Jul/15 ] | |||||||||
|
Author: {u'username': u'coollog', u'name': u'Qingyang Chen', u'email': u'qingyang.chen@10gen.com'}Message: | |||||||||
| Comment by Asya Kamsky [ 15/Jul/15 ] | |||||||||
|
Regarding index use by $bitwise query operators, please create a new ticket requesting this new feature. Keep in mind that since currently in MongoDB Indexes hold the values that are searched for it would not be a trivial task to implement it. nifan discussion of performance of different ways of implementing this probably fits better on mongodb-user google group rather than in this ticket. | |||||||||
| Comment by nifan [ 15/Jul/15 ] | |||||||||
|
IMO the array of list values is a workaround in itself for these cases which we would like to get rid of. An array will not work with capped collections, since adding a flag will change the size of the document. Which should all performance significantly worse then an in-place update on an int or byte field. The index would probably be much more compact then indexing a list of things and querying by bitmask probably faster then doing a ["foo", "bar", "egg"] in some-list for each document. For my use cases I'm with Jeremy; I really like the new operators but they are not very helpful if they cannot use an index. | |||||||||
| Comment by Asya Kamsky [ 14/Jul/15 ] | |||||||||
|
Since $where involves JavaScript and these operators would run on the server natively, $where will likely be slower in all cases. Don't forget that in cases where there are other conditions in addition to the bitwise operator, those conditions can use available indexes, meanwhile if using array of list values is working well for you, there should be no reason to switch. In addition, $where cannot be used in some cases where the bitwise operators can be used (i.e. aggregation framework $match phase, in nested documents ie $elemMatch). | |||||||||
| Comment by Jeremy [ 14/Jul/15 ] | |||||||||
|
Glad to see progress here, but without using an index, will this perform any better than the previous solution of using the $where. e.g. '$where'=>'!(this.code & 8)’? Bitwise operators that don't use indexes will not help us at all. Will stick to array list of values until this happens. | |||||||||
| Comment by Asya Kamsky [ 14/Jul/15 ] | |||||||||
|
Index behavior will be documented, but keep in mind that this is not a new scenario - many regular expressions cannot use indexes either. | |||||||||
| Comment by nifan [ 14/Jul/15 ] | |||||||||
|
David Storch, is there a ticket for that we can follow and vote on ? Could we also add a hint about not supporting indexes (yet) to the documentation ? As I think people would expect queries to be able to use indexes for bitwise queries and might be disappointed and confused when queries turn out to be slower then an equivalent none bitwise schema. (eg: indexed list of permissions/flags/status vs indexed integer field with bitmask operations) | |||||||||
| Comment by David Storch [ 14/Jul/15 ] | |||||||||
|
nifan, no, the query predicates introduced in this ticket will never be answered using an index. This is conceivable as future work, but is not currently planned. | |||||||||
| Comment by nifan [ 14/Jul/15 ] | |||||||||
|
Just to be sure, will bit wise query operations take advantage of an (compound) index on the field ? | |||||||||
| Comment by Githook User [ 14/Jul/15 ] | |||||||||
|
Author: {u'username': u'coollog', u'name': u'Qingyang Chen', u'email': u'qingyang.chen@10gen.com'}Message: | |||||||||
| Comment by David Storch [ 01/Jul/15 ] | |||||||||
|
nifan, thanks! I updated the description based on your example. | |||||||||
| Comment by nifan [ 01/Jul/15 ] | |||||||||
|
Ah I was assuming using the most significant bit first. (going left to right) Can we chose a different example ? for example 43:
That example is would have only one way to interpret the result I think. I also removed the 5'th bit position to indicate that not all the indexes have to be specified to match 43. | |||||||||
| Comment by David Storch [ 01/Jul/15 ] | |||||||||
|
nifan, I believe that the bit positions are zero-based, not one-based. The following shows the indexes of each bit for the binary representation of 54:
This is why 54 matches when you ask for bit positions 1, 2, 4, and 5. Does this clarify the specification of the feature? Best, | |||||||||
| Comment by nifan [ 01/Jul/15 ] | |||||||||
|
Let's be careful that the documentation will reflect that this is 1-based not 0-based. One might expect it would be 0-based.
| |||||||||
| Comment by Qingyang Chen [ 01/Jul/15 ] | |||||||||
|
Hi pulsar: This feature is planned and under development. I have updated the description of the ticket to reflect what we have decided to implement. | |||||||||
| Comment by Asya Kamsky [ 01/Jul/15 ] | |||||||||
|
When the fix version is set (as it is now) to a specific release, it means we have scheduled for this feature to go into that release. In the case of this ticket, it's targeted for 3.1.6 dev release - meaning that it would be in 3.2 production release, scheduled for later this year. The status indicates that the work is "in progress" so I would say that it's not just planned, it's already scheduled. Asya Kamsky | |||||||||
| Comment by guipulsar [ 01/Jul/15 ] | |||||||||
|
hi, We need to know if this feature is planned or not ? this is very important for us dev logic , regards | |||||||||
| Comment by Jeremy [ 30/Mar/15 ] | |||||||||
|
Agreed. Can we please, please, pretty please get this feature? | |||||||||
| Comment by Alain Cordier [ 30/Mar/15 ] | |||||||||
|
I cant understand how the $bit operator for update can be available for this long time with no counterpart in queries. Seems completely silly to me ! There was an attempt to implement it here: https://github.com/mongodb/mongo/pull/168, but it was cancelled because the code evolved too much... | |||||||||
| Comment by Vincent Robert [ 27/Dec/14 ] | |||||||||
|
We fully agree with the comments of Guipulsar and our position is similar. We have requested this feature a very long time ago and we still have no planning/schedule for the implementation. This is not serious. | |||||||||
| Comment by guipulsar [ 27/Dec/14 ] | |||||||||
|
thhanks for ur alternative propositions but concerning bitwise there are no really alternative , thats why its so much important thing and future, i will continue to pushup this feature request , honestly i think mongo devloppers made a serious mistake when flagging this at " trivial " and "planing but not scheduled " ; | |||||||||
| Comment by Ali Gangji [ 27/Dec/14 ] | |||||||||
|
I didn't say anything about 450 keywords. I don't know the details of your application. Maybe bitwise flags are more useful for your purposes. It's possible a different architecture would be required to eliminate your usage of bitwise flags. Awyways, I am not a MongoDB developer and have no control over their development path. I'm watching this issue myself because I'm interested in this feature becoming available. I'm only trying to help point you to the alternatives that exist today. I wish you good luck! | |||||||||
| Comment by guipulsar [ 27/Dec/14 ] | |||||||||
|
im simply not agree at all.. first our flow has been reflect from years, all apps input are capped to 50 , all this discussion has been benchmarked for a while you know.. Think an application who search for 15 keywords with 30 variation for each ..You continue to agree for putting 450 keywords string in ur post ? lol | |||||||||
| Comment by Ali Gangji [ 27/Dec/14 ] | |||||||||
|
That doesn't seem any easier to work with. You're right that the query itself is shorter because you can add the values you are looking for rather than enumerate them. However, if you have 30 bitwise flags you certainly won't be working with numbers like 126. 2^30 is 1073741824. Using bigint, once you've hit 62 flags, that is the highest number of bitwise flags you can have and store them all together and you're reaching the limit and using up a 64-bit int. Secondly, an embedded array can be indexed by MongoDB. So by using arrays you won't be limited in the number of possible values or the size of int/bigint AND your query can use an index and be very fast. It's also easier to manage because you don't have to convert your flags to their bit position. The only disadvantage is a longer query, which i'd say is trivial. | |||||||||
| Comment by guipulsar [ 27/Dec/14 ] | |||||||||
|
number will always more simple .. imagine a common flow with a ton of keywords, with bitwise | |||||||||
| Comment by Ali Gangji [ 24/Dec/14 ] | |||||||||
|
I can only guess as to why this has been deemed low priority. In my experience, this really has limited applications and can always be replaced by something more robust and scalable. MongoDB supports arrays, which can be used in much the same way. I would say you are better off using arrays rather than holding your breath for bitwise operators. Array query operators - http://docs.mongodb.org/manual/reference/operator/query-array/ | |||||||||
| Comment by guipulsar [ 24/Dec/14 ] | |||||||||
|
oh gosh theres no bitwise support actually ?!? | |||||||||
| Comment by Alfredo [ 19/Nov/14 ] | |||||||||
|
Also, I feel pretty strongly that "Trivial", regardless of actual context, is a misnomer for this feature. Overall DB size, query performance and bitwise implementations are affected by this. Just my $0.02 | |||||||||
| Comment by Alfredo [ 19/Nov/14 ] | |||||||||
|
Where are we at with this folks (cc: 10gen/Mongodb Inc) ? Crucial stuff here for lots of folks. | |||||||||
| Comment by Lars Plessmann [ 10/Oct/14 ] | |||||||||
|
db.collection.findOne({field: {$bit: { or: 1 }}}) | |||||||||
| Comment by Ken Kopelson [ 02/Aug/14 ] | |||||||||
|
Having the ability to query based on bits is extremely important if one is to use the $bit operator at all. The whole purpose for the $bit operator in the first place is to store things like permission bits and so forth. The current $bit update operator is a step in the right direction, but what you do not have is the ability to turn bits off, since you don't support the "invert" operation at all, and it seems from the examples that you only support a single bit operation on a field. To turn bits off you need to do both an AND and an INVERT, so it might be easiest to support a single "off" which does & and ~: Turn bits on: $bit: {value: { on: 0x34 }} <-- this would do (value = value | 0x34) Test for ANY bits in a mask: $bit: {value: {any: 0x34}} <-- this would do (value & 0x34) != 0 So, the point is, given the usual way that bits are used to store compact information, having XOR and AND by themselves for updating are of limited value. AND is really of importance when querying, but not having both ANY and ALL will require you to choose how the results of an AND operation are interpreted, and invariable you will get it wrong in half the cases. Better for you to support both and we will love you for it Please include this as soon as possible, as it is EXTREMELY useful and would be used by many people if made available. Cheers! | |||||||||
| Comment by Dan [ 31/May/14 ] | |||||||||
|
+1 for this feature. This would be a tremendous help. | |||||||||
| Comment by Hans Stevens [ 19/Mar/14 ] | |||||||||
|
+1 for this feature! | |||||||||
| Comment by Fred [ 17/Mar/14 ] | |||||||||
|
+1 for this feature. Would be really great addition. | |||||||||
| Comment by Jeremy [ 08/Feb/14 ] | |||||||||
|
Bump on this issue. 81 votes and still not assigned to anyone? | |||||||||
| Comment by Jake W [ 27/Nov/13 ] | |||||||||
|
Would really like to have this feature. Our data will be processed by different flows, and we need a field to record which flows have been run with the data. A single int field is good to record flow types via bitwise 'or', and then querying a specific flow type, we will need bitwise 'and'. | |||||||||
| Comment by Roshan Bhandari [ 26/Nov/13 ] | |||||||||
|
I would really appreciate if there were bitwise query operators ($bitand, $bitor). | |||||||||
| Comment by Jeremy [ 12/Nov/13 ] | |||||||||
|
Would really like this feature. We use a bitwise field for stacking together various error codes, but there is no way to filter on them in mongo without the javascript (slow) query. | |||||||||
| Comment by Ken Williams [ 25/Jul/13 ] | |||||||||
|
+1 for bitwise querying operators. Would really help subsetting the data into buckets. | |||||||||
| Comment by alex 1829 [ 05/Jun/13 ] | |||||||||
|
To extend Alex comment, workaround with scala + lift + casbah:
then :
Alexandre Richonnier | |||||||||
| Comment by Omanand Jha Vatsa [ 19/Apr/13 ] | |||||||||
|
+1 for $bitand support. | |||||||||
| Comment by Polo Wang [ 02/Apr/13 ] | |||||||||
|
+1 for bitwise operation on query cirteria | |||||||||
| Comment by nuk nuk san [ 22/Feb/13 ] | |||||||||
|
+1 for bitwise operations on numeric, +2 if we can get byte array or binary object of arbitrary size to support bit set operations, bloom filters etc. | |||||||||
| Comment by Alex [ 15/Feb/13 ] | |||||||||
|
+1 for merging. Why did you implement bitwise updates without possibility to query by mask? What is the use case? PS my workaround is .find({$where : "(this.FIELD & MASK) > 0"}) | |||||||||
| Comment by Joseph Chen [ 02/Feb/13 ] | |||||||||
|
+1 for merging @shannon's pull request. | |||||||||
| Comment by Will Stanley [ 26/Nov/12 ] | |||||||||
|
+1 for bitwise querying operators. | |||||||||
| Comment by Andrew Reslan [X] [ 30/Oct/12 ] | |||||||||
|
+1 I want to store chemical structure fingerprints as binary, querying will needs bitwise operators to find potential matches. | |||||||||
| Comment by Shannon Weyrick [ 14/Jun/12 ] | |||||||||
|
Ok, so I'm not sure there's a good way to query it back out like you need to in the latest version. My pending patch should solve your use case, though. It's waiting for review. | |||||||||
| Comment by Szaniszlo Szoke [ 14/Jun/12 ] | |||||||||
|
Thanks for the link, it really helps. , {value2: 1}. I still have to dig into that... | |||||||||
| Comment by Shannon Weyrick [ 14/Jun/12 ] | |||||||||
|
Szaniszlo, you can already do bitwise updates: http://www.mongodb.org/display/DOCS/Updating#Updating-%24bit .. this bug is related specifically to querying. What do your corresponding SELECTs look like? | |||||||||
| Comment by Szaniszlo Szoke [ 14/Jun/12 ] | |||||||||
|
I'm currently trying to move our database to MongoDB. But all privileges are managed by flags stored in integers. Bitwise operations are absolutely mandatory for us. | |||||||||
| Comment by Ian Whalen (Inactive) [ 08/Jun/12 ] | |||||||||
|
@shannon, right now we're basically in a code freeze to prep 2.2 - we'll be able to get someone to review this and talk about pulling it forward to 2.3 as soon as we've got 2.2 completed and available. | |||||||||
| Comment by Shannon Weyrick [ 05/Jun/12 ] | |||||||||
|
This has a pending pull request here: https://github.com/mongodb/mongo/pull/168 | |||||||||
| Comment by Patrick Boos [ 20/Feb/12 ] | |||||||||
|
+1 | |||||||||
| Comment by Jon Keating [ 20/Feb/12 ] | |||||||||
|
+1 from me too. | |||||||||
| Comment by Shannon Weyrick [ 25/Jan/12 ] | |||||||||
|
I've started some work on this here: https://github.com/weyrick/mongo/tree/server-3518 | |||||||||
| Comment by Jérémy Bethmont [ 12/Jan/12 ] | |||||||||
|
It would be great to have this implemented. Especially since it is already possible to do bitwise updates with $bit. I definitely second this feature request! | |||||||||
| Comment by Mike Urbanski [ 08/Dec/11 ] | |||||||||
|
I agree. I would love to move a large part of an application that I'm working on away from PostgreSQL, but, it relies heavily on bitwise queries. | |||||||||
| Comment by Michael Korbakov [ 23/Nov/11 ] | |||||||||
|
I second on this. Having bitwise queries enables some very memory-efficient structures like packed integers. It can be huge saving of index size. |