Flow Control Functions

IF()

If the first parameter is TRUE and the third parameter expression contains an implicit type conversion error, or if the first parameter is FALSE and the second parameter expression contains an implicit type conversion error, MySQL ignores the error while GaussDB displays a type conversion error.

IFNULL()

• If the first parameter is not NULL and the expression of the second parameter contains an implicit type conversion error, MySQL ignores the error while GaussDB reports a type conversion error.
• If the input parameter type is FLOAT, the result value precision in GaussDB is the same as that in MySQL 8.0. For example:

  m_db=# CREATE TABLE t1(c1 float);
  CREATE TABLE
  m_db=# INSERT INTO t1 VALUES(2.123);
  INSERT 0 1
  
  -- Behavior in GaussDB:
  m_db=# SELECT ifnull(c1, c1) FROM t1;
   ifnull 
  --------
    2.123
  (1 row)
  
  -- Behavior in MySQL 5.7:
  mysql> SELECT ifnull(c1, c1) FROM t1;
  +-------------------+
  | ifnull(c1, c1)    |
  +-------------------+
  | 2.122999906539917 |
  +-------------------+
  1 row in set (0.00 sec)
  
  -- Behavior in MySQL 8.0:
  mysql> SELECT ifnull(c1, c1) FROM t1;
  +----------------+
  | ifnull(c1, c1) |
  +----------------+
  |          2.123 |
  +----------------+
  1 row in set (0.00 sec)

NULLIF()

• In MySQL 5.7 and MySQL 8.0, the return value types of functions are different. Those in MySQL 8.0 are more reasonable. Therefore, the return value types of GaussDB functions are compatible with MySQL 8.0.
• If the input parameter type is FLOAT, the result value precision in GaussDB is the same as that in MySQL 8.0. For example:

  m_db=# CREATE TABLE t1(c1 float);
  CREATE TABLE
  m_db=# INSERT INTO t1 VALUES(2.123);
  INSERT 0 1
  
  -- Behavior in GaussDB:
  m_db=# SELECT nullif(c1, 1) FROM t1;
   nullif 
  --------
    2.123
  (1 row)
  
  -- Behavior in MySQL 5.7:
  mysql> SELECT nullif(c1, 1) SELECT t1;
  +-------------------+
  | nullif(c1, 1)     |
  +-------------------+
  | 2.122999906539917 |
  +-------------------+
  1 row in set (0.00 sec)
  
  -- Behavior in MySQL 8.0:
  mysql> SELECT nullif(c1, 1) SELECT t1;
  +---------------+
  | nullif(c1, 1) |
  +---------------+
  |         2.123 |
  +---------------+
  1 row in set (0.00 sec)