Flow Control Functions

1

IF()

Supported

• The expr1 input parameter supports only the Boolean type. If an input parameter of the non-Boolean type cannot be converted to the Boolean 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, the output is of the TEXT type. In MySQL, the output is of the VARCHAR type.

2

IFNULL()

Supported

• 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, the output is of the TEXT type. In MySQL, the output is of the VARCHAR type.
• If one input parameter is of the FLOAT4 type and the other is of any type in the numeric category, the return type is DOUBLE. In MySQL, if one input parameter is of FLOAT4 type and the other input parameter is of the TINYINT, UNSIGNED TINYINT, SMALLINT, UNSIGNED SMALLINT, MEDIUMINT, UNSIGNED MEDIUMINT, or BOOL type, the FLOAT4 type is returned. If the first input parameter is of FLOAT4 type and the second input parameter is of BIGINT or UNSIGNED BIGINT type, the FLOAT type is returned.

3

NULLIF()

Supported

• The NULLIF() type derivation in GaussDB complies with the following logic:
  1. 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.
  2. 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.
  3. 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.
  1. If the type of the first input parameter is TINYINT, SMALLINT, MEDIUMINT, INT, or BOOL, the output is of the INT type.
  2. If the type of the first input parameter is BIGINT, the output is of the BIGINT type.
  3. 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.
  4. If the type of the first input parameter is UNSIGNED BIGINT, the output is of the UNSIGNED BIGINT type.
  5. If the type of the first input parameter is FLOAT, DOUBLE, or REAL, the output is of the DOUBLE type.
  6. If the type of the first input parameter DECIMAL or NUMERIC, the output is of the DECIMAL type.
  7. If the type of the first input parameter is DATE, TIME, DATE, DATETIME, TIMESTAMP, CHAR, VARCHAR, TINYTEXT, ENUM, or SET, the output is of the VARCHAR type.
  8. If the type of the first input parameter is TEXT, MEDIUMTEXT, or LONGTEXT, the output is of the LONGTEXT type.
  9. If the type of the first input parameter is TINYBLOB, the output is of the VARBINARY type.
  10. If the type of the first input parameter is MEDIUMBLOB or LONGBLOB, the output is of the LONGBLOB type.
  11. If the type of the first input parameter is BLOB, the output is of the BLOB type.

4

ISNULL()

Supported

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.