The bitmap constructors are used to construct new bitmap data types. OceanBase Database currently supports the following bitmap constructors: rb_build(), rb_build_empty(), rb_build_varbinary(), and rb_from_string().
Note
For more information about the considerations for visualized hexadecimal query results, see Create a highly compressed bitmap column.
rb_build
The rb_build() function is used to convert array data with an integer base type into a highly compressed bitmap data (RoaringBitmap). Syntax:
rb_build(arr)
The input parameters are described as follows:
arrThe input type must be an array.
The return value is a RoaringBitmap that contains all elements of the array.
Constraints:
- The array cannot contain empty elements.
- If the array is a nested array, recursively extract the base elements of each subarray and insert them into the RoaringBitmap.
- The RoaringBitmap supports inserting unsigned 64-bit integers (uint64) and signed 32-bit integers (int32). The behavior of
rb_from_stringis consistent with the following: input signed 32-bit integers that are negative are first converted to unsigned 32-bit integers and then inserted into the RoaringBitmap. Therefore, the input integer range is from the minimum value of signed 32-bit integers to the maximum value of unsigned 64-bit integers, that is,[INT32_MIN, UINT64_MAX].
Here are some examples:
SELECT rb_to_string(rb_build([1.2]));
ERROR 5083 (22000): Invalid data type for the operation
SELECT rb_to_string(rb_build([0,1,2]));
+---------------------------------+
| rb_to_string(rb_build([0,1,2])) |
+---------------------------------+
| 0,1,2 |
+---------------------------------+
1 row in set
SELECT rb_to_string(rb_build([[0,1],[2],[2,3]]));
+-------------------------------------------+
| rb_to_string(rb_build([[0,1],[2],[2,3]])) |
+-------------------------------------------+
| 0,1,2,3 |
+-------------------------------------------+
1 row in set
rb_build_empty
The rb_build_empty() function is used to construct an empty bitmap data. Syntax:
rb_build_empty()
This function does not require any input parameters.
Here are some examples:
SELECT rb_build_empty();
+------------------------------------+
| rb_build_empty() |
+------------------------------------+
| 0x0100 |
+------------------------------------+
1 row in set
SELECT rb_is_empty(rb_build_empty());
+-------------------------------+
| rb_is_empty(rb_build_empty()) |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set
rb_build_varbinary
The rb_build_varbinary() function is used to construct bitmap data from a varbinary string. Varbinary is a private format of OceanBase Database, which is a binary format composed of version information, type information, and data. Syntax:
rb_build_varbinary(bin_str)
The input parameter is a varbinary string.
Here are some examples:
SELECT rb_to_string(rb_build_varbinary(x'0100'));
+-------------------------------------------+
| rb_to_string(rb_build_varbinary(x'0100')) |
+-------------------------------------------+
| |
+-------------------------------------------+
1 row in set
SELECT rb_to_string(rb_build_varbinary(x'0103020100000002000000'));
+-------------------------------------------------------------+
| rb_to_string(rb_build_varbinary(x'0103020100000002000000')) |
+-------------------------------------------------------------+
| 1,2 |
+-------------------------------------------------------------+
1 row in set
rb_from_string
The rb_from_string() function is used to construct bitmap data from a string in a specific format. The string format is a list of elements of the bitmap data, separated by commas, such as 1,2,3,4. Syntax:
rb_from_string(str)
The input string supports two formats: INT32 and INT64. The valid value range supported by OceanBase Database is [0, UINT64_MAX]. However, to ensure compatibility with PostgreSQL, it also supports the input of negative integers in the range [INT32_MIN, 0). When outputting, negative integers are converted to UINT32 values. For example, if the input is -1, the output is 4294967295.
Here are some examples:
SELECT rb_from_string('1,2,3');
+--------------------------------------------------+
| rb_from_string('1,2,3') |
+--------------------------------------------------+
| 0x010303010000000200000003000000 |
+--------------------------------------------------+
1 row in set
SELECT rb_to_string(rb_from_string('-1'));
+------------------------------------+
| rb_to_string(rb_from_string('-1')) |
+------------------------------------+
| 4294967295 |
+------------------------------------+
1 row in set
rb_select
Bitmap data stores a set of non-negative integers. The rb_select() function can select a local range of values from the bitmap data based on the specified conditions, save them as new bitmap data, and return the values. Syntax:
res_rb = rb_select(rb, limit [, offset] [, reverse] [, range_start] [, range_end])
The parameters are described as follows:
| Parameter | Data type | Default value | Description | Required |
|---|---|---|---|---|
| rb | RoaringBitmap | The input bitmap data. | Yes | |
| limit | uint64 | The maximum number of elements to output. | Yes | |
| offset | uint64 | 0 | The offset of the starting position. | No |
| reverse | bool | false | Whether to output in reverse order. | No |
| range_start | uint64 | 0 | The starting value of the output range. | No |
| range_end | uint64 | UINT64_MAX | The ending value of the output range. | No |
The calculation order is as follows:
If a range is specified, filter out all elements in the range
[range_start, range_end).If
reverseisfalse, select values in the normal order; iftrue, select values in the reverse order. Therb_select()function calculates the starting position of the output based on the reverse direction from the filtered elements.Calculate the
offset.Calculate the
limit. Output at mostlimitelements within the range. If the number of elements within the range is less thanlimit, output all elements within the range.
The return value is of the bitmap data type.
Here are some examples:
Return the first 3 values of '10, 20, 30, 40, 50, 60, 70':
SELECT rb_to_string(rb_select(rb_from_string('10, 20, 30, 40, 50, 60, 70'), 3));
+--------------------------------------------------------------------------+
| rb_to_string(rb_select(rb_from_string('10, 20, 30, 40, 50, 60, 70'), 3)) |
+--------------------------------------------------------------------------+
| 10,20,30 |
+--------------------------------------------------------------------------+
1 row in set
Offset by 2 and return the first 2 values of '1,2,3,4,5,6,7':
SELECT rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 2));
+----------------------------------------------------------------+
| rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 2)) |
+----------------------------------------------------------------+
| 3,4 |
+----------------------------------------------------------------+
1 row in set
Select the first 2 values in the normal order from [2, 6):
SELECT rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 0, false, 2, 6));
+-----------------------------------------------------------------------------+
| rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 0, false, 2, 6)) |
+-----------------------------------------------------------------------------+
| 2,3 |
+-----------------------------------------------------------------------------+
1 row in set
Calculate the result of offsetting by 1 in the reverse order based on the range [2, 6), and return the first 2 values:
SELECT rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 1, true, 2, 6));
+----------------------------------------------------------------------------+
| rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 1, true, 2, 6)) |
+----------------------------------------------------------------------------+
| 3,4 |
+----------------------------------------------------------------------------+
1 row in set
Calculate the result of offsetting by 1 in the normal order based on the range [2, 6), and return the first 10 values (only 3 values are actually returned):
SELECT rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 10, 1, false, 2, 6));
+------------------------------------------------------------------------------+
| rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 10, 1, false, 2, 6)) |
+------------------------------------------------------------------------------+
| 3,4,5 |
+------------------------------------------------------------------------------+
1 row in set