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:

SELECT toTypeName(0), toTypeName(0 + 0), toTypeName(0 + 0 + 0), toTypeName(0 + 0 + 0 + 0)

┌─toTypeName(0)─┬─toTypeName(plus(0, 0))─┬─toTypeName(plus(plus(0, 0), 0))─┬─toTypeName(plus(plus(plus(0, 0), 0), 0))─┐
│ UInt8         │ UInt16                 │ UInt32                          │ UInt64                                   │
└───────────────┴────────────────────────┴─────────────────────────────────┴──────────────────────────────────────────┘

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

abs(x)

Arguments

• x — Value to get the absolute value of

Returned value

The absolute value of x

Examples

Usage example

SELECT abs(-0.5)

0.5

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:

┌─toIPv4(byteSwap(toUInt32(toIPv4('205.251.199.199'))))─┐
│ 199.199.251.205                                       │
└───────────────────────────────────────────────────────┘

Syntax

byteSwap(x)

Arguments

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

Returned value

Returns x with bytes reversed. (U)Int*

Examples

Usage example

SELECT byteSwap(3351772109)

3455829959

8-bit

SELECT byteSwap(54)

54

16-bit

SELECT byteSwap(4135)

10000

32-bit

SELECT byteSwap(3351772109)

3455829959

64-bit

SELECT byteSwap(123294967295)

18439412204227788800

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

divide(x, y)

Arguments

• x — Dividend - y — Divisor

Returned value

The quotient of x and y

Examples

Dividing two numbers

SELECT divide(25,5) AS quotient, toTypeName(quotient)

5 Float64

Dividing by zero

SELECT divide(25,0)

inf

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

divideDecimal(x, y[, result_scale])

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

divideDecimal(toDecimal256(-12, 0), toDecimal32(2.1, 1), 10)

┌─divideDecimal(toDecimal256(-12, 0), toDecimal32(2.1, 1), 10)─┐
│                                                -5.7142857142 │
└──────────────────────────────────────────────────────────────┘

Example 2

SELECT toDecimal64(-12, 1) / toDecimal32(2.1, 1);
SELECT toDecimal64(-12, 1) as a, toDecimal32(2.1, 1) as b, divideDecimal(a, b, 1), divideDecimal(a, b, 5);

┌─divide(toDecimal64(-12, 1), toDecimal32(2.1, 1))─┐
│                                             -5.7 │
└──────────────────────────────────────────────────┘
┌───a─┬───b─┬─divideDecimal(toDecimal64(-12, 1), toDecimal32(2.1, 1), 1)─┬─divideDecimal(toDecimal64(-12, 1), toDecimal32(2.1, 1), 5)─┐
│ -12 │ 2.1 │                                                       -5.7 │                                                   -5.71428 │
└─────┴─────┴────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────┘

divideOrNull

Introduced in: v25.5

Same as divide but returns NULL when dividing by zero.

Syntax

divideOrNull(x, y)

Arguments

• x — Dividend - y — Divisor

Returned value

The quotient of x and y, or NULL.

Examples

Dividing by zero

SELECT divideOrNull(25, 0)

\N

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

gcd(x, y)

Arguments

• x — First integer - y — Second integer

Returned value

The greatest common divisor of x and y.

Examples

Usage example

SELECT gcd(12, 18)

6

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

ifNotFinite(x,y)

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

SELECT 1/0 AS infimum, ifNotFinite(infimum,42)

inf  42

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

intDiv(x, y)

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

SELECT intDiv(toFloat64(1), 0.001) AS res, toTypeName(res)

┌──res─┬─toTypeName(intDiv(toFloat64(1), 0.001))─┐
│ 1000 │ Int64                                   │
└──────┴─────────────────────────────────────────┘

Quotient does not fit in the range of the dividend

SELECT
intDiv(1, 0.001) AS res,
toTypeName(res)

Received exception from server (version 23.2.1):
Code: 153. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot perform integer division, because it will produce infinite or too
large number: While processing intDiv(1, 0.001) AS res, toTypeName(res).
(ILLEGAL_DIVISION)

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

intDivOrNull(x, y)

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

SELECT intDivOrNull(1, 0)

\N

Dividing a minimal negative number by minus 1

SELECT intDivOrNull(-9223372036854775808, -1)

\N

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

intDivOrZero(a, b)

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

SELECT intDivOrZero(1, 0)

0

Dividing a minimal negative number by minus 1

SELECT intDivOrZero(0.05, -1)

0

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

isFinite(x)

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

SELECT isFinite(inf)

0

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

isInfinite(x)

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

SELECT isInfinite(inf), isInfinite(NaN), isInfinite(10))

1 0 0

isNaN

Introduced in: v1.1

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

Syntax

isNaN(x)

Arguments

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

Returned value

1 if NaN, otherwise 0

Examples

Usage example

SELECT isNaN(NaN)

1

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

lcm(x, y)

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

SELECT lcm(6, 8)

24

max2

Introduced in: v21.11

Returns the bigger of two numeric values x and y.

Syntax

max2(x, y)

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

SELECT max2(-1, 2)

2

min2

Introduced in: v21.11

Returns the smaller of two numeric values x and y.

Syntax

min2(x, y)

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

SELECT min2(-1, 2)

-1

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

minus(x, y)

Arguments

• x — Minuend. - y — Subtrahend.

Returned value

x minus y

Examples

Subtracting two numbers

SELECT minus(10, 5)

5

Subtracting an integer and a date

SELECT minus(toDate('2025-01-01'),5)

2024-12-27

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

modulo(a, b)

Arguments

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

Returned value

The remainder of a % b

Examples

Usage example

SELECT modulo(5, 2)

1

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

moduloOrNull(x, y)

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

SELECT moduloOrNull(5, 0)

\N

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

moduloOrZero(a, b)

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

SELECT moduloOrZero(5, 0)

0

multiply

Introduced in: v1.1

Calculates the product of two values x and y.

Syntax

multiply(x, y)

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

SELECT multiply(5,5)

25

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

multiplyDecimal(a, b[, result_scale])

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

SELECT multiplyDecimal(toDecimal256(-12, 0), toDecimal32(-2.1, 1), 1)

25.2

Difference with regular multiplication

SELECT multiplyDecimal(toDecimal256(-12, 0), toDecimal32(-2.1, 1), 1)

┌─multiply(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4))─┐
│                                               -26.8609633 │
└───────────────────────────────────────────────────────────┘
┌─multiplyDecimal(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4))─┐
│                                                         -26.8609 │
└──────────────────────────────────────────────────────────────────┘

Decimal overflow

SELECT
    toDecimal64(-12.647987876, 9) AS a,
    toDecimal64(123.967645643, 9) AS b,
    multiplyDecimal(a, b);
SELECT
    toDecimal64(-12.647987876, 9) AS a,
    toDecimal64(123.967645643, 9) AS b,
    a * b;

┌─────────────a─┬─────────────b─┬─multiplyDecimal(toDecimal64(-12.647987876, 9), toDecimal64(123.967645643, 9))─┐
│ -12.647987876 │ 123.967645643 │                                                               -1567.941279108 │
└───────────────┴───────────────┴───────────────────────────────────────────────────────────────────────────────┘
Received exception from server (version 22.11.1):
Code: 407. DB::Exception: Received from localhost:9000. DB::Exception: Decimal math overflow:
While processing toDecimal64(-12.647987876, 9) AS a, toDecimal64(123.967645643, 9) AS b, a * b. (DECIMAL_OVERFLOW)

negate

Introduced in: v1.1

Negates the argument x. The result is always signed.

Syntax

negate(x)

Arguments

• x — The value to negate.

Returned value

Returns -x from x

Examples

Usage example

SELECT negate(10)

-10

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

plus(x, y)

Arguments

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

Returned value

Returns the sum of x and y

Examples

Adding two numbers

SELECT plus(5,5)

10

Adding an integer and a date

SELECT plus(toDate('2025-01-01'),5)

2025-01-06

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

positiveModulo(x, y)

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

SELECT positiveModulo(-1, 10)

9

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

positiveModuloOrNull(x, y)

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

SELECT positiveModuloOrNull(5, 0)

\N