Arithmetic operators

OceanBase Database supports general arithmetic operators.

Overview

The following table describes the arithmetic operators supported by OceanBase Database.

Notes

Operation rules

Operation rules of integer division:

• 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.

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 (0.01 sec)

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

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.