Help Center/ GaussDB/ MySQL Compatibility(Centralized)/ MySQL Compatibility in M-Compatible Mode/ System Functions/ Arithmetic Functions
Updated on 2024-12-06 GMT+08:00
View PDF
Share

Arithmetic Functions

Table 1 Arithmetic functions

No.

MySQL

GaussDB

Difference

1

ABS()

Supported.

-

2

ACOS()

Supported.

-

3

ASIN()

Supported.

-

4

ATAN()

Supported.

-

5

ATAN2()

Supported.

-

6

CEILING()

Supported, with differences.

Some operation result types and the precision of CREATE TABLE AS are inconsistent with that in MySQL.

  1. If the input parameter is of the INT type, the return value type is BIGINT in GaussDB and INT in MySQL.
  2. If the input parameter is of the BIGINT or BIGINT UNSIGNED type and the input parameter value contains 20 or more characters (including the sign bit), GaussDB returns an integer, whereas MySQL 5.7 returns a decimal. In this case, the difference lies in the CEILING function that is used as the inner function in nesting. 
    SET m_format_behavior_compat_options='enable_precision_decimal';
CREATE TABLE tt AS SELECT ceiling(-9223372036854775808);
DESC tt;

    The return type of MySQL table fields is DECIMAL(16,0).

    The return type of GaussDB table fields is BIGINT(17).

  3. If the input parameter is of the NUMERIC type, the return type may be different from that in MySQL. If the parameter is a constant or table field, the result type is the same as that in MySQL 5.7. For other types of input parameters, such as nested input parameters, the results are different. GaussDB returns the result of the NUMERIC type, whereas MySQL may return an integer. 
    SET m_format_behavior_compat_options='enable_precision_decimal';
CREATE TABLE t AS SELECT ceiling(-5.5);
DESC t;

    The return type of MySQL table fields is INT(5).

    The return type of GaussDB table fields is INT(5).

    SET m_format_behavior_compat_options='enable_precision_decimal';
CREATE TABLE t AS SELECT ceiling(abs(5.5));
DESC t;

    The return type of MySQL table fields is INT(4).

    The return type of GaussDB table fields is DECIMAL(3,0).

7

COS()

Supported.

-

8

DEGREES()

Supported.

-

9

EXP()

Supported.

-

10

FLOOR()

Supported, with differences.

Some operation result types and the precision of CREATE TABLE AS are inconsistent with that in MySQL.

  1. If the input parameter is of the INT type, the return value type is BIGINT in GaussDB and INT in MySQL.
  2. If the input parameter is of the BIGINT or BIGINT UNSIGNED type and the input parameter value contains 20 or more characters (including the sign bit), GaussDB returns an integer, whereas MySQL 5.7 returns a decimal. In this case, the difference lies in the floor function that is used as the inner function in nesting. 
    -- In loose mode:
SET m_format_behavior_compat_options='enable_precision_decimal';
CREATE TABLE tt AS SELECT floor(-9223372036854775808);
DESC tt;

    The return type of MySQL table fields is DECIMAL(16,0).

    The return type of GaussDB table fields is BIGINT(17).

  3. If the input parameter is of the NUMERIC type, the return type may be different from that in MySQL. If the parameter is a constant or table field, the result type is the same as that in MySQL 5.7. For other types of input parameters, such as nested input parameters, the results are different. GaussDB returns the result of the NUMERIC type, whereas MySQL may return an integer. 
    SET m_format_behavior_compat_options='enable_precision_decimal';
CREATE TABLE t AS SELECT floor(-5.5);
DESC t;

    The return type of MySQL table fields is INT(5).

    The return type of GaussDB table fields is INT(5).

    SET m_format_behavior_compat_options='enable_precision_decimal';
CREATE TABLE t AS SELECT floor(abs(5.5));
DESC t;

    The return type of MySQL table fields is INT(4).

    The return type of GaussDB table fields is DECIMAL(3,0).

11

LN()

Supported.

-

12

LOG()

Supported.

-

13

LOG10()

Supported.

-

14

LOG2()

Supported.

-

15

PI()

Supported.

When the precision transfer function is disabled, that is, m_format_behavior_compat_options is not set to enable_precision_decimal, the returned value of the PI function is rounded off to six decimal places in MySQL, but is rounded off to 15 decimal places in GaussDB.

16

POW()

Supported.

-

17

POWER()

Supported.

-

18

RAND()

Supported.

-

19

SIGN()

Supported.

-

20

SIN()

Supported.

-

21

SQRT()

Supported.

-

22

TAN()

Supported.

-

23

TRUNCATE()

Supported.

-

24

CEIL()

Supported.

-

25

CRC32()

Supported, with differences.

When the length of the inserted string of the BINARY type is less than the target length, the padding characters in GaussDB are different from those in MySQL. Therefore, when the input parameter is of the BINARY type, the function result in GaussDB is different from that in MySQL.

26

CONV()

Supported.

-
Parent topic: System Functions