Bitwise Functions in N1QL

Bitwise Functions in N1QL

All Bit/Binary functions can only operate on 64-bit signed integers.
Note: All non-integer numbers and other data types result in null.
Note: Couchbase Server uses two's complement representation.

When looking at the value in binary form, bit 1 is the Least Significant Bit (LSB) and bit 32 is the Most Significant Bit.

(MSB) Bit 32 → 0000 0000 0000 0000 0000 0000 0000 0000 ← Bit 1 (LSB)

BITAND (int1, int2)

Description
Returns the result of a bitwise AND operation performed on two integer values.
The bitwise AND operation compares each bit of int1 to the corresponding bit of int2. If both bits are 1, then the corresponding result bit is set to 1; otherwise it is set to 0 (zero).
Arguments
int1, int2
Integers, or any valid expressions which evaluates to integers, that are used to compare.
Return Value
An integer, representing the bitwise AND between the two input integers.
Limitations
Input values must be integers (such as 1 or 1.0) and cannot contain decimals (such as 1.2).

Example 1: Compare 3 (0011 in binary) and 6 (0110 in binary).

SELECT BITAND(3,6) AS BitAND;

results: [
   "BitAND": 2
]

This results in 2 (0010 in binary) because only bit 2 is set in both 3 (0011) and 6 (0110).

Example 2: Compare 4.5 and 3 (0011 in binary).

SELECT BITAND(4.5,3) AS BitAND;

results: [
   "BitAND": null
]

This results in null because 4.5 is not an integer.

Example 3: Compare 4.0 (0100 in binary) and 3 (0011 in binary).

SELECT BITAND(4.0,3) AS BitAND;

results: [
   "BitAND": 0
]

This results in 0 (zero) because 4.0 (0100) and 3 (0011) do not share any bits that are both 1.

BITCLEAR (int_value, bit_position)

Description
Returns the result after clearing the specified bit, or array of bits in int1 using the given bit_positions. Specifying a negative or zero bit position does not result in any change to the value.
Note: Specifying a negative or zero bit position makes the function return a null.
Arguments
int_value
An integer, or any valid expression which evaluates to an integer, that contains the target bit or bits to clear.
bit_position
An integer or an array of integers specifying the position or positions to be cleared.
Return Value
An integer, representing the result after clearing the bit or bits specified.
Limitations
Input values must be integers (such as 1 or 1.0) and cannot contain decimals (such as 1.2).
Example 1: Clear bit 1 from 6 (0110 in binary).
SELECT BITCLEAR(6,1) AS BitCLEAR;

results: [
   "BitCLEAR": 6
]
This results in 6 (011 0 in binary) because bit 1 was already zero.

Example 2: Clear bits 1 and 2 from 6 (0110 in binary).

SELECT BITCLEAR(6,[1,2]) AS BitCLEAR;

results: [
   "BitCLEAR": 4
]
This results in 4 (01 00 in binary) because bit 2 changed to zero.

Example 3: Clear bits 1, 2, 4, and 5 from 31 (0 111 11 in binary).
SELECT BITCLEAR(31,[1,2,4,5]) AS BitCLEAR;

results: [
   "BitCLEAR": 4
]
This results in 4 (0 001 00) because bits 1, 2, 4, and 5 changed to zero.

BITNOT (int_value)

Description
Returns the results of a bitwise logical NOT operation performed on an integer value.
The bitwise logical NOT operation reverses the bits in the value. For each value bit that is 1, the corresponding result bit will be set to 0 (zero); and for each value bit that is 0 (zero), the corresponding result bit will be set to 1.
Note: All bits of the integer will be altered by this operation.
Arguments
int_value
An integer, or any valid expression which evaluates to an integer, that contains the target bit or bits to clear.
Return Value
An integer, representing the result after performing the logical NOT operation.
Limitations
Input values must be integers (such as 1 or 1.0) and cannot contain decimals (such as 1.2).
Example 1: Perform the NOT operation on 3 (0000 0000 0000 0000 0000 0000 0000 0011 in binary).
SELECT BITNOT(3) AS BitNOT;

results: [
   "BitNOT": -4
]
This results in -4 ( 1111 1111 1111 1111 1111 1111 1111 1100 in binary) because all bits changed.

BITOR (int_value1, int_value2)

Description
Returns the result of a bitwise inclusive OR operation performed on two integer values.
The bitwise inclusive OR operation compares each bit of int1 to the corresponding bit of int2. If either bit is 1, the corresponding result bit is set to 1; otherwise, it is set to 0 (zero).
Arguments
int_value1, int_value2
Integers, or any valid expressions which evaluate to integers, that are used to compare.
Return Value
An integer, representing the bitwise OR between the two input integers.
Limitations
Input values must be integers (such as 1 or 1.0) and cannot contain decimals (such as 1.2).
Example 1: Perform OR on 3 (0011 in binary) and 6 (0110 in binary).
SELECT BITOR(3,6) AS BitOR;

results: [
   "BitOR": 7
]
This results in 7 (0 111 in binary) because at least 1 bit of each (00 11 and 0 110) is 1 in bits 1, 2, and 3.

Example 2: Perform OR on 3 (0011 in binary) and -4 (1000 0000 0000 ... 0000 1100 in binary).
SELECT BITOR(3,-4) AS BitOR;

results: [
   "BitOR": -1
]
This results in -1 ( 1111 1111 1111 ... 1111 1111 in binary) because the two 1 bits in 3 fill in the two 0 bits in -4 to turn on all the bits.

BITSET (int_value, position)

Description
Returns the result after setting the specified bit position, or array of bit positions, to 1 in the int_value given.
Specifying a negative or zero bit position does not result in any change to the value. If the bit is already set, then it stays set.
Note: Specifying a negative or zero position makes the function return a null.
Arguments
int_value
An integer, or any valid expression which evaluates to an integer, that contains the target bit or bits to clear.
position
An integer or an array of integers specifying the position or positions to be set.
Return Value
An integer, representing the result after setting the bit or bits specified.
Limitations
Input values must be integers (such as 1 or 1.0) and cannot contain decimals (such as 1.2).
Example 1: Set bit 1 in the value 6 (011 0 in binary).
SELECT BITSET(6,1) AS BitSET;

results: [
   "BitSET": 7
]
This results in 7 (011 1 in binary) because bit 1 changed to 1.

Example 2: Set bits 1 and 2 in the value 6 (01 10 in binary).
SELECT BITSET(6,[1,2]) AS BitSET;

results: [
   "BitSET": 7
]

This also results in 7 (0111 in binary) because bit 1 changed while bit 2 remained the same.

Example 3: Set bits 1 and 4 in the value 6 ( 011 0 in binary).
SELECT BITSET(6,[1,4]) AS BitSET;

results: [
   "BitSET": 15
]
This results in 15 ( 111 1 in binary) because bit 1 and 4 changed to ones.

BITSHIFT (int_value, shift_amount, rotate)

Description
Returns the result of a bit logical shift operation performed on the integer value int_value. The shift_amount supports left and right shifts as well as logical and circular shifts. The third parameter rotate handles BitROTATE operations.
Arguments
int_value
An integer, or any valid expression which evaluates to an integer, that contains the target bit or bits to shift.
shift_amount
An integer, or any valid expression which evaluates to an integer, that contains the number of bits to shift.
A positive (+) number means this is a LEFT shift.
A negative (-) number means this is a RIGHT shift.
rotate
[optional; FALSE by default] A boolean, or any valid expression which evaluates to a boolean, where:
  • FALSE means this is a LOGICAL shift, where bits shifted off the end of a value are considered lost.
  • TRUE means this is a CIRCULAR shift (shift-and-rotate operation), where bits shifted off the end of a value are rotated back onto the value at the other end. In other words, the bits rotate in what might be thought of as a circular pattern; therefore, these bits are not lost.
For comparison, see the below table.
Input Shift Result (Rotate FALSE) Result (Rotate TRUE)
6 (0000 0110) 4 96 (0110 0000) 96 (0110 0000)
6 (0000 0110) 3 48 (0011 0000) 48 (0011 0000)
6 (0000 0110) 2 24 (0001 1000) 24 (0001 1000)
6 (0000 0110) 1 12 (0000 1100) 12 (0000 1100)
6 (0000 0110) 0 6 (0000 0110) 6 (0000 0110)
6 (0000 0110) -1 3 (0000 0011) 3 (0000 0011)
6 (0000 0110) -2 1 (0000 0001) -9223372036854776000 (1000 0000 ... 0000 0001)
6 (0000 0110) -3 0 (0000 0000) -4611686018427388000 (1100 0000 ... 0000 0000)
6 (0000 0110) -4 0 (0000 0000) 6917529027641081856 (0110 0000 ... 0000 0000)
Return Value
An integer, representing the result of either a logical or circular shift of the given integer.
Limitations
Input values must be integers (such as 1 or 1.0) and cannot contain decimals (such as 1.2).
Example 1: Logical left shift of the number 6 (0110 in binary) by one bit.
SELECT BITSHIFT(6,1,FALSE) AS BitSHIFT;

results: [
   "BitSHIFT": 12
]

This results in 12 (1100 in binary) because the 1-bits moved from positions 2 and 3 to positions 3 and 4.

Example 2: Logical right shift of the number 6 (0110 in binary) by two bits.
SELECT BITSHIFT(6,-2) AS BitSHIFT;

results: [
   "BitSHIFT": 1
]
This results in 1 (0001 in binary) because the 1-bit in position 3 moved to position 1 and the 1-bit in position 2 was dropped.

Example 2b: Circular right shift of the number 6 (0110 in binary) by two bits.
SELECT BITSHIFT(6,-2,TRUE) AS BitSHIFT;

results: [
   "BitSHIFT": -9223372036854776000
]
This results in -9223372036854776000 (1100 0000 0000 0000 0000 0000 0000 0000 in binary) because the two 1-bits wrapped right, around to the Most Significant Digit position and changed the integer's sign to negative.

Example 3: Circular left shift of the number 524288 (1000 0000 0000 0000 0000 in binary) by 45 bits.
SELECT BITSHIFT(524288,45,TRUE) AS BitSHIFT;

results: [
   "BitSHIFT": 1
]
This results in 1 because the 1-bit wrapped left, around to the Least Significant Digit position.

BITTEST (int_value, position [, all_set])

Description
BitTEST() and IsBitSet() are synonyms.
Returns TRUE if the specified bit, or bits, is a 1; otherwise, returns FALSE if the specified bit, or bits, is a 0 (zero).
Note: Specifying a negative or zero bit position will result in 0 (zero) being returned.
Arguments
int_value
An integer, or any valid expression which evaluates to an integer, that contains the target bit or bits to test.
position
An integer or an array of integers specifying the position or positions to be cleared.
all_set
[OPTIONAL; default is TRUE] A boolean, or any valid expression which evaluates to a boolean.
When all_set is FALSE, then it returns TRUE even if one bit in one of the positions is set.
When all_set is TRUE, then it returns TRUE only if all input positions are set.
Return Value
A boolean, that follows the below table:
int_value all_set Return Value
all specified bits are TRUE FALSE TRUE
all specified bits are TRUE TRUE TRUE
some specified bits are TRUE FALSE TRUE
some specified bits are TRUE TRUE FALSE
Limitations
Input values must be integers (such as 1 or 1.0) and cannot contain decimals (such as 1.2).
Example 1: In the number 6 (0110 in binary), is bit 1 set?
SELECT ISBITSET(6,1) AS IsBitSET;

results: [
   "IsBitSET": FALSE
]
This returns FALSE because bit 1 of 6 (011 0 in binary) is not set to 1.

Example 2: In the number 1, is either bit 1 or bit 2 set?
SELECT BITTEST(1,[1,2],FALSE) AS BitTEST;

results: [
   "BitTEST": TRUE
]
This returns TRUE because bit 1 of the number 1 (000 1 in binary) is set to 1.

Example 3: In the number 6 (0110 in binary), are both bits 2 and 3 set?
SELECT ISBITSET(6,[2,3],TRUE) AS IsBitSET;

results: [
   "IsBitSET": TRUE
]
This returns TRUE because both bits 2 and 3 in the number 6 (0 110 in binary) are set to 1.

Example 4: In the number 6 (0110 in binary), are all the bits in positions 1 through 3 set?
SELECT BITTEST(6,[1,3],TRUE) AS BitTEST;

results: [
   "BitTEST": FALSE
]
This returns FALSE because bit 1 in the number 6 (011 0 in binary) is set to 0 (zero).

Example 5: Find only flights that have 1 layover (to rest and walk around). That is, flight stops of 1 (0001 in binary) only.
SELECT airline, stops, schedule[0].day FROM `travel-sample` 
WHERE type = "route" AND stops = 1;
The above query returns the exact same results as the below query which uses a bit operation.
SELECT airline, stops, schedule[0].day FROM `travel-sample` 
WHERE type = "route" AND BITTEST(stops,1);

BITXOR (int_value1, int_value2)

Description
Returns the result of a bitwise Exclusive OR operation performed on two integer values.
The bitwise Exclusive OR operation compares each bit of int_value1 to the corresponding bit of int_value2. If one bit is 0 (zero) and the other bit is 1, the corresponding result bit is set to 1; otherwise, the corresponding result bit is set to 0 (zero).
Arguments
int_value1, int_value2
Integers, or any valid expressions which evaluate to integers, that are used to compare.
Return Value
An integer, representing the bitwise XOR between the two input integers.
Limitations
Input values must be integers (such as 1 or 1.0) and cannot contain decimals (such as 1.2).
Example: Perform the XOR operation on 3 (0011 in binary) and 6 (0110 in binary).
SELECT BITXOR(3,6) AS BitXOR;

results: [
   "BitXOR": 5
]
This returns 5 (0101 in binary) because both the 1st and 3rd bit are different, and hence give 1:

0011 (3)

0110 (6)

=============

0101 (5)

For the XOR to be 1, there can be only one 1 and one 0 for each bit pair, as is boldfaced in bit 1 and bit 3; bit 2 contains two 1's and bit 4 contains two 0's.

IsBitSET → see BITTEST

This function is a synonym of BitSET and has been detailed within the BITTEST function.