Arithmetic operators

OceanBase Database supports general arithmetic operators.

Overview

The following table lists the arithmetic operators that are supported by the current version of OceanBase Database.

Description

Operation rules

Operation rules of integer division are as follows:

• The quotient is rounded off toward 0 regardless of whether the quotient is positive or negative.

• The positive/negative attribute of the remainder is the same as that of the dividend.

Here are some examples:

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

Operation results

The results of arithmetic operations are determined by the following rules:

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

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

• If the operands of the +, -, /, *, and % operators are real numbers or strings, the precision of the result is the precision of the operand with the maximum precision.

• When you perform a division operation with two exact-value operands by using the / operator, the number of decimal places of the result are the number of decimal places of the first operand plus the value of the div_precision_increment variable. By default, the value of this variable is 4. For example, the result of the expression 5.15/0.013 is 396.153846, which has six decimal places.