Bitmap output functions are used to select the output format for bitmap data. For example, they can output each element of the bitmap data in a string format, separated by commas. OceanBase AI Database currently supports the following bitmap output functions: rb_to_varbinary(), rb_to_string(), and rb_to_array().
rb_to_varbinary
The rb_to_varbinary() function is used to output bitmap data in varbinary format. The syntax is as follows:
rb_to_varbinary(rb, [format])
The format parameter is optional. If you need to configure it, only roaring is currently supported. After configuration, the output binary data types are limited to the two compatible formats: BITMAP_32 and BITMAP_64. If you do not configure format, the default output format is the OceanBase varbinary type (which includes seven subtypes, including BITMAP_32 and BITMAP_64).
An example is as follows:
SELECT rb_to_varbinary((1,2,3));
+------------------------------------------------------------------------------------+
| rb_to_varbinary(rb_from_string('1,2,3')) |
+------------------------------------------------------------------------------------+
| 0x010303010000000200000003000000 |
+------------------------------------------------------------------------------------+
1 row in set
SELECT rb_to_varbinary((1,2,3),'roaring');
+----------------------------------------------------------------------------------------------------------+
| rb_to_varbinary(rb_from_string('1,2,3'), 'roaring') |
+----------------------------------------------------------------------------------------------------------+
| 0x01053A300000010000000000020010000000010002000300 |
+----------------------------------------------------------------------------------------------------------+
1 row in set
rb_to_string
The rb_to_string() function sequentially outputs each element of the bitmap data as a string, separated by commas. The output format for each element is UINT64, and the maximum number of elements that can be output is 1,000,000. The syntax is as follows:
rb_to_string(rb)
An example is as follows:
SELECT rb_to_string(rb_from_string('1,2,3'));
+---------------------------------------+
| rb_to_string(rb_from_string('1,2,3')) |
+---------------------------------------+
| 1,2,3 |
+---------------------------------------+
1 row in set
SELECT rb_to_string(x'010101000000');
+-------------------------------+
| rb_to_string(x'010101000000') |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set
rb_iterate
The rb_iterate() function expands the values of bitmap data into a multi-row column. It can be used after SELECT or after FROM:
When used after
SELECT, if multiplerb_iterate()functions are used, multiple columns will be returned. The column names default torb_iterate. The number of returned rows is the maximum number of rows; any fewer columns will have extra rows filled withNULLvalues.When used after
FROM, if multiplerb_iterate()functions are used, it is equivalent to performing aJOINoperation on the returned tables in sequence, with the number of rows returned being the Cartesian product. The syntax is as follows:
table = rb_iterate(rb) [AS] [alias]
The parameters are described as follows:
Parameters |
Data types |
Default Value |
Description |
Required |
|---|---|---|---|---|
| rb | RoaringBitmap | Bitmap data to be output. | Yes | |
| alia | String | rb_iterate | Alias. You can use the AS keyword or omit it. | No |
The return value is a multi-row table with one column of type uint64.
Here is an example:
SELECT rb_iterate(rb_from_string('1,2,3'));
+------------+
| rb_iterate |
+------------+
| 1 |
| 2 |
| 3 |
+------------+
3 rows in set
You can use multiple rb_iterate() functions after FROM, as shown in the following example:
SELECT * FROM rb_iterate(rb_from_string('1,2,3')) AS t1, rb_iterate(rb_from_string('11,22')) AS t2;
+------+------+
| t1 | t2 |
+------+------+
| 1 | 11 |
| 1 | 22 |
| 2 | 11 |
| 2 | 22 |
| 3 | 11 |
| 3 | 22 |
+------+------+
6 rows in set
Using multiple rb_iterate() functions after SELECT returns multiple columns, as shown in the following example:
SELECT rb_iterate(rb_from_string('1,2,3')), rb_iterate(rb_from_string('11,22'));
+------------+------------+
| rb_iterate | rb_iterate |
+------------+------------+
| 1 | 11 |
| 2 | 22 |
| 3 | NULL |
+------------+------------+
3 rows in set
CREATE TABLE t1 (id int, rb roaringbitmap, rb2 roaringbitmap);
INSERT INTO t1 VALUES (1, rb_from_string('1,2,3'), rb_from_string('33,44')),(2, rb_from_string('3,4'), rb_from_string('44,55,66'));
SELECT rb_iterate(rb) FROM t1;
+------------+
| rb_iterate |
+------------+
| 1 |
| 2 |
| 3 |
| 3 |
| 4 |
+------------+
5 rows in set
rb_to_array
The rb_to_array() function is used to output bitmap data in an array format. The syntax is as follows:
rb_to_array(rb)
The input parameter of this function is a bitmap data.
The return value is an ordered array.
Here is an example:
SELECT rb_to_array(rb_from_string('0,1,2,3,4,5,6,7,8,9,123789,68719476736'));
+-----------------------------------------------------------------------+
| rb_to_array(rb_from_string('0,1,2,3,4,5,6,7,8,9,123789,68719476736')) |
+-----------------------------------------------------------------------+
| [0,1,2,3,4,5,6,7,8,9,123789,68719476736] |
+-----------------------------------------------------------------------+
1 row in set