Skip to main content
Skip to main content
Edit this page

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

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

Syntax

Arguments

  • x — An integer value.

Returned value

x with bytes reversed

Examples

Usage example

8-bit

16-bit

32-bit

64-bit

divide​

Introduced in: v1.1

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

Syntax

Arguments

Returned value

The result of division with given scale. Type: Decimal256.

Examples

Example 1

Example 2

gcd​

Introduced in: v1.1

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

Syntax

Arguments

  • x — Value to check if infinite. Float32/Float64
  • y — Fallback value. Float32/Float64

Returned value

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

Examples

Usage example

intDiv​

Introduced in: v1.1

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

intDivOrZero​

Introduced in: v1.1

Syntax

Arguments

  • a — Left hand operand.
  • b — Right hand operand.

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

Syntax

Arguments

  • x — Number to check for finiteness. Float32 or Float64.

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

Syntax

Arguments

  • x — Number to check for infiniteness. Float32 or Float64.

Returned value

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

Examples

Test if a number is infinite

isNaN​

Introduced in: v1.1

Syntax

Arguments

  • x — Argument to evaluate for if it is NaN

Returned value

1 if NaN, otherwise 0

Examples

Usage example

lcm​

Introduced in: v1.1

Syntax

Arguments

  • x — First integer
  • y — Second integer

Returned value

The least common multiple of x and y.

Examples

Usage example

max2​

Introduced in: v21.11

Syntax

Arguments

  • x — First value
  • y — Second value

Returned value

Returns the bigger value of x and y

Examples

Usage example

min2​

Introduced in: v21.11

Syntax

Arguments

  • x — First value
  • y — Second value

Returned value

Returns the smaller value of x and y

Examples

Usage example

minus​

Introduced in: v1.1

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

Syntax

Arguments

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

Returned value

The remainder of a % b

Examples

Usage example

moduloOrZero​

Introduced in: v20.3

Syntax

Arguments

Returned value

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

Examples

Usage example

multiply​

Introduced in: v1.1

Syntax

Arguments

  • x — factor
  • y — factor

Returned value

The product of x and y

Examples

Multiplying two numbers

multiplyDecimal​

Introduced in: v22.12

Syntax

Arguments

  • a — First value. Type Decimal.
  • b — Second value. Type Decimal.
  • result_scale — Scale of result. Type Int/UInt.

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

Syntax

Arguments

  • x — The value to negate.

Returned value

Returns -x from x

Examples

Usage example

plus​

Introduced in: v1.1

Syntax

Arguments

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

Returned value

The sum of x and y

Examples

Adding two numbers

Adding an integer and a date

positiveModulo​

Introduced in: v22.11

Syntax

Arguments

Returned value

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

Examples

Usage example

positiveModuloOrNull​

Introduced in: v22.11

Syntax

Arguments

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

positiveModulo