Flow Control Functions

IF()

Supported with differences

• The expr1 input parameter supports only the bool type. If an input parameter of the 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 types of expr2 and expr3 are NUMERIC, STRING, or TIME, the output is of the TEXT type in GaussDB and of the VARCHAR type in MySQL.

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 types of expr1 and expr2 are NUMERIC, STRING, or TIME, the output is of the TEXT type in GaussDB and of the VARCHAR type in MySQL.
• If one of the two input parameters is of the FLOAT4 type and the other is of any type of NUMERIC, the return value is of the DOUBLE type. In MySQL, if one of the input parameters is of the FLOAT4 type and the other is of any type of TINYINT, UNSIGNED TINYINT, SMALLINT, UNSIGNED SMALLINT, MEDIUMINT, UNSIGNED MEDIUMINT, or BOOL, the return value is of the FLOAT4 type. If the first input parameter is of the FLOAT4 type and the second input parameter is of the BIGINT or UNSIGNED BIGINT type, the return value is of 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 an equivalent comparison operator, but the equivalent 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 an equivalent comparison operator, and no equivalent comparison operator exists 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 type of the first input parameter is TINYINT, SMALLINT, MEDIUMINT, INT, or BOOL, the output is of the INT type.
  • If the type of the first input parameter is BIGINT, the output is of the BIGINT type.
  • When the type of the first input parameter is UNSIGNED TINYINT, UNSIGNED SMALLINT, UNSIGNED MEDIUMINT, UNSIGNED INT, or BIT, the output is of the UNSIGNED INT type.
  • If the type of the first input parameter is UNSIGNED BIGINT, the output is of the UNSIGNED BIGINT type.
  • If the type of the first input parameter is FLOAT, DOUBLE, or REAL, the output is of the DOUBLE type.
  • If the type of the first input parameter is DECIMAL or NUMERIC, the output is of the DECIMAL type.
  • If the type of the first input parameter is DATE, TIME, DATETIME, TIMESTAMP, CHAR, VARCHAR, TINYTEXT, ENUM, or SET, the output is of the VARCHAR type.
  • If the type of the first input parameter is TEXT, MEDIUMTEXT, or LONGTEXT, the output is of the LONGTEXT type.
  • If the type of the first input parameter is TINYBLOB, the output is of the VARBINARY type.
  • If the type of the first input parameter is MEDIUMBLOB or LONGBLOB, the output is of the LONGBLOB type.
  • If the type of the first input parameter is BLOB, 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.