Other functions

**Returned value**

Returns the result of aggregation for every row passed to the function. The return type is the same as the return type of the function that `initializeAggregation` takes as a first argument. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Basic usage with uniqState**

```
SELECT uniqMerge(state) FROM (SELECT initializeAggregation('uniqState', number % 3) AS state FROM numbers(10000));
```

```
┌─uniqMerge(state)─┐
│                3 │
└──────────────────┘
```

**Usage with sumState and finalizeAggregation**

```
SELECT finalizeAggregation(state), toTypeName(state) FROM (SELECT initializeAggregation('sumState', number % 3) AS state FROM numbers(5));
```

```
┌─finalizeAggregation(state)─┬─toTypeName(state)─────────────┐
│                          0 │ AggregateFunction(sum, UInt8) │
│                          1 │ AggregateFunction(sum, UInt8) │
│                          2 │ AggregateFunction(sum, UInt8) │
│                          0 │ AggregateFunction(sum, UInt8) │
│                          1 │ AggregateFunction(sum, UInt8) │
└────────────────────────────┴───────────────────────────────┘
```

## isConstant[​](#isConstant "Direct link to isConstant")

Introduced in: v20.3.0

Returns whether the argument is a constant expression.
A constant expression is an expression whose result is known during query analysis, i.e. before execution.
For example, expressions over [literals](/docs/sql-reference/syntax#literals) are constant expressions.
This function is mostly intended for development, debugging and demonstration.

**Syntax**

```
isConstant(x)
```

**Arguments**

- `x` — An expression to check. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns `1` if `x` is constant, `0` if `x` is non-constant. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Constant expression**

```
SELECT isConstant(x + 1)
FROM (SELECT 43 AS x)
```

```
┌─isConstant(plus(x, 1))─┐
│                      1 │
└────────────────────────┘
```

**Constant with function**

```
WITH 3.14 AS pi
SELECT isConstant(cos(pi))
```

```
┌─isConstant(cos(pi))─┐
│                   1 │
└─────────────────────┘
```

**Non-constant expression**

```
SELECT isConstant(number)
FROM numbers(1)
```

```
┌─isConstant(number)─┐
│                  0 │
└────────────────────┘
```

**Behavior of the now() function**

```
SELECT isConstant(now())
```

```
┌─isConstant(now())─┐
│                 1 │
└───────────────────┘
```

## isDecimalOverflow[​](#isDecimalOverflow "Direct link to isDecimalOverflow")

Introduced in: v20.8.0

Checks if a decimal number has too many digits to fit properly in a Decimal data type with given precision.

**Syntax**

```
isDecimalOverflow(value[, precision])
```

**Arguments**

- `value` — Decimal value to check. [`Decimal`](/docs/sql-reference/data-types/decimal)
- `precision` — Optional. The precision of the Decimal type. If omitted, the initial precision of the first argument is used. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns `1` if the decimal value has more digits than allowed by its precision, `0` if the decimal value satisfies the specified precision. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT isDecimalOverflow(toDecimal32(1000000000, 0), 9),
       isDecimalOverflow(toDecimal32(1000000000, 0)),
       isDecimalOverflow(toDecimal32(-1000000000, 0), 9),
       isDecimalOverflow(toDecimal32(-1000000000, 0));
```

```
┌─isDecimalOverflow(toDecimal32(1000000000, 0), 9)─┬─isDecimalOverflow(toDecimal32(1000000000, 0))─┬─isDecimalOverflow(toDecimal32(-1000000000, 0), 9)─┬─isDecimalOverflow(toDecimal32(-1000000000, 0))─┐
│                                                1 │                                             1 │                                                 1 │                                              1 │
└──────────────────────────────────────────────────┴───────────────────────────────────────────────┴───────────────────────────────────────────────────┴────────────────────────────────────────────────┘
```

## joinGet[​](#joinGet "Direct link to joinGet")

Introduced in: v18.16.0

Allows you to extract data from a table the same way as from a dictionary.
Gets data from Join tables using the specified join key.

Note

Only supports tables created with the `ENGINE = Join(ANY, LEFT, <join_keys>)` [statement](/docs/engines/table-engines/special/join).

**Syntax**

```
joinGet(join_storage_table_name, value_column, join_keys)
```

**Arguments**

- `join_storage_table_name` — An identifier which indicates where to perform the search. The identifier is searched in the default database (see parameter `default_database` in the config file). To override the default database, use the `USE database_name` query or specify the database and the table through a dot, like `database_name.table_name`. [`String`](/docs/sql-reference/data-types/string)
- `value_column` — The name of the column of the table that contains required data. [`const String`](/docs/sql-reference/data-types/string)
- `join_keys` — A list of join keys. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns list of values corresponded to list of keys. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Usage example**

```
CREATE TABLE db_test.id_val(`id` UInt32, `val` UInt32) ENGINE = Join(ANY, LEFT, id);
INSERT INTO db_test.id_val VALUES (1,11)(2,12)(4,13);

SELECT joinGet(db_test.id_val, 'val', toUInt32(1));
```

```
┌─joinGet(db_test.id_val, 'val', toUInt32(1))─┐
│                                          11 │
└─────────────────────────────────────────────┘
```

**Usage with table from current database**

```
USE db_test;
SELECT joinGet(id_val, 'val', toUInt32(2));
```

```
┌─joinGet(id_val, 'val', toUInt32(2))─┐
│                                  12 │
└─────────────────────────────────────┘
```

**Using arrays as join keys**

```
CREATE TABLE some_table (id1 UInt32, id2 UInt32, name String) ENGINE = Join(ANY, LEFT, id1, id2);
INSERT INTO some_table VALUES (1, 11, 'a') (2, 12, 'b') (3, 13, 'c');

SELECT joinGet(some_table, 'name', 1, 11);
```

```
┌─joinGet(some_table, 'name', 1, 11)─┐
│ a                                  │
└────────────────────────────────────┘
```

## joinGetOrNull[​](#joinGetOrNull "Direct link to joinGetOrNull")

Introduced in: v20.4.0

Allows you to extract data from a table the same way as from a dictionary.
Gets data from Join tables using the specified join key.
Unlike [`joinGet`](#joinGet) it returns `NULL` when the key is missing.

Note

Only supports tables created with the `ENGINE = Join(ANY, LEFT, <join_keys>)` [statement](/docs/engines/table-engines/special/join).

**Syntax**

```
joinGetOrNull(join_storage_table_name, value_column, join_keys)
```

**Arguments**

- `join_storage_table_name` — An identifier which indicates where to perform the search. The identifier is searched in the default database (see parameter default\_database in the config file). To override the default database, use the `USE database_name` query or specify the database and the table through a dot, like `database_name.table_name`. [`String`](/docs/sql-reference/data-types/string)
- `value_column` — The name of the column of the table that contains required data. [`const String`](/docs/sql-reference/data-types/string)
- `join_keys` — A list of join keys. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns a list of values corresponding to the list of keys, or `NULL` if a key is not found. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Usage example**

```
CREATE TABLE db_test.id_val(`id` UInt32, `val` UInt32) ENGINE = Join(ANY, LEFT, id);
INSERT INTO db_test.id_val VALUES (1,11)(2,12)(4,13);

SELECT joinGetOrNull(db_test.id_val, 'val', toUInt32(1)), joinGetOrNull(db_test.id_val, 'val', toUInt32(999));
```