Arithmetic functions

Overview

Arithmetic functions work for any two operands of type UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, or Float64.

Before performing the operation, both operands are cast to the result type. The result type is determined as follows (unless specified differently in the function documentation below):

• If both operands are up to 32 bits wide, the size of the result type will be the size of the next bigger type following the bigger of the two operands (integer size promotion). For example, UInt8 + UInt16 = UInt32 or Float32 * Float32 = Float64.
• If one of the operands has 64 or more bits, the size of the result type will be the same size as the bigger of the two operands. For example, UInt32 + UInt128 = UInt128 or Float32 * Float64 = Float64.
• If one of the operands is signed, the result type will also be signed, otherwise it will be signed. For example, UInt32 * Int32 = Int64.

These rules make sure that the result type will be the smallest type which can represent all possible results. While this introduces a risk of overflows around the value range boundary, it ensures that calculations are performed quickly using the maximum native integer width of 64 bit. This behavior also guarantees compatibility with many other databases which provide 64 bit integers (BIGINT) as the biggest integer type.

Example:

Overflows are produced the same way as in C++.

abs

Introduced in: v1.1

Calculates the absolute value of x. Has no effect if x is of an unsigned type. If x is of a signed type, it returns an unsigned number.

Syntax

Arguments

• x — Value to get the absolute value of

Returned value

The absolute value of x

Examples

Usage example

byteSwap

Introduced in: v23.10

Reverses the bytes of an integer, i.e. changes its endianness.

The below example can be worked out in the following manner:

1. Convert the base-10 integer to its equivalent hexadecimal format in big-endian format, i.e. 3351772109 -> C7 C7 FB CD (4 bytes)
2. Reverse the bytes, i.e. C7 C7 FB CD -> CD FB C7 C7
3. Convert the result back to an integer assuming big-endian, i.e. CD FB C7 C7 -> 3455829959 One use case of this function is reversing IPv4s:

Syntax

Arguments

• x — An integer value. (U)Int*

Returned value

Returns x with bytes reversed. (U)Int*

Examples

Usage example

8-bit

16-bit

32-bit

64-bit

divide

Introduced in: v1.1

Calculates the quotient of two values a and b. The result type is always Float64. Integer division is provided by the intDiv function.

Note

Division by 0 returns inf, -inf, or nan.

Syntax

Arguments

• x — Dividend - y — Divisor

Returned value

The quotient of x and y

Examples

Dividing two numbers

Dividing by zero

divideDecimal

Introduced in: v22.12

Performs division on two decimals. Result value will be of type Decimal256. Result scale can be explicitly specified by result_scale argument (const Integer in range [0, 76]). If not specified, the result scale is the max scale of given arguments.

Note

These function work significantly slower than usual divide. In case you don't really need controlled precision and/or need fast computation, consider using divide.

Syntax

Arguments

• x — First value: Decimal. - y — Second value: Decimal. - result_scale — Scale of result. Type Int/UInt.

Returned value

The result of division with given scale. Decimal256

Examples

Example 1

Example 2

divideOrNull

Introduced in: v25.5

Same as divide but returns NULL when dividing by zero.

Syntax

Arguments

• x — Dividend - y — Divisor

Returned value

The quotient of x and y, or NULL.

Examples

Dividing by zero

gcd

Introduced in: v1.1

Returns the greatest common divisor of two values a and b.

An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.

Syntax

Arguments

• x — First integer - y — Second integer

Returned value

The greatest common divisor of x and y.

Examples

Usage example

ifNotFinite

Introduced in: v20.3

Checks whether a floating point value is finite.

You can get a similar result by using the ternary operator: isFinite(x) ? x : y.

Syntax

Arguments

• x — Value to check if infinite. Float*
• y — Fallback value. Float*

Returned value

• x if x is finite.
• y if x is not finite.

Examples

Usage example

intDiv

Introduced in: v1.1

Performs an integer division of two values x by y. In other words it computes the quotient rounded down to the next smallest integer.

The result has the same width as the dividend (the first parameter).

An exception is thrown when dividing by zero, when the quotient does not fit in the range of the dividend, or when dividing a minimal negative number by minus one.

Syntax

Arguments

• x — Left hand operand. - y — Right hand operand.

Returned value

Result of integer division of x and y

Examples

Integer division of two floats

Quotient does not fit in the range of the dividend

intDivOrNull

Introduced in: v25.5

Same as intDiv but returns NULL when dividing by zero or when dividing a minimal negative number by minus one.

Syntax

Arguments

• x — Left hand operand. (U)Int*
• y — Right hand operand. (U)Int*

Returned value

Result of integer division of x and y, or NULL.

Examples

Integer division by zero

Dividing a minimal negative number by minus 1

intDivOrZero

Introduced in: v1.1

Same as intDiv but returns zero when dividing by zero or when dividing a minimal negative number by minus one.

Syntax

Arguments

• a — Left hand operand. (U)Int*
• b — Right hand operand. (U)Int*

Returned value

Result of integer division of a and b, or zero.

Examples

Integer division by zero

Dividing a minimal negative number by minus 1

isFinite

Introduced in: v1.1

Returns 1 if the Float32 or Float64 argument not infinite and not a NaN, otherwise this function returns 0.

Syntax

Arguments

• x — Number to check for finiteness. Float*

Returned value

1 if x is not infinite and not NaN, otherwise 0.

Examples

Test if a number is finite

isInfinite

Introduced in: v1.1

Returns 1 if the Float32 or Float64 argument is infinite, otherwise this function returns 0. Note that 0 is returned for a NaN.

Syntax

Arguments

• x — Number to check for infiniteness. Float*

Returned value

1 if x is infinite, otherwise 0 (including for NaN).

Examples

Test if a number is infinite

isNaN

Introduced in: v1.1

Returns 1 if the Float32 and Float64 argument is NaN, otherwise returns 0.

Syntax

Arguments

• x — Argument to evaluate for if it is NaN. Float*

Returned value

1 if NaN, otherwise 0

Examples

Usage example

lcm

Introduced in: v1.1

Returns the least common multiple of two values x and y.

An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.

Syntax

Arguments

• x — First integer. (U)Int*
• y — Second integer. (U)Int*

Returned value

Returns the least common multiple of x and y. (U)Int*

Examples

Usage example

max2

Introduced in: v21.11

Returns the bigger of two numeric values x and y.

Syntax

Arguments

• x — First value (U)Int8/16/32/64 or Float* or Decimal
• y — Second value (U)Int8/16/32/64 or Float* or Decimal

Returned value

Returns the bigger value of x and y. Float64

Examples

Usage example

min2

Introduced in: v21.11

Returns the smaller of two numeric values x and y.

Syntax

Arguments

• x — First value (U)Int8/16/32/64 or Float* or Decimal
• y — Second value (U)Int8/16/32/64 or Float* or Decimal

Returned value

Returns the smaller value of x and y. Float64

Examples

Usage example

minus

Introduced in: v1.1

Calculates the difference of two values a and b. The result is always signed. Similar to plus, it is possible to subtract an integer from a date or date with time. Additionally, subtraction between date with time is supported, resulting in the time difference between them.

Syntax

Arguments

• x — Minuend. - y — Subtrahend.

Returned value

x minus y

Examples

Subtracting two numbers

Subtracting an integer and a date

modulo

Introduced in: v1.1

Calculates the remainder of the division of two values a by b.

The result type is an integer if both inputs are integers. If one of the inputs is a floating-point number, the result type is Float64.

The remainder is computed like in C++. Truncated division is used for negative numbers.

An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.

Syntax

Arguments

• a — The dividend - b — The divisor (modulus)

Returned value

The remainder of a % b

Examples

Usage example

moduloOrNull

Introduced in: v25.5

Calculates the remainder when dividing a by b. Similar to function modulo except that moduloOrNull will return NULL if the right argument is 0.

Syntax

Arguments

• x — The dividend. (U)Int* or Float*
• y — The divisor (modulus). (U)Int* or Float*

Returned value

Returns the remainder of the division of x by y, or null when the divisor is zero.

Examples

moduloOrNull by zero

moduloOrZero

Introduced in: v20.3

Like modulo but returns zero when the divisor is zero, as opposed to an exception with the modulo function.

Syntax

Arguments

• a — The dividend. (U)Int* or Float*
• b — The divisor (modulus). (U)Int* or Float*

Returned value

Returns the remainder of a % b, or 0 when the divisor is 0.

Examples

Usage example

multiply

Introduced in: v1.1

Calculates the product of two values x and y.

Syntax

Arguments

• x — factor. (U)Int* or Float* or Decimal
• y — factor. (U)Int* or Float* or Decimal

Returned value

Returns the product of x and y

Examples

Multiplying two numbers

multiplyDecimal

Introduced in: v22.12

Performs multiplication on two decimals. Result value will be of type Decimal256. Result scale can be explicitly specified by result_scale argument (const Integer in range [0, 76]). If not specified, the result scale is the max scale of given arguments.

Note

These functions work significantly slower than usual multiply. In case you don't really need controlled precision and/or need fast computation, consider using multiply

Syntax

Arguments

• a — First value. Decimal
• b — Second value. Decimal
• result_scale — Scale of result. (U)Int*

Returned value

The result of multiplication with the given scale. Type: Decimal256

Examples

Usage example

Difference with regular multiplication

Decimal overflow

negate

Introduced in: v1.1

Negates the argument x. The result is always signed.

Syntax

Arguments

• x — The value to negate.

Returned value

Returns -x from x

Examples

Usage example

numericIndexedVectorAllValueSum

Introduced in: v25.7

Returns sum of all the value in NumericIndexedVector.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object.

Returned value

Numeric value which type is Float64

Examples

---

numericIndexedVectorBuild

Introduced in: v25.7

Creates a NumericIndexedVector from a map. The map’s keys represent the vector's index and map's value represents the vector's value.

Syntax

Arguments

• map — A mapping from index to value.

Returned value

NumericIndexedVector object.

Examples

---

numericIndexedVectorCardinality

Introduced in: v25.7

Returns the cardinality (number of unique indexes) of the NumericIndexedVector.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object.

Returned value

Numeric value which type is UInt

Examples

---

numericIndexedVectorGetValue

Introduced in: v25.7

Retrieves the value corresponding to a specified index.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object. - index — The index for which the value is to be retrieved.

Returned value

A Numeric value with the same type as the value type of NumericIndexedVector.

Examples

---

numericIndexedVectorPointwiseAdd

Introduced in: v25.7

Performs pointwise addition between a NumericIndexedVector and either another NumericIndexedVector or a numeric constant. The function returns a new NumericIndexedVector.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object. - numeric — A numeric constant

Returned value

NumericIndexedVector object.

Examples

---

numericIndexedVectorPointwiseDivide

Introduced in: v25.7

Performs pointwise division between a NumericIndexedVector and either another NumericIndexedVector or a numeric constant. The function returns a new NumericIndexedVector.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object. - numeric — A numeric constant

Returned value

NumericIndexedVector object.

Examples

---

numericIndexedVectorPointwiseEqual

Introduced in: v25.7

Performs pointwise comparison between a NumericIndexedVector and either another NumericIndexedVector or a numeric constant. The result is a NumericIndexedVector containing the indices where the values are equal, with all corresponding value set to 1.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object. - numeric — A numeric constant

Returned value

NumericIndexedVector object.

Examples

---

numericIndexedVectorPointwiseGreater

Introduced in: v25.7

Performs pointwise comparison between a NumericIndexedVector and either another NumericIndexedVector or a numeric constant. The result is a NumericIndexedVector containing the indices where the first vector’s value is greater than the second vector’s value, with all corresponding value set to 1.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object. - numeric — A numeric constant

Returned value

NumericIndexedVector object.

Examples

---

numericIndexedVectorPointwiseGreaterEqual

Introduced in: v25.7

Performs pointwise comparison between a NumericIndexedVector and either another NumericIndexedVector or a numeric constant. The result is a NumericIndexedVector containing the indices where the first vector’s value is greater than or equal to the second vector’s value, with all corresponding value set to 1.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object. - numeric — A numeric constant

Returned value

NumericIndexedVector object.

Examples

---

numericIndexedVectorPointwiseLess

Introduced in: v25.7

Performs pointwise comparison between a NumericIndexedVector and either another NumericIndexedVector or a numeric constant. The result is a NumericIndexedVector containing the indices where the first vector’s value is less than the second vector’s value, with all corresponding value set to 1.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object. - numeric — A numeric constant

Returned value

NumericIndexedVector object.

Examples

---

numericIndexedVectorPointwiseLessEqual

Introduced in: v25.7

Performs pointwise comparison between a NumericIndexedVector and either another NumericIndexedVector or a numeric constant. The result is a NumericIndexedVector containing the indices where the first vector’s value is less than or equal to the second vector’s value, with all corresponding value set to 1.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object. - numeric — A numeric constant

Returned value

NumericIndexedVector object.

Examples

---

numericIndexedVectorPointwiseMultiply

Introduced in: v25.7

Performs pointwise multiplication between a NumericIndexedVector and either another NumericIndexedVector or a numeric constant. The function returns a new NumericIndexedVector.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object. - numeric — A numeric constant

Returned value

NumericIndexedVector object.

Examples

---

numericIndexedVectorPointwiseNotEqual

Introduced in: v25.7

Performs pointwise comparison between a NumericIndexedVector and either another NumericIndexedVector or a numeric constant. The result is a NumericIndexedVector containing the indices where the values are not equal, with all corresponding value set to 1.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object. - numeric — A numeric constant

Returned value

NumericIndexedVector object.

Examples

---

numericIndexedVectorPointwiseSubtract

Introduced in: v25.7

Performs pointwise subtraction between a NumericIndexedVector and either another NumericIndexedVector or a numeric constant. The function returns a new NumericIndexedVector.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object. - numeric — A numeric constant

Returned value

NumericIndexedVector object.

Examples

---

numericIndexedVectorShortDebugString

Introduced in: v25.7

Returns internal information of the NumericIndexedVector in a json format. This function is primarily used for debugging purposes.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object.

Returned value

String

Examples

---

numericIndexedVectorToMap

Introduced in: v25.7

Converts a NumericIndexedVector to a map.

Syntax

Arguments

• numericIndexedVector — A NumericIndexedVector object.

Returned value

Map(IndexType, ValueType)

Examples

---

plus

Introduced in: v1.1

Calculates the sum of two values x and y. Alias: x + y (operator). It is possible to add an integer and a date or date with time. The former operation increments the number of days in the date, the latter operation increments the number of seconds in the date with time.

Syntax

Arguments

• x — Left hand operand. - y — Right hand operand.

Returned value

Returns the sum of x and y

Examples

Adding two numbers

Adding an integer and a date

positiveModulo

Introduced in: v22.11

Calculates the remainder when dividing x by y. Similar to function modulo except that positiveModulo always return non-negative number.

Syntax

Arguments

• x — The dividend. (U)Int* or Float* or Decimal
• y — The divisor (modulus). (U)Int* or Float* or Decimal

Returned value

Returns the difference between x and the nearest integer not greater than x divisible by y.

Examples

Usage example

positiveModuloOrNull

Introduced in: v25.5

Calculates the remainder when dividing a by b. Similar to function positiveModulo except that positiveModuloOrNull will return NULL if the right argument is 0.

Syntax

Arguments

• x — The dividend. (U)Int*/Float32/64. - x — The divisor (modulus). (U)Int*/Float32/64.

Returned value

Returns the difference between x and the nearest integer not greater than x divisible by y, null when the divisor is zero.

Examples

positiveModuloOrNull