Skip to main content

Documentation Index

Fetch the complete documentation index at: https://private-7c7dfe99-page-updates.mintlify.app/llms.txt

Use this file to discover all available pages before exploring further.

The documentation below is generated from the system.functions system table.

​
dotProduct

Introduced in: v21.11.0 Calculates the dot product (scalar product) of two vectors (tuples or arrays of equal size). Returns the sum of the products of the corresponding elements. Syntax 
dotProduct(vector1, vector2)
Aliases: scalarProduct Arguments Returned value Returns the dot product of the two vectors. (U)Int* or Float* or Decimal Examples Basic usage
Query
SELECT dotProduct((1, 2), (3, 4))
Response
11

​
flattenTuple

Introduced in: v22.6.0 Flattens a named and nested tuple. The elements of the returned tuple are the paths of the input tuple. Syntax 
flattenTuple(input)
Arguments Returned value Returns an output tuple whose elements are paths from the original input. Tuple(T) Examples Usage example
Query
CREATE TABLE tab(t Tuple(a UInt32, b Tuple(c String, d UInt32))) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO tab VALUES ((3, ('c', 4)));

SELECT flattenTuple(t) FROM tab;
Response
┌─flattenTuple(t)┐
│ (3, 'c', 4)    │
└────────────────┘

​
tuple

Introduced in: v1.1.0 Returns a tuple by grouping input arguments. For columns C1, C2, … with the types T1, T2, …, it returns a named Tuple(C1 T1, C2 T2, …) type tuple containing these columns if their names are unique and can be treated as unquoted identifiers, otherwise a Tuple(T1, T2, …) is returned. There is no cost to execute the function. Tuples are normally used as intermediate values for an argument of IN operators, or for creating a list of formal parameters of lambda functions. Tuples can’t be written to a table. The function implements the operator (x, y, ...). Syntax 
tuple([t1[, t2[ ...]])
Arguments
  • None.
Returned value Examples typical
Query
SELECT tuple(1, 2)
Response
(1,2)

​
tupleConcat

Introduced in: v23.8.0 Combines tuples passed as arguments. Syntax 
tupleConcat(tuple1[, tuple2, [...]])
Arguments
  • tupleN — Arbitrary number of arguments of Tuple type. Tuple(T)
Returned value Returns a tuple containing all elements from the input tuples. Tuple(T) Examples Usage example
Query
SELECT tupleConcat((1, 2), ('a',), (true, false))
Response
(1, 2, 'a', true, false)

​
tupleDivide

Introduced in: v21.11.0 Calculates the division of corresponding elements of two tuples of the same size.
Division by zero will return inf.
Syntax 
tupleDivide(t1, t2)
Arguments Returned value Returns tuple with the result of division. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tupleDivide((1, 2), (2, 3))
Response
(0.5, 0.6666666666666666)

​
tupleDivideByNumber

Introduced in: v21.11.0 Returns a tuple with all elements divided by a number.
Division by zero will return inf.
Syntax 
tupleDivideByNumber(tuple, number)
Arguments Returned value Returns a tuple with divided elements. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tupleDivideByNumber((1, 2), 0.5)
Response
(2, 4)

​
tupleElement

Introduced in: v1.1.0 Extracts an element from a tuple by index or name. For access by index, an 1-based numeric index is expected. For access by name, the element name can be provided as a string (works only for named tuples). Negative indexes are supported. In this case, the corresponding element is selected, numbered from the end. For example, tuple.-1 is the last element in the tuple. An optional third argument specifies a default value which is returned instead of throwing an exception when the accessed element does not exist. All arguments must be constants. This function has zero runtime cost and implements the operators x.index and x.name. Syntax 
tupleElement(tuple, index|name[, default_value])
Arguments Returned value Returns the element at the specified index or name. Any Examples Index access
Query
SELECT tupleElement((1, 'hello'), 2)
Response
hello
Negative indexing
Query
SELECT tupleElement((1, 'hello'), -1)
Response
hello
Named tuple with table
Query
CREATE TABLE example (values Tuple(name String, age UInt32)) ENGINE = Memory;
INSERT INTO example VALUES (('Alice', 30));
SELECT tupleElement(values, 'name') FROM example;
Response
Alice
With default value
Query
SELECT tupleElement((1, 2), 5, 'not_found')
Response
not_found
Operator syntax
Query
SELECT (1, 'hello').2
Response
hello

​
tupleHammingDistance

Introduced in: v21.1.0 Returns the Hamming Distance between two tuples of the same size.
The result type is determined the same way it is for Arithmetic functions, based on the number of elements in the input tuples.
SELECT
    toTypeName(tupleHammingDistance(tuple(0), tuple(0))) AS t1,
    toTypeName(tupleHammingDistance((0, 0), (0, 0))) AS t2,
    toTypeName(tupleHammingDistance((0, 0, 0), (0, 0, 0))) AS t3,
    toTypeName(tupleHammingDistance((0, 0, 0, 0), (0, 0, 0, 0))) AS t4,
    toTypeName(tupleHammingDistance((0, 0, 0, 0, 0), (0, 0, 0, 0, 0))) AS t5

┌─t1────┬─t2─────┬─t3─────┬─t4─────┬─t5─────┐
│ UInt8 │ UInt16 │ UInt32 │ UInt64 │ UInt64 │
└───────┴────────┴────────┴────────┴────────┘
Syntax 
tupleHammingDistance(t1, t2)
Arguments Returned value Returns the Hamming distance. UInt8/16/32/64 Examples Usage example
Query
SELECT tupleHammingDistance((1, 2, 3), (3, 2, 1))
Response
2
With MinHash to detect semi-duplicate strings
Query
SELECT tupleHammingDistance(wordShingleMinHash(string), wordShingleMinHashCaseInsensitive(string)) FROM (SELECT 'ClickHouse is a column-oriented database management system for online analytical processing of queries.' AS string)
Response
2

​
tupleIntDiv

Introduced in: v23.8.0 Performs an integer division with a tuple of numerators and a tuple of denominators. Returns a tuple of quotients. If either tuple contains non-integer elements then the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor. Division by 0 causes an error to be thrown. Syntax 
tupleIntDiv(tuple_num, tuple_div)
Arguments Returned value Returns a tuple of the quotients. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tupleIntDiv((15, 10, 5), (5, 5, 5))
Response
(3, 2, 1)
With decimals
Query
SELECT tupleIntDiv((15, 10, 5), (5.5, 5.5, 5.5))
Response
(2, 1, 0)

​
tupleIntDivByNumber

Introduced in: v23.8.0 Performs integer division of a tuple of numerators by a given denominator, and returns a tuple of the quotients. If either of the input parameters contain non-integer elements then the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor. An error will be thrown for division by 0. Syntax 
tupleIntDivByNumber(tuple_num, div)
Arguments Returned value Returns a tuple of the quotients. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tupleIntDivByNumber((15, 10, 5), 5)
Response
(3, 2, 1)
With decimals
Query
SELECT tupleIntDivByNumber((15.2, 10.7, 5.5), 5.8)
Response
(2, 1, 0)

​
tupleIntDivOrZero

Introduced in: v23.8.0 Like tupleIntDiv performs integer division of a tuple of numerators and a tuple of denominators, and returns a tuple of the quotients. In case of division by 0, returns the quotient as 0 instead of throwing an exception. If either tuple contains non-integer elements then the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor. Syntax 
tupleIntDivOrZero(tuple_num, tuple_div)
Arguments Returned value Returns tuple of the quotients. Returns 0 for quotients where the divisor is 0. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples With zero divisors
Query
SELECT tupleIntDivOrZero((5, 10, 15), (0, 0, 0))
Response
(0, 0, 0)

​
tupleIntDivOrZeroByNumber

Introduced in: v23.8.0 Like tupleIntDivByNumber it does integer division of a tuple of numerators by a given denominator, and returns a tuple of the quotients. It does not throw an error for zero divisors, but rather returns the quotient as zero. If either the tuple or div contain non-integer elements then the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor. Syntax 
tupleIntDivOrZeroByNumber(tuple_num, div)
Arguments Returned value Returns a tuple of the quotients with 0 for quotients where the divisor is 0. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tupleIntDivOrZeroByNumber((15, 10, 5), 5)
Response
(3, 2, 1)
With zero divisor
Query
SELECT tupleIntDivOrZeroByNumber((15, 10, 5), 0)
Response
(0, 0, 0)

​
tupleMinus

Introduced in: v21.11.0 Calculates the difference between corresponding elements of two tuples of the same size. Syntax 
tupleMinus(t1, t2)
Aliases: vectorDifference Arguments Returned value Returns a tuple containing the results of the subtractions. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tupleMinus((1, 2), (2, 3))
Response
(-1, -1)

​
tupleModulo

Introduced in: v23.8.0 Returns a tuple of the remainders (moduli) of division operations of two tuples. Syntax 
tupleModulo(tuple_num, tuple_mod)
Arguments Returned value Returns tuple of the remainders of division. An error is thrown for division by zero. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tupleModulo((15, 10, 5), (5, 3, 2))
Response
(0, 1, 1)

​
tupleModuloByNumber

Introduced in: v23.8.0 Returns a tuple of the moduli (remainders) of division operations of a tuple and a given divisor. Syntax 
tupleModuloByNumber(tuple_num, div)
Arguments Returned value Returns tuple of the remainders of division. An error is thrown for division by zero. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tupleModuloByNumber((15, 10, 5), 2)
Response
(1, 0, 1)

​
tupleMultiply

Introduced in: v21.11.0 Calculates the multiplication of corresponding elements of two tuples of the same size. Syntax 
tupleMultiply(t1, t2)
Arguments Returned value Returns a tuple with the results of the multiplications. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tupleMultiply((1, 2), (2, 3))
Response
(2, 6)

​
tupleMultiplyByNumber

Introduced in: v21.11.0 Returns a tuple with all elements multiplied by a number. Syntax 
tupleMultiplyByNumber(tuple, number)
Arguments Returned value Returns a tuple with multiplied elements. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tupleMultiplyByNumber((1, 2), -2.1)
Response
(-2.1, -4.2)

​
tupleNames

Introduced in: v24.8.0 Converts a tuple into an array of column names. For a tuple in the form Tuple(a T, b T, ...), it returns an array of strings representing the named columns of the tuple. If the tuple elements do not have explicit names, their indices will be used as the column names instead. Syntax 
tupleNames(tuple)
Arguments
  • None.
Returned value Examples typical
Query
SELECT tupleNames(tuple(1 as a, 2 as b))
Response
['a','b']

​
tupleNegate

Introduced in: v21.11.0 Calculates the negation of the tuple elements. Syntax 
tupleNegate(t)
Arguments Returned value Returns a tuple with the result of negation. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tupleNegate((1, 2))
Response
(-1, -2)

​
tuplePlus

Introduced in: v21.11.0 Calculates the sum of corresponding elements of two tuples of the same size. Syntax 
tuplePlus(t1, t2)
Aliases: vectorSum Arguments Returned value Returns a tuple containing the sums of corresponding input tuple arguments. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tuplePlus((1, 2), (2, 3))
Response
(3, 5)

​
tuplePositiveModuloByNumber

Introduced in: v26.4.0 Returns a tuple of the positive moduli (remainders) of division operations of a tuple and a given divisor. Unlike tupleModuloByNumber, the result is always non-negative. Syntax 
tuplePositiveModuloByNumber(tuple_num, div)
Arguments Returned value Returns a tuple of the non-negative remainders. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage
Query
SELECT tuplePositiveModuloByNumber((15, 10, 5), 2)
Response
(1, 0, 1)

​
tupleToNameValuePairs

Introduced in: v21.9.0 Converts a tuple to an array of (name, value) pairs. For example, tuple Tuple(n1 T1, n2 T2, ...) is converted to Array(Tuple('n1', T1), Tuple('n2', T2), ...). All values in the tuple must be of the same type. Syntax 
tupleToNameValuePairs(tuple)
Arguments Returned value Returns an array with (name, value) pairs. Array(Tuple(String, T)) Examples Named tuple
Query
SELECT tupleToNameValuePairs(tuple(1593 AS user_ID, 2502 AS session_ID))
Response
[('1', 1593), ('2', 2502)]
Unnamed tuple
Query
SELECT tupleToNameValuePairs(tuple(3, 2, 1))
Response
[('1', 3), ('2', 2), ('3', 1)]

​
untuple

Performs syntactic substitution of tuple elements in the call location. The names of the result columns are implementation-specific and subject to change. Do not assume specific column names after untuple. Syntax 
untuple(x)
You can use the EXCEPT expression to skip columns as a result of the query. Arguments
  • x — A tuple function, column, or tuple of elements. Tuple.
Returned value
  • None.
Examples Input table: 
┌─key─┬─v1─┬─v2─┬─v3─┬─v4─┬─v5─┬─v6────────┐
│   1 │ 10 │ 20 │ 40 │ 30 │ 15 │ (33,'ab') │
│   2 │ 25 │ 65 │ 70 │ 40 │  6 │ (44,'cd') │
│   3 │ 57 │ 30 │ 20 │ 10 │  5 │ (55,'ef') │
│   4 │ 55 │ 12 │  7 │ 80 │ 90 │ (66,'gh') │
│   5 │ 30 │ 50 │ 70 │ 25 │ 55 │ (77,'kl') │
└─────┴────┴────┴────┴────┴────┴───────────┘
Example of using a Tuple-type column as the untuple function parameter: Query: 
SELECT untuple(v6) FROM kv;
Result: 
┌─_ut_1─┬─_ut_2─┐
│    33 │ ab    │
│    44 │ cd    │
│    55 │ ef    │
│    66 │ gh    │
│    77 │ kl    │
└───────┴───────┘
Example of using an EXCEPT expression: Query: 
SELECT untuple((* EXCEPT (v2, v3),)) FROM kv;
Result: 
┌─key─┬─v1─┬─v4─┬─v5─┬─v6────────┐
│   1 │ 10 │ 30 │ 15 │ (33,'ab') │
│   2 │ 25 │ 40 │  6 │ (44,'cd') │
│   3 │ 57 │ 10 │  5 │ (55,'ef') │
│   4 │ 55 │ 80 │ 90 │ (66,'gh') │
│   5 │ 30 │ 25 │ 55 │ (77,'kl') │
└─────┴────┴────┴────┴───────────┘

​
Distance functions

All supported functions are described in distance functions documentation.