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.