Comparison Functions

1

COALESCE()

Supported, with differences.

In the union distinct scenario, the precision of the return value is different from that in MySQL.

If there is an implicit type conversion error in the subsequent parameter expression of the first parameter that is not NULL, MySQL ignores the error while GaussDB displays a type conversion error. When the parameter is a MIN or MAX function, the return value type is different from that in MySQL.

2

INTERVAL()

Supported.

-

3

GREATEST()

Supported, with differences.

If the return value type in MySQL is binary string (such as BINARY, VARBINARY, or BLOB), the return value type in GaussDB is LONGBLOB. If the return value type in MySQL is non-binary string (such as CHAR, VARCHAR, or TEXT), the return value type in GaussDB is TEXT.

If the input parameter of the function contains NULL and the function is called after the WHERE keyword, the returned result is inconsistent with that of MySQL 5.7. This problem lies in MySQL 5.7. Since MySQL 8.0 has resolved this problem, GaussDB are consistent with MySQL 8.0.

4

LEAST()

Supported, with differences.

If the return value type in MySQL is binary string (such as BINARY, VARBINARY, or BLOB), the return value type in GaussDB is LONGBLOB. If the return value type in MySQL is non-binary string (such as CHAR, VARCHAR, or TEXT), the return value type in GaussDB is TEXT.

If the input parameter of the function contains NULL and the function is called after the WHERE keyword, the returned result is inconsistent with that of MySQL 5.7. This problem lies in MySQL 5.7. Since MySQL 8.0 has resolved this problem, GaussDB are consistent with MySQL 8.0.

5

ISNULL()

Supported, with differences.

• The return value type of a function differs in MySQL 5.7 and MySQL 8.0. Return types are compatible with MySQL 8.0 because its behavior is more appropriate.
• When some aggregate functions are nested, the returned results in MySQL 5.7 and MySQL 8.0 are different in some scenarios. Return values are compatible with MySQL 8.0 because its behavior is more appropriate.

  m_db=# SELECT isnull(avg(1.23));
  ?column?
  ----------
  f
  (1 row)
  
  m_db=# SELECT isnull(group_concat(1.23));
  ?column?
  ----------
  f
  (1 row)
  
  m_db=# SELECT isnull(max('1.23'));
  ?column?
  ----------
  f
  (1 row)
  
  m_db=# SELECT isnull(min(1/2));
  ?column?
  ----------
  f
  (1 row)
  
  m_db=# SELECT isnull(std(3.14159 * 1.2345));
  ?column?
  ----------
  f
  (1 row)
  
  m_db=# SELECT isnull(sum('0.23400'));
  ?column?
  ----------
  f
  (1 row)