Skip to main content

Arithmetic Functions

Add

Substrait definition

Add takes two parameters and does an addition of the two values.

Add depends on the input types on what result it will give:

Left typeRight typeOutput
IntegerIntegerInteger
IntegerFloatFloat
FloatFloatFloat
DecimalIntegerDecimal
DecimalFloatDecimal
DecimalDecimalDecmial
Non numericIntegerNull
num numericFloatNull
num numericDecimalNull
Non numericNon numericNull

Only numeric inputs will return a result, otherwise it will return null.

SQL usage

In SQL the add function is called using the plus operator:

SELECT column1 + 13 FROM ...

Subtract

Substrait definition

Subtract takes two parameters and does a subtraction of the two values.

Subtract depends on the input types on what result it will give:

Left typeRight typeOutput
IntegerIntegerInteger
IntegerFloatFloat
FloatFloatFloat
DecimalIntegerDecimal
DecimalFloatDecimal
DecimalDecimalDecmial
Non numericIntegerNull
num numericFloatNull
num numericDecimalNull
Non numericNon numericNull

Only numeric inputs will return a result, otherwise it will return null.

SQL usage

In SQL the subtract function is called using the minus operator:

SELECT column1 - 13 FROM ...

Multiply

Substrait definition

Multipies two numbers.

Multiply depends on the input types on what result it will give:

Left typeRight typeOutput
IntegerIntegerInteger
IntegerFloatFloat
FloatFloatFloat
DecimalIntegerDecimal
DecimalFloatDecimal
DecimalDecimalDecmial
Non numericIntegerNull
num numericFloatNull
num numericDecimalNull
Non numericNon numericNull

SQL Usage

SELECT column1 * 3 FROM ...

Divide

Substrait definition

Divide two numbers.

Divide depends on the input types on what result it will give:

Left typeRight typeOutput
IntegerIntegerFloat
IntegerFloatFloat
FloatFloatFloat
DecimalIntegerDecimal
DecimalFloatDecimal
DecimalDecimalDecmial
Non numericIntegerNull
num numericFloatNull
num numericDecimalNull
Non numericNon numericNull

There are some special cases when dividing with zero:

  • 0 / 0 -> this results in NaN.
  • PositiveNumber / 0 -> +Infinity
  • NegativeNumber / 0 -> -Infinity

SQL Usage

SELECT column1 / 3 FROM ...

Negate

Substrait definition

Negates a numeric value, example:

  • 1 becomes -1
  • -1 becomes 1
  • 1.3 becomes -1.3

Non numeric values becomes 'null'.

SQL Usage

SELECT -column1 FROM ...

Modulo

Substrait definition

Calculate the remainder when dividing two numbers.

Modulo depends on the input types on what result it will give:

Left typeRight typeOutput
IntegerIntegerInteger
IntegerFloatFloat
FloatFloatFloat
DecimalIntegerDecimal
DecimalFloatDecimal
DecimalDecimalDecmial
Non numericIntegerNull
num numericFloatNull
num numericDecimalNull
Non numericNon numericNull

Taking modulo between two integers where the divider is 0, the return will be type double and the value NaN.

SQL Usage

SELECT column1 % 3 FROM ...

Power

Substrait definition

Calculate the power with the first argument being the base and the other the exponent.

Power depends on the input types on what result it will give:

Left typeRight typeOutput
IntegerIntegerInteger
IntegerFloatFloat
FloatFloatFloat
DecimalIntegerDecimal
DecimalFloatDecimal
DecimalDecimalDecmial
Non numericIntegerNull
num numericFloatNull
num numericDecimalNull
Non numericNon numericNull

SQL Usage

SELECT power(column1, 2) FROM ...

Sqrt

Substrait definition

Calculate the square root of a number.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalDecimal
Non numericNull

SQL Usage

SELECT sqrt(column1) FROM ...

Exp

Substrait definition

Calculates the constant e raised to the power of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT exp(column1) FROM ...

Cos

Substrait definition

Calculate the cosine of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT cos(column1) FROM ...

Sin

Substrait definition

Calculate the sine of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT sin(column1) FROM ...

Tan

Substrait definition

Calculate the tangent of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT tan(column1) FROM ...

Cosh

Substrait definition

Calculate the hyperbolic cosine of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT cosh(column1) FROM ...

Sinh

Substrait definition

Calculate the hyperbolic sine of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT sinh(column1) FROM ...

Tanh

Substrait definition

Calculate the hyperbolic tangent of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT tanh(column1) FROM ...

Acos

Substrait definition

Calculate the arccosine of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT acos(column1) FROM ...

Asin

Substrait definition

Calculate the arcsine of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT asin(column1) FROM ...

Atan

Substrait definition

Calculate the arctangent of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT atan(column1) FROM ...

Acosh

Substrait definition

Calculate the hyperbolic arccosine of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT acosh(column1) FROM ...

Asinh

Substrait definition

Calculate the hyperbolic arcsine of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT asinh(column1) FROM ...

Atanh

Substrait definition

Calculate the hyperbolic arctangent of the input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT atanh(column1) FROM ...

Atan2

Substrait definition

Calculate the arctangent of two input value.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT atan2(column1) FROM ...

Radians

Substrait definition

Convert the input value from degrees to radians.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT radians(column1) FROM ...

Degrees

Substrait definition

Convert the input value from radians to degrees.

Output types:

TypeOutput
IntegerFloat
FloatFloat
DecimalFloat
Non numericNull

SQL Usage

SELECT degrees(column1) FROM ...

Abs

Substrait definition

Calculate the absolute value of the input value.

Output types:

TypeOutput
IntegerInteger
FloatFloat
DecimalDecimal
Non numericNull

SQL Usage

SELECT abs(column1) FROM ...

Sign

Substrait definition

Get the sign of the input value.

Output:

TypeOutput TypeOutput range
IntegerInteger[-1, 0, 1]
FloatFloat[-1.0, 0.0, 1.0, NaN]
DecimalDecimal[-1.0, 0.0, 1.0]
Non numericNullNull

SQL Usage

SELECT sign(column1) FROM ...