sub_bitmap
Description
Intercepts len
elements from a BITMAP value src
starting from the position specified by offset
. The output elements are a subset of src
.
This function is mainly used for scenarios such as paginated queries. It is supported from v2.5.
This function is similar to bitmap_subset_limit. The difference is that this function intercepts elements starting from an offset whereas bitmap_subset_limit intercepts elements starting from an element value (start_range
).
Syntax
BITMAP sub_bitmap(BITMAP src, BIGINT offset, BIGINT len)
Parameters
src
: the BITMAP value from which you want to obtain elements.offset
: the starting position. It must be a BIGINT value. Note the following points when you useoffset
:- Offsets start from 0.
- Negative offsets are counted from right to left. See Examples 3 and 4.
- If the starting position specified by
offset
exceeds the actual length of the BITMAP value, NULL is returned. See Example 6.
len
: the number of elements to obtain. It must be a BIGINT value greater than or equal to 1. If the number of matching elements is less than the value oflen
, all the matching elements are returned. See Examples 2, 3, and 7.
Return value
Returns a value of the BITMAP type. NULL is returned if any of the input parameters is invalid.
Examples
In the following examples, the input of sub_bitmap() is the output of bitmap_from_string. For example, bitmap_from_string('1,1,3,1,5,3,5,7,7,9')
returns 1, 3, 5, 7, 9
. sub_bitmap() takes this BITMAP value as the input.
Example 1: Obtain two elements from the BITMAP value with the offset set to 0.
select bitmap_to_string(sub_bitmap(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), 0, 2)) value;
+-------+
| value |
+-------+
| 1,3 |
+-------+
Example 2: Obtain 100 elements from the BITMAP value with the offset set to 0. 100 exceeds the length of the BITMAP value and all the matching elements are returned.
select bitmap_to_string(sub_bitmap(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), 0, 100)) value;
+-----------+
| value |
+-----------+
| 1,3,5,7,9 |
+-----------+
Example 3: Obtain 100 elements from the BITMAP value with the offset set to -3. 100 exceeds the length of the BITMAP value and all the matching elements are returned.
select bitmap_to_string(sub_bitmap(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), -3, 100)) value;
+-------+
| value |
+-------+
| 5,7,9 |
+-------+
Example 4: Obtain two elements from the BITMAP value with the offset set to -3.
select bitmap_to_string(sub_bitmap(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), -3, 2)) value;
+-------+
| value |
+-------+
| 5,7 |
+-------+
Example 5: NULL is returned because -10
is an invalid input of len
.
select bitmap_to_string(sub_bitmap(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), 0, -10)) value;
+-------+
| value |
+-------+
| NULL |
+-------+
Example 6: The starting position specified by offset 5 exceeds the length of the BITMAP value 1,3,5,7,9
. NULL is returned.
select bitmap_to_string(sub_bitmap(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), 5, 1)) value;
+-------+
| value |
+-------+
| NULL |
+-------+
Example 7: len
is set to 5 but only two elements match the condition. All of these two elements are returned.
select bitmap_to_string(sub_bitmap(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), -2, 5)) value;
+-------+
| value |
+-------+
| 7,9 |
+-------+