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 displays a type conversion error.
• When the input parameter is of the float type, the precision of the GaussDB result value is the same as that of 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.
• When the input parameter is of the float type, the precision of the GaussDB result value is the same as that of 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)