Mathematical operators are provided to manipulate values of data types supported by PolarDB-O. For types without common mathematical conventions for all possible permutations (for example, date/time types), the actual behavior is described in subsequent sections.

The following table shows the allowed mathematical operators.

Table 1. Mathematical operators
Operator Description Example Result
+ Addition 2 + 3 5
- Subtraction 2 - 3 -1
* Multiplication 2 * 3 6
/ Division (integer division truncates results) 4 / 2 2
** Exponentiation operator 2 ** 3 8

The following table shows the available mathematical functions. Many of these functions are provided in multiple forms with different argument types. Note that any form of function returns the same data type as its argument. The functions that involve DOUBLE PRECISION data are mostly implemented on top of the C library of the host system. The accuracy and behavior in boundary cases may vary depending on the host system.

Table 2. Mathematical functions
Function Return type Description Example Result
ABS(x) Same as x The absolute value. ABS(-17.4) 17.4
CEIL(DOUBLE PRECISION or NUMBER) Same as input The smallest integer not less than argument. CEIL(-42.8) -42
EXP(DOUBLE PRECISION or NUMBER) Same as input Exponential EXP(1.0) 2.71828182845904 52
FLOOR(DOUBLE PRECISION or NUMBER) Same as input The largest integer not greater than argument. FLOOR(-42.8) 43
LN(DOUBLE PRECISION or NUMBER) Same as input Natural logarithm LN(2.0) 0.69314718055994 53
LOG(b NUMBER, X NUMBER) NUMBER The logarithm to base b. LOG(2.0, 64.0) 6.00000000000000 00
MOD(y, X) Same as argument types The remainder of y/x. MOD(9, 4) 1
NVL(x, y) Same as argument types; where both arguments are of the same data type. If X is null, NVL returns y. NVL(9, 0) 9
POWER(a DOUBLE PRECISION, b DOUBLE PRECISION) DOUBLE PRECISION a raised to the power of b POWER(9.0, 3.0) 729.000000000000 0000
POWER(a NUMBER, b NUMBER) NUMBER a raised to the power of b POWER(9.0, 3.0) 729.000000000000 0000
ROUND(DOUBLE PRECISION or NUMBER) Same as input Rounds to the nearest integer. ROUND(42.4) 42
ROUND(v NUMBER, s INTEGER) NUMBER Rounds to s decimal places. ROUND(42.4382, 2) 42.44
SIGN(DOUBLE PRECISION or NUMBER) Same as input Sign of the argument (-1, 0, +1) SIGN(-8.4) -1
SQRT(DOUBLE PRECISION or NUMBER) Same as input Square root SQRT(2.0) 1.41421356237309 5
TRUNC(DOUBLE PRECISION or NUMBER) Same as input Truncates toward zero. TRUNC(42.8) 42
TRUNC(v NUMBER, s INTEGER) NUMBER Truncates to s decimal places. TRUNC(42.4382, 2) 42.43
WIDTH BUCKET( op NUMBER, b1 NUMBER, b2 NUMBER, count INTEGER) INTEGER Returns the bucket to which op will be assigned in an equidepth histogram with count buckets, in the range b1 to b2. WIDTH BUCKET(5.35, 0.024, 10.06, 5) 3

The following table shows the available trigonometric functions. The arguments and return values of all trigonometric functions are of type DOUBLE PRECISION.

Table 3. Trigonometric functions
Function Description
ACOS(x) Inverse cosine
ASIN(x) Inverse sine
ATAN(x) Inverse tangent
ATAN2 (x, y) Inverse tangent of x/y
COS(x) Cosine
SIN(x) Sine
TAN(x) Tangent