Updated on 2025-09-09 GMT+08:00

Flow Control Functions

Table 1 Flow control functions

MySQL

GaussDB

Difference

IF()

Supported, with differences.

  • The input parameter expr1 supports only the BOOL type. If the input parameter of a non-BOOL type cannot be converted to the BOOL type, an error is reported.
  • If the types of expr2 and expr3 are different and no implicit conversion function exists between the two types, an error is reported.
  • If the two input parameters are of the same type, the input parameter type is returned.
  • If the expr2 and expr3 input parameters are of the NUMERIC, STRING, or TIME type respectively, GaussDB outputs the TEXT type, while MySQL outputs the VARCHAR type.

IFNULL()

Supported, with differences.

  • If the types of expr1 and expr2 are different and no implicit conversion function exists between the two types, an error is reported.
  • If the two input parameters are of the same type, the input parameter type is returned.
  • If the expr1 and expr2 input parameters are of the NUMERIC, STRING, or TIME type respectively, GaussDB outputs the TEXT type, while MySQL outputs the VARCHAR type.
  • If the first input parameter is of the FLOAT4 type and the other is of the BIGINT or UNSIGNED BIGINT type, GaussDB returns the DOUBLE type, while MySQL returns the FLOAT type.

NULLIF()

Supported, with differences.

  • The NULLIF() type derivation in GaussDB complies with the following logic:
    • If the data types of two parameters are different and the two input parameter types have an equality comparison operator, the left value type corresponding to the equality comparison operator is returned. Otherwise, the two input parameter types are forcibly compatible.
    • If an equality comparison operator exists after forcible type compatibility, the left value type of the equality comparison operator after forcible type compatibility is returned.
    • If the corresponding equality operator cannot be found after forcible type compatibility, an error is reported.
      -- The two input parameter types have an equality comparison operator.
      gaussdb=# SELECT pg_typeof(nullif(1::int2, 2::int8));
       pg_typeof
      -----------
       smallint
      (1 row)
      -- The two input parameter types do not have the equality comparison operator, but the equality comparison operator can be found after forcible type compatibility.
      gaussdb=# SELECT pg_typeof(nullif(1::int1, 2::int2));
       pg_typeof
      -----------
       bigint
      (1 row)
      
      -- The two input parameter types do not have the equality comparison operator, and the equality comparison operator does not exist after forcible type compatibility.
      gaussdb=# SELECT nullif(1::bit, '1'::MONEY);
      ERROR:  operator does not exist: bit = money
      LINE 1: SELECT nullif(1::bit, '1'::MONEY);
                     ^
      HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
      CONTEXT:  referenced column: nullif
  • The MySQL output type is related only to the type of the first input parameter.
    • If the first input parameter is of the TINYINT, SMALLINT, MEDIUMINT, INT, or BOOL type, the output is of the INT type.
    • If the first input parameter is of the BIGINT type, the output is of the BIGINT type.
    • If the first input parameter is of the UNSIGNED TINYINT, UNSIGNED SMALLINT, UNSIGNED MEDIUMINT, UNSIGNED INT, or BIT type, the output is of the UNSIGNED INT type.
    • If the first input parameter is of the UNSIGNED BIGINT type, the output is of the UNSIGNED BIGINT type.
    • If the first input parameter is of the FLOAT, DOUBLE, or REAL type, the output is of the DOUBLE type.
    • If the first input parameter is of the DECIMAL or NUMERIC type, the output is of the DECIMAL type.
    • If the first input parameter is of the DATE, TIME, DATE, DATETIME, TIMESTAMP, CHAR, VARCHAR, TINYTEXT, ENUM, or SET type, the output is of the VARCHAR type.
    • If the first input parameter is of the TEXT, MEDIUMTEXT, or LONGTEXT type, the output is of the LONGTEXT type.
    • If the first input parameter is of the TINYBLOB type, the output is of the VARBINARY type.
    • If the first input parameter is of the MEDIUMBLOB or LONGBLOB type, the output is of the LONGBLOB type.
    • If the first input parameter is of the BLOB type, the output is of the BLOB type.

ISNULL()

Supported, with differences.

In GaussDB, the return value is t or f of the BOOLEAN type. In MySQL, the return value is 1 or 0 of the INT type.