> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-trino-dialect.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for Bitmap Functions

# Bitmap Functions

Bitmaps can be constructed in two ways. The first way is constructed by aggregation function groupBitmap with `-State`, the other way is to constructed a bitmap from an Array object.

<h2 id="bitmapAnd">
  bitmapAnd
</h2>

Introduced in: v20.1.0

Computes the logical conjunction (AND) of two bitmaps.

**Syntax**

```sql theme={null}
bitmapAnd(bitmap1, bitmap2)
```

**Arguments**

* `bitmap1` — First bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns a bitmap containing bits present in both input bitmaps [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapToArray(bitmapAnd(bitmapBuild([1, 2, 3]), bitmapBuild([3, 4, 5]))) AS res;
```

```response title=Response theme={null}
┌─res─┐
│ [3] │
└─────┘
```

<h2 id="bitmapAndCardinality">
  bitmapAndCardinality
</h2>

Introduced in: v20.1.0

Returns the cardinality of the logical conjunction (AND) of two bitmaps.

**Syntax**

```sql theme={null}
bitmapAndCardinality(bitmap1, bitmap2)
```

**Arguments**

* `bitmap1` — First bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns the number of set bits in the intersection of the two bitmaps [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapAndCardinality(bitmapBuild([1,2,3]), bitmapBuild([3,4,5])) AS res;
```

```response title=Response theme={null}
┌─res─┐
│   1 │
└─────┘
```

<h2 id="bitmapAndnot">
  bitmapAndnot
</h2>

Introduced in: v20.1.0

Computes the set difference A AND-NOT B of two bitmaps.

**Syntax**

```sql theme={null}
bitmapAndnot(bitmap1, bitmap2)
```

**Arguments**

* `bitmap1` — First bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns a bitmap containing set bits present in the first bitmap but not in the second [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapToArray(bitmapAndnot(bitmapBuild([1, 2, 3]), bitmapBuild([3, 4, 5]))) AS res;
```

```response title=Response theme={null}
┌─res───┐
│ [1,2] │
└───────┘
```

<h2 id="bitmapAndnotCardinality">
  bitmapAndnotCardinality
</h2>

Introduced in: v20.1.0

Returns the cardinality of the AND-NOT operation of two bitmaps.

**Syntax**

```sql theme={null}
bitmapAndnotCardinality(bitmap1, bitmap2)
```

**Arguments**

* `bitmap1` — First bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns the number of set bits in the result of `bitmap1 AND-NOT bitmap2` [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapAndnotCardinality(bitmapBuild([1,2,3]), bitmapBuild([3,4,5])) AS res;
```

```response title=Response theme={null}
┌─res─┐
│   2 │
└─────┘
```

<h2 id="bitmapBuild">
  bitmapBuild
</h2>

Introduced in: v20.1.0

Builds a bitmap from an integer array. Supported element types are signed and unsigned integers of 8, 16, 32, or 64 bits. It is the opposite of function [`bitmapToArray`](/reference/functions/regular-functions/bitmap-functions#bitmapToArray).

**Syntax**

```sql theme={null}
bitmapBuild(array)
```

**Arguments**

* `array` — Integer array. [`Array((U)Int*)`](/reference/data-types/array)

**Returned value**

Returns a bitmap from the provided array [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction)

**Examples**

**Usage example**

```sql title=Query theme={null}
-- A bitmap is a binary value, so it is shown with `hex`.
SELECT hex(bitmapBuild([1, 2, 3, 4, 5])) AS res, toTypeName(bitmapBuild([1, 2, 3, 4, 5])) AS type;
```

```response title=Response theme={null}
┌─res────────────┬─type──────────────────────────────────┐
│ 00050102030405 │ AggregateFunction(groupBitmap, UInt8) │
└────────────────┴───────────────────────────────────────┘
```

**Signed bitmap**

```sql title=Query theme={null}
-- A bitmap is a binary value, so it is shown with `hex`.
SELECT hex(bitmapBuild([-128, -1]::Array(Int8))) AS res, toTypeName(bitmapBuild([-128, -1]::Array(Int8))) AS type;
```

```response title=Response theme={null}
┌─res──────┬─type─────────────────────────────────┐
│ 000280FF │ AggregateFunction(groupBitmap, Int8) │
└──────────┴──────────────────────────────────────┘
```

<h2 id="bitmapCardinality">
  bitmapCardinality
</h2>

Introduced in: v20.1.0

Returns the number of bits set (the cardinality) in the bitmap.

**Syntax**

```sql theme={null}
bitmapCardinality(bitmap)
```

**Arguments**

* `bitmap` — Bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns the number of bits set in the bitmap [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapCardinality(bitmapBuild([1, 3, 3, 5, 7, 7])) AS res
```

```response title=Response theme={null}
┌─res─┐
│   4 │
└─────┘
```

<h2 id="bitmapContains">
  bitmapContains
</h2>

Introduced in: v20.1.0

Checks if the bitmap contains a specific element. The value is compared as an unsigned integer of the bitmap element type. For signed bitmaps, a negative element matches its unsigned counterpart (for example, `Int8` value `-1` matches `255`).

**Syntax**

```sql theme={null}
bitmapContains(bitmap, value)
```

**Arguments**

* `bitmap` — Bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `value` — Element to check for. [(U)Int8/16/32/64](/reference/data-types/int-uint)

**Returned value**

Returns `1` if the bitmap contains the specified value, otherwise `0` [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapContains(bitmapBuild([1, 2, 3]), 2) AS res;
```

```response title=Response theme={null}
┌─res─┐
│   1 │
└─────┘
```

**Signed bitmap**

```sql title=Query theme={null}
SELECT bitmapContains(bitmapBuild([-1]::Array(Int8)), 255) AS res;
```

```response title=Response theme={null}
┌─res─┐
│   1 │
└─────┘
```

<h2 id="bitmapHasAll">
  bitmapHasAll
</h2>

Introduced in: v20.1.0

Checks if the first bitmap contains all set bits of the second bitmap.

**Syntax**

```sql theme={null}
bitmapHasAll(bitmap1, bitmap2)
```

**Arguments**

* `bitmap1` — First bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns `1` if all set bits of the second bitmap are present in the first bitmap, otherwise `0` [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapHasAll(bitmapBuild([1, 2, 3]), bitmapBuild([2, 3])) AS res;
```

```response title=Response theme={null}
┌─res─┐
│   1 │
└─────┘
```

<h2 id="bitmapHasAny">
  bitmapHasAny
</h2>

Introduced in: v20.1.0

Checks if the first bitmap contains any set bits of the second bitmap.

**Syntax**

```sql theme={null}
bitmapHasAny(bitmap1, bitmap2)
```

**Arguments**

* `bitmap1` — First bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns `1` if any bits of the second bitmap are present in the first bitmap, otherwise `0` [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapHasAny(bitmapBuild([1, 2, 3]), bitmapBuild([3, 4, 5])) AS res;
```

```response title=Response theme={null}
┌─res─┐
│   1 │
└─────┘
```

<h2 id="bitmapMax">
  bitmapMax
</h2>

Introduced in: v20.1.0

Returns the greatest element in a bitmap, interpreted as an unsigned integer of the bitmap element type. For signed bitmaps, negative values are treated as their unsigned counterparts (for example, `Int8` value `-1` is `255`). Returns `0` if the bitmap is empty.

**Syntax**

```sql theme={null}
bitmapMax(bitmap)
```

**Arguments**

* `bitmap` — Bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns the greatest element as an unsigned value of the bitmap element type, or `0` if the bitmap is empty [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapMax(bitmapBuild([1, 2, 3, 4, 5])) AS res;
```

```response title=Response theme={null}
┌─res─┐
│   5 │
└─────┘
```

**Signed bitmap**

```sql title=Query theme={null}
SELECT bitmapMax(bitmapBuild([-128, -1]::Array(Int8))) AS res;
```

```response title=Response theme={null}
┌─res─┐
│ 255 │
└─────┘
```

<h2 id="bitmapMin">
  bitmapMin
</h2>

Introduced in: v20.1.0

Returns the smallest element in a bitmap, interpreted as an unsigned integer of the bitmap element type. For signed bitmaps, negative values are treated as their unsigned counterparts (for example, `Int8` value `-128` is `128`). If the bitmap is empty, returns `UINT32_MAX` (`UINT64_MAX` if the bitmap element type is wider than 32 bits).

**Syntax**

```sql theme={null}
bitmapMin(bitmap)
```

**Arguments**

* `bitmap` — Bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns the smallest element as an unsigned value of the bitmap element type, or `UINT32_MAX`/`UINT64_MAX` if the bitmap is empty [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapMin(bitmapBuild([3, 5, 2, 6])) AS res;
```

```response title=Response theme={null}
┌─res─┐
│   2 │
└─────┘
```

**Signed bitmap**

```sql title=Query theme={null}
SELECT bitmapMin(bitmapBuild([-128, -1]::Array(Int8))) AS res;
```

```response title=Response theme={null}
┌─res─┐
│ 128 │
└─────┘
```

<h2 id="bitmapOr">
  bitmapOr
</h2>

Introduced in: v20.1.0

Computes the logical disjunction (OR) of two bitmaps.

**Syntax**

```sql theme={null}
bitmapOr(bitmap1, bitmap2)
```

**Arguments**

* `bitmap1` — First bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns a bitmap containing set bits present in either input bitmap [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapToArray(bitmapOr(bitmapBuild([1, 2, 3]), bitmapBuild([3, 4, 5]))) AS res;
```

```response title=Response theme={null}
┌─res─────────┐
│ [1,2,3,4,5] │
└─────────────┘
```

<h2 id="bitmapOrCardinality">
  bitmapOrCardinality
</h2>

Introduced in: v20.1.0

Returns the cardinality of the logical disjunction (OR) of two bitmaps.

**Syntax**

```sql theme={null}
bitmapOrCardinality(bitmap1, bitmap2)
```

**Arguments**

* `bitmap1` — First bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns the number of set bits in the union of the two bitmaps [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapOrCardinality(bitmapBuild([1,2,3]), bitmapBuild([3,4,5])) AS res;
```

```response title=Response theme={null}
┌─res─┐
│   5 │
└─────┘
```

<h2 id="bitmapSubsetInRange">
  bitmapSubsetInRange
</h2>

Introduced in: v20.1.0

Returns a subset of the bitmap containing elements in the value range `[start, end)`. Element values are compared as unsigned integers of the bitmap element type.

**Syntax**

```sql theme={null}
bitmapSubsetInRange(bitmap, start, end)
```

**Arguments**

* `bitmap` — Bitmap to extract the subset from. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `start` — Start of the range (inclusive). [`UInt*`](/reference/data-types/int-uint) - `end` — End of the range (exclusive). [`UInt*`](/reference/data-types/int-uint)

**Returned value**

Returns a bitmap containing only the elements in the specified value range [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapToArray(bitmapSubsetInRange(bitmapBuild([1, 2, 3, 4, 5]), 2, 5)) AS res;
```

```response title=Response theme={null}
┌─res─────┐
│ [2,3,4] │
└─────────┘
```

<h2 id="bitmapSubsetLimit">
  bitmapSubsetLimit
</h2>

Introduced in: v20.1.0

Returns a subset of at most `cardinality_limit` elements whose values are greater than or equal to `range_start`, selecting the smallest such values in unsigned order.

**Syntax**

```sql theme={null}
bitmapSubsetLimit(bitmap, range_start, cardinality_limit)
```

**Arguments**

* `bitmap` — Bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `range_start` — Start of the range (inclusive). [`UInt32`](/reference/data-types/int-uint) - `cardinality_limit` — Maximum cardinality of the subset. [`UInt32`](/reference/data-types/int-uint)

**Returned value**

Returns a bitmap containing at most `cardinality_limit` elements with unsigned value at least `range_start` [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT arraySort(bitmapToArray(bitmapSubsetLimit(bitmapBuild([1, 5, 3, 2, 8]), 3, 2))) AS res;
```

```response title=Response theme={null}
┌─res───┐
│ [3,5] │
└───────┘
```

<h2 id="bitmapToArray">
  bitmapToArray
</h2>

Introduced in: v20.1.0

Converts a bitmap to an array of its elements. The array element type matches the bitmap element type `T` (signed or unsigned integer). It is the opposite of function [`bitmapBuild`](/reference/functions/regular-functions/bitmap-functions#bitmapBuild).

**Syntax**

```sql theme={null}
bitmapToArray(bitmap)
```

**Arguments**

* `bitmap` — Bitmap to convert. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns an array of the elements contained in the bitmap [`Array(T)`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) AS res;
```

```response title=Response theme={null}
┌─res─────────┐
│ [1,2,3,4,5] │
└─────────────┘
```

**Signed bitmap**

```sql title=Query theme={null}
SELECT arraySort(bitmapToArray(bitmapBuild([-128, -1]::Array(Int8)))) AS res;
```

```response title=Response theme={null}
┌─res───────┐
│ [-128,-1] │
└───────────┘
```

<h2 id="bitmapTransform">
  bitmapTransform
</h2>

Introduced in: v20.1.0

Replaces elements in a bitmap according to a mapping from `from_array` to `to_array`.
Values are interpreted as unsigned integers of the bitmap element type (same domain as [`bitmapContains`](/reference/functions/regular-functions/bitmap-functions#bitmapContains)).
For signed bitmaps, a negative element matches its unsigned counterpart (for example, `Int8` value `-1` matches `255`).
A value in `to_array` that does not fit into the bitmap element type raises `BAD_ARGUMENTS`. A value in `from_array`
that does not fit is simply not found, so the corresponding replacement does not apply.

**Syntax**

```sql theme={null}
bitmapTransform(bitmap, from_array, to_array)
```

**Arguments**

* `bitmap` — Bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `from_array` — Array of original set bits to be replaced. [`Array(T)`](/reference/data-types/array). - `to_array` — Array of new set bits to replace with. [`Array(T)`](/reference/data-types/array).

**Returned value**

Returns a bitmap with elements transformed according to the given mapping [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapToArray(bitmapTransform(bitmapBuild([1, 2, 3, 4, 5]), [2, 4], [20, 40])) AS res;
```

```response title=Response theme={null}
┌─res───────────┐
│ [1,3,5,20,40] │
└───────────────┘
```

**Signed bitmap**

```sql title=Query theme={null}
SELECT arraySort(bitmapToArray(bitmapTransform(bitmapBuild([-1, 0]::Array(Int8)), [255], [10]))) AS res;
```

```response title=Response theme={null}
┌─res────┐
│ [0,10] │
└────────┘
```

<h2 id="bitmapXor">
  bitmapXor
</h2>

Introduced in: v20.1.0

Computes the symmetric difference (XOR) of two bitmaps.

**Syntax**

```sql theme={null}
bitmapXor(bitmap1, bitmap2)
```

**Arguments**

* `bitmap1` — First bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns a bitmap containing set bits present in either input bitmap, but not in both [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapToArray(bitmapXor(bitmapBuild([1, 2, 3]), bitmapBuild([3, 4, 5]))) AS res;
```

```response title=Response theme={null}
┌─res───────┐
│ [1,2,4,5] │
└───────────┘
```

<h2 id="bitmapXorCardinality">
  bitmapXorCardinality
</h2>

Introduced in: v20.1.0

Returns the cardinality of the XOR (symmetric difference) of two bitmaps.

**Syntax**

```sql theme={null}
bitmapXorCardinality(bitmap1, bitmap2)
```

**Arguments**

* `bitmap1` — First bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction).

**Returned value**

Returns the number of set bits in the symmetric difference of the two bitmaps [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapXorCardinality(bitmapBuild([1,2,3]), bitmapBuild([3,4,5])) AS res;
```

```response title=Response theme={null}
┌─res─┐
│   4 │
└─────┘
```

<h2 id="subBitmap">
  subBitmap
</h2>

Introduced in: v21.9.0

Returns a subset of the bitmap after skipping `offset` elements in ascending unsigned value order. The maximum cardinality of the returned bitmap is `cardinality_limit`.

**Syntax**

```sql theme={null}
subBitmap(bitmap, offset, cardinality_limit)
```

**Arguments**

* `bitmap` — Bitmap object. [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction). - `offset` — Number of set bits to skip from the beginning (zero-based). [`UInt32`](/reference/data-types/int-uint) - `cardinality_limit` — Maximum number of set bits to include in the subset. [`UInt32`](/reference/data-types/int-uint)

**Returned value**

Returns a bitmap containing at most `cardinality_limit` elements after skipping `offset` elements in ascending unsigned value order [`AggregateFunction(groupBitmap, T)`](/reference/data-types/aggregatefunction)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT bitmapToArray(subBitmap(bitmapBuild([1, 2, 3, 4, 5]), 2, 2)) AS res;
```

```response title=Response theme={null}
┌─res───┐
│ [3,4] │
└───────┘
```