bitmap_subset_in_range
Description
Intercepts elements from a Bitmap value within the range of start_range
and end_range
(exclusive). The output elements are a subset of the Bitmap value.
This function is mainly used for scenarios such as paginated queries. It is supported from v3.1.
Syntax
BITMAP bitmap_subset_in_range(BITMAP src, BIGINT start_range, BIGINT end_range)
Parameters
src
: the Bitmap value from which to obtain elements.start_range
: the start range to intercept elements. It must be a BIGINT value. If the specified start range exceeds the maximum length of the BITMAP value, NULL is returned. See Example 4.end_range
: the end range to intercept elements. It must be a BIGINT value. Ifend_range
equals or is less thanstart range
, NULL is returned. See Example 3.
Return value
Returns a value of the BITMAP type. NULL is returned if any of the input parameters is invalid.
Usage notes
The subset elements include start_range
but exclude end_range
. See Example 5.
Examples
In the following examples, the input of bitmap_subset_in_range() 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
. bitmap_subset_in_range() takes this BITMAP value as the input.
Example 1: Obtain subset elements from the BITMAP value with elements values in the range of 1 to 4. The values within this range are 1 and 3.
select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), 1, 4)) value;
+-------+
| value |
+-------+
| 1,3 |
+-------+
Example 2: Obtain subset elements from the BITMAP value with elements value in the range 1 to 100. The end value exceeds the maximum length of the BITMAP value and all the matching elements are returned.
select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), 0, 100)) value;
+-----------+
| value |
+-----------+
| 1,3,5,7,9 |
+-----------+
Example 3: NULL is returned because the end range 3
is less than the start range 4
.
select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), 4, 3)) value;
+-------+
| value |
+-------+
| NULL |
+-------+
Example 4: The start range 10 exceeds the maximum length (5) of the BITMAP value 1,3,5,7,9
. NULL is returned.
select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), 10, 15)) value;
+-------+
| value |
+-------+
| NULL |
+-------+
Example 5: The returned subset includes the start value 1
but excludes the end value 3
.
select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,1,3,1,5,4,5,6,7,9'), 1, 3)) value;
+-------+
| value |
+-------+
| 1 |
+-------+