Bitwise operators are useful because they allow you to perform efficient and concise operations on individual bits within integer values, which can be very useful in a variety of SQL queries.
CrateDB continues to provide many valuable features. In the v5.2, we added support for bitwise operators. Now, you may wonder when this feature is handy. There are at least a couple of scenarios:
If you want to store multiple pieces of information in a single column. For example, you can use a bitwise OR operator to combine multiple flags into a single value.
Simplifying conditional statements. Bitwise operators can be used to check the state of individual bits within a value. For example, you can use a bitwise AND operator to check if a particular bit is set or not.
Easier data manipulation. Bitwise operators can be used to set or manipulate specific bits within a value. For example, you can use a bitwise OR operator to set a particular bit to 1, or a bitwise XOR to swap bit value (1 to 0 or 0 to 1).
CrateDB supports three bitwise operators:
BITWISE AND (&)
BITWISE OR (|)
BITWISE XOR (#)
Now, let’s take a look at each operator and some interesting examples.
Bitwise AND
The Bitwise AND operator compares each bit of two values and returns a new value with the bits that are set in both of the original values.
The syntax for this operator is as follows:
SELECT value1 & value2
Here, value1 and value2 are the two values that you want to compare using the bitwise AND operator. These values can be any valid expressions or constants in SQL, such as columns, variables, or literals.
For example, let’s imagine a table of employees with a column status that stores a bitmask value representing the status of each employee. The status value is stored as a byte and each bit represents a different aspect of the employee status. To save storage space, we recommend a byte data structure for storing up to 7 states simultaneously. The first bit always represents a sign and we don't use negative values to encode states. Similarly, use short data structure for storing up to 15 states, integer for up to 31 states, long for up to 63 states, and for more than 63 states that use Bit String type. To learn more about data types supported in CrateDB, check out our documentation.
The first bit in status value says whether the employee is working full-time, the second bit says if the employee is remotely, and so on. For instance, if the employee is working full-time, the status value will be 1 (B'01') if remote the status value will be 2 (B'10') and if both, the status value will be 3 (B'11').
Now, let’s create a table and populate it with sample data
CREATE TABLE employees (name text, status BYTE, comment TEXT)INSERT INTO employees (name ,status, comment) VALUES ('Ana', 1, 'Ana is working full-time from office'),('Mary', 3, 'Mary is working full-time remotely'),('Sara', 2, 'Sara is working part-time remotly');
To select all employees who are working full-time:
SELECT name, comment FROM employees WHERE status & 1 = 1 # Ana, Mary
This query will select all rows from the employees table where the first bit (full-time status) is set to 1.
To find all employees who are working full-time and remotely:
SELECT name, comment FROM employees where status & 3 = 3 # Mary
You can also use the bitwise AND operator in combination with other logical operators, such as OR and NOT, to create more complex queries.
Bitwise OR
The bitwise OR operator is used to compare two binary values and return a new binary value where the resulting bit is set to 1 if either of the input bits is 1. It is represented by the symbol|.
To understand how the bitwise OR operator works, let's consider two binary values: 1011 and 1100. The bitwise OR operation would compare each bit position of the two values and return a new binary value based on the following rules:
If either of the input bits is 1, the resulting bit is set to 1.
If both of the input bits are 0, the resulting bit is set to 0.
Applying these rules to the example above, we get the following result: 1011 | 1100 = 1111
The resulting binary value is 1111, which is equivalent to 15 in decimal.
Considering the example with employees table, let’s say we would like to select all employees that are working full-time, remotely, or both. Here's how you can use the bitwise OR operator in the query:
SELECT name, comment FROM employees where status & (1 | 2) > 0;
The following query illustrates how to change a flag specifying whether an employee is working full-time without changing the existing flags:
UPDATE employeesSET status = (status | 1 /* FULL-TIME */) WHERE name='Sara'
It's important to note that the bitwise OR operator only works with binary values. If you want to perform a logical OR operation with non-binary values, you can use the OR operator in SQL.
Bitwise XOR
The bitwise XOR operator, represented by the symbol #, compares two binary values and returns a new value based on the following rules:
- If both values are 0, the result is 0.
- If both values are 1, the result is 0.
- If one value is 1 and the other is 0, the result is 1.
Using these rules in the example from above, the result of the XOR operator is: 1011 # 1100 = 0111
One interesting example of using the bitwise XOR operator is changing the status of an employee from “working remotely” to “working in the office“ and vice versa.UPDATE employees
SET status = status # 2
WHERE name = 'Ana';
In this example, the status of the employee with the name Ana will be toggled from “working remotely“ to “working in the office“ or vice versa, depending on the current value of the status field.
Wrap up
In summary, the bitwise operators in CrateDB allow you to perform bitmasking operations on values in your database and use the resulting values to filter or modify rows in your tables. By combining this operator with other logical operators, you can create powerful queries that can manipulate and extract specific data from your database. If you have any further questions or would like to learn more about CrateDB, check out our documentation and join the CrateDB community.