The bitmap constructor is used to create a new bitmap data type. OceanBase AI Database currently supports the following bitmap construction functions: rb_build(), rb_build_empty(), rb_build_varbinary(), and rb_from_string().
Note
For more information about the visualized hexadecimal query results, see Create efficient compressed bitmap columns.
rb_build
The rb_build() function is used to convert an array of integers into an efficiently compressed bitmap data structure (RoaringBitmap). The syntax is as follows:
rb_build(arr)
The input parameters are described as follows:
- The type of
arrmust be an array.
The return value is a RoaringBitmap containing all elements of the array.
The constraints are as follows:
- An 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.
- RoaringBitmap supports inserting unsigned 64-bit integers (uint64) and signed 32-bit integers (int32) data. This is consistent with the behavior of
rb_from_stringdescribed below. When a negative signed 32-bit integer is input, it is first converted to an unsigned 32-bit integer before being inserted into the RoaringBitmap. Therefore, the permissible input integer range is from the minimum value of a signed 32-bit integer to the maximum value of an unsigned 64-bit integer, namely[INT32_MIN, UINT64_MAX].
An example is as follows:
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 structure. The syntax is as follows:
rb_build_empty()
This function takes no input parameters.
For example:
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 constructs bitmap data using varbinary. Varbinary is a proprietary format of OceanBase, consisting of version information, type information, data, and other components in a binary format. The syntax is as follows:
rb_build_varbinary(bin_str)
The input parameter is a varbinary string.
An example is as follows:
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 constructs bitmap data from a string in a specific format. The string consists of elements for the bitmap data to be constructed, separated by commas, for example, 1,2,3,4. The syntax is as follows:
rb_from_string(str)
Input string values support two formats: INT32 and INT64. The valid value range supported by OceanBase is [0, UINT64_MAX]. However, for compatibility with PostgreSQL, input of negative integers within the range [INT32_MIN, 0) is also supported. When outputting, negative integers are converted to UINT32 values. For example, input -1 outputs 4294967295.
Here is an example:
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 bitmap data based on specified conditions, save it as new bitmap data, and return its value. The syntax is as follows:
res_rb = rb_select(rb, limit [, offset] [, reverse] [, range_start] [, range_end])
The parameters are described as follows:
Parameters |
Data types |
Default Value |
Description |
Required |
|---|---|---|---|---|
| rb | RoaringBitmap | The bitmap data to be input. | Yes | |
| limit | uint64 | Limits the number of elements to output. | Yes | |
| offset | uint64 | 0 | Offset from the start position for output. | 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 end value of the output range. | No |
The calculation order is as follows:
If a range is specified, all elements within the range
[range_start,range_end)are filtered first.If
reverseisfalse, values are selected in ascending order; if it istrue, values are selected in descending order. Therb_select()function calculates the starting position of the output from the filtered elements based on the direction specified byreverse.Calculate the
offset.Calculate
limit. Output at mostlimitpieces of data within the specified range. If the number of data items in the range is less thanlimit, output all data items in the range.
The return value is of the bitmap data type.
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 values 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 ascending order from the interval [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
On the basis of the range [2, 6), offset by 1 value in descending order to calculate the result, and finally 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
On the basis of the range [2, 6), offset by 1 value in ascending order to calculate the result, and finally return the first 10 values (actually 3 values are 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