User GuidesAPI ReferenceRelease Notes
Doc HomeHelp CenterLog In

Arithmetic Expressions

Arithmetic expressions perform arithmetic operations on numerical values.

Supported Arithmetic Operations

Tamr supports arithmetic expressions for numerical values that use the operators +, -, *, %, and /, as well as unary negation (that is, negative numbers are identified by - before the value).

To keep the data in the numerical format, you may need to use a data type conversion function such as to_int, to_double, or to_long.

The Remainder Operator and the pmod() Function

The % operator returns the remainder of the division of the dividend by the divisor. As in Java, it ignores negative divisors. It allows positive and negative dividends.

For example, for a positive dividend 19, the result of 19%5 is 4. For a negative dividend of -19, the result of -19%5 is -4.

In addition to the % operator, Tamr supports the pmod() function. pmod() uses the following formula:

for numbers x and n, (x % n + n) % n.

pmod() differs in behavior from the % operator in that it returns only positive remainders, even in cases when the dividend is a negative number. Specifically:

  • The % operator implements the standard Java modulo function which returns negative results if the dividend is negative, as in -19%5 is -4
  • In Tamr Core, the pmod() function returns strictly positive results when given any input (positive or negative dividend).

For example, for the pair of values -19,5 the % operator in -19%5 returns -4, but the pmod(-19,5) function calculates and returns the positive remainder of 1. Specifically, -19%5 is -4, -4+5=1 and 1%5 is 1.

Examples of the % Operator

The following query uses the % operator:

SELECT dividend % divisor AS result

It produces the following results for these values:

-19 % 5 = -4
22 % 5 = 2
5 % 3 = 2
5%-3 = 2 (negative divisor is ignored)

Note: You cannot divide by zero and therefore x%0 is null.