Bitmap output functions

Bitmap output functions are used to select the output format of bitmap data, such as sequentially outputting each element of the bitmap data in string format and separating them with commas. The current supported bitmap output functions in seekdb include 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. Currently, only roaring is supported. After configuration, the output binary data types are only compatible with BITMAP_32 and BITMAP_64. If the format parameter is not configured, the default output format is the varbinary format of seekdb, which includes seven subtypes: BITMAP_32, BITMAP_64, BITMAP_16, BITMAP_8, BITMAP_4, BITMAP_2, and BITMAP_1.

Here are some examples:

SELECT rb_to_varbinary((1,2,3));
+------------------------------------------------------------------------------------+
| rb_to_varbinary(rb_from_string('1,2,3'))                                           |
+------------------------------------------------------------------------------------+
| 0x010303010000000200000003000000                                                   |
+------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

SELECT rb_to_varbinary((1,2,3),'roaring');
+----------------------------------------------------------------------------------------------------------+
| rb_to_varbinary(rb_from_string('1,2,3'), 'roaring')                                                      |
+----------------------------------------------------------------------------------------------------------+
| 0x01053A300000010000000000020010000000010002000300                                                       |
+----------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

rb_to_string

The rb_to_string() function is used to sequentially output each element of the bitmap data in string format and separate them with commas. The output format of each element is UINT64, and the maximum number of elements is 1,000,000. The syntax is as follows:

rb_to_string(rb)

Here are some examples:

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 (0.001 sec)

SELECT rb_to_string(x'010101000000');
+-------------------------------+
| rb_to_string(x'010101000000') |
+-------------------------------+
| 1                             |
+-------------------------------+
1 row in set (0.001 sec)

rb_iterate

The rb_iterate() function is used to expand the values of the bitmap data into multiple rows of a single column. This function can be used after SELECT or FROM:

• When used after SELECT, if multiple rb_iterate() functions are used, multiple columns will be returned, with the default column name being rb_iterate. The number of rows returned is the maximum, and the remaining rows in fewer columns are filled with NULL values.

• When used after FROM, if multiple rb_iterate() functions are used, it is equivalent to performing a JOIN operation on the returned tables in sequence, and the number of rows returned is the Cartesian product. The syntax is as follows:

table = rb_iterate(rb) [AS] [alias]

The parameters are described as follows:

Parameter	Data type	Default value	Description	Required
rb	RoaringBitmap		The bitmap data to be output.	Yes
alia	String	rb_iterate	The alias. You can use the AS keyword or not.	No

The return value is a table with one column containing multiple rows of uint64 data.

Here are some examples:

SELECT rb_iterate(rb_from_string('1,2,3'));
+------------+
| rb_iterate |
+------------+
|          1 |
|          2 |
|          3 |
+------------+
3 rows in set (0.002 sec)

Here is an example of using multiple rb_iterate() functions after FROM:

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 (0.003 sec)

Here is an example of using multiple rb_iterate() functions after SELECT:

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 (0.002 sec)

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 (0.002 sec)

rb_to_array

The rb_to_array() function is used to output the bitmap data in 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 (0.001 sec)