Bitmap operation functions perform basic operations on bitmap data. OceanBase Database supports the following bitmap operation functions: rb_and(), rb_or(), rb_xor(), rb_andnot(), rb_and_null2empty(), rb_or_null2empty(), and rb_andnot_null2empty().
rb_and and rb_and_null2empty
The rb_and() function calculates the intersection of two bitmap data sets. Syntax:
rb_and(rb1, rb2)
The order of the rb1 and rb2 parameters does not affect the result.
Here are some examples:
SELECT rb_to_string(rb_and(rb_from_string('1,2,3'), rb_from_string('2,3,4')));
+------------------------------------------------------------------------+
| rb_to_string(rb_and(rb_from_string('1,2,3'), rb_from_string('2,3,4'))) |
+------------------------------------------------------------------------+
| 2,3 |
+------------------------------------------------------------------------+
1 row in set
SELECT rb_to_string(rb_and(rb_from_string('1,2,3'), NULL));
+-----------------------------------------------------+
| rb_to_string(rb_and(rb_from_string('1,2,3'), NULL)) |
+-----------------------------------------------------+
| NULL |
+-----------------------------------------------------+
1 row in set
The rb_and_null2empty() function performs the same logical operation as rb_and(), but treats empty inputs as empty bitmap data.
SELECT rb_to_string(rb_and_null2empty(rb_from_string('1,2,3'), NULL));
+----------------------------------------------------------------+
| rb_to_string(rb_and_null2empty(rb_from_string('1,2,3'), NULL)) |
+----------------------------------------------------------------+
| |
+----------------------------------------------------------------+
1 row in set
rb_or and rb_or_null2empty
The rb_or() function calculates the union of two bitmap data sets. Syntax:
rb_or(rb1, rb2)
The order of the rb1 and rb2 parameters does not affect the result.
Here are some examples:
SELECT rb_to_string(rb_or(rb_from_string('1,2,3'), rb_from_string('2,3,4')));
+-----------------------------------------------------------------------+
| rb_to_string(rb_or(rb_from_string('1,2,3'), rb_from_string('2,3,4'))) |
+-----------------------------------------------------------------------+
| 1,2,3,4 |
+-----------------------------------------------------------------------+
1 row in set
The rb_or_null2empty() function performs the same logical operation as rb_or(), but treats empty inputs as empty bitmap data.
rb_xor
The rb_xor() function performs an XOR operation on two bitmap data sets. Syntax:
rb_xor(rb1, rb2)
The order of the rb1 and rb2 parameters does not affect the result.
Here are some examples:
SELECT rb_to_string(rb_xor(rb_from_string('1,2,3'), rb_from_string('2,3,4')));
+------------------------------------------------------------------------+
| rb_to_string(rb_xor(rb_from_string('1,2,3'), rb_from_string('2,3,4'))) |
+------------------------------------------------------------------------+
| 1,4 |
+------------------------------------------------------------------------+
1 row in set
rb_andnot and rb_andnot_null2empty
The rb_andnot() function performs an AND NOT operation on two bitmap data sets. Syntax:
rb_andnot(rb1, rb2)
This function calculates the difference between rb1 and rb2, returning a bitmap data set where the value is rb1 - rb2.
Here are some examples:
SELECT rb_to_string(rb_andnot(rb_from_string('1,2,3'), rb_from_string('2,3,4')));
+---------------------------------------------------------------------------+
| rb_to_string(rb_andnot(rb_from_string('1,2,3'), rb_from_string('2,3,4'))) |
+---------------------------------------------------------------------------+
| 1 |
+---------------------------------------------------------------------------+
1 row in set
The rb_andnot_null2empty() function performs the same logical operation as rb_andnot(), but treats empty inputs as empty bitmap data.