Arithmetic operators

OceanBase Database supports the commonly used arithmetic operators.

Overview

The following table describes the arithmetic operators supported in the current version of OceanBase Database.

Considerations

Rules for integer division

The following rules apply to integer division:

• The quotient is rounded to zero.

• The remainder has the same sign as the dividend.

Here is an example:

obclient> SELECT (-7) DIV (3.6), (-7) MOD (3.6);
+----------------+----------------+
| (-7) DIV (3.6) | (-7) MOD (3.6) |
+----------------+----------------+
|             -1 |           -3.4 |
+----------------+----------------+
1 row in set

obclient> SELECT (-7) DIV (-3.4), (-7) % (-3.4);
+-----------------+---------------+
| (-7) DIV (-3.4) | (-7) % (-3.4) |
+-----------------+---------------+
|               2 |          -0.2 |
+-----------------+---------------+
1 row in set

Results

The following rules apply to arithmetic operations:

• For -, +, and *, if both operands are integers, the result is calculated with BIGINT (64-bit) precision.

• If both operands are integers and one of them is unsigned, the result is an unsigned integer. For subtraction, if the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is signed even if one of the operands is unsigned.

• If the operands of +, -, /, *, or % are real numbers or string values, the result is calculated with the precision of the operand with the highest precision.

• When you use / to divide two exact-value operands, the number of decimal places in the result is the number of decimal places in the first operand plus the value of the div_precision_increment system variable (which defaults to 4). For example, the expression 5.15/0.013 has six decimal places (396.153846).