Type Conversions

Operands in an expression must be of the same general data type — numeric, character, binary, or date/time — in order for FedSQL to resolve the expression. When it is necessary, FedSQL converts an operand's data type to another data type, depending on the operands and operators in the expression. This process is called type conversion. A type conversion occurs only if the underlying data source supports it. For example, the concatenation operator ( || ) operates on character data types. For a database that supports data types INTEGER and CHAR, in a concatenation of the character string “First” and the numeric integer 1, the INTEGER data type for the operand 1 is converted to a CHAR data type before the concatenation takes place.

When an operand data type is converted within the same general data type, the operand data type is promoted. Operands with a data type of SMALLINT and TINYINT are promoted to INTEGER, and operands of type REAL are promoted to DOUBLE. Type promotion is performed for all operations on SMALLINT, TINYINT, and REAL, including arguments for method and function expressions.

Numeric and character data types are coercible. The BINARY, VARBINARY, and the date/time data types DATE, TIME, and TIMESTAMP are non-coercible and can be converted to only one of the character data types by using the PUT function.

When FedSQL evaluates an expression, if the data types of the operands match exactly, no type conversion or promotion is necessary and the expression is resolved. Otherwise, each operand must go through a standard numeric conversion or a standard character conversion, depending on the operator.

The results of a numeric or character expression are based on a data type precedence. If both operands have different types within the same general data type, the data type of the expression is that of the operand with the higher precedence, where 1 is the highest precedence. For example, for numeric data types, a data type of DOUBLE has the highest precedence. If an expression has an operand of type INTEGER and an operand of type DOUBLE, the data type of the expression is DOUBLE. A list of precedences can be found in the topics that follow, if applicable, for the different types of expressions.

In unary expressions, such as ++1 or -444, the standard numeric conversion is applied to the operand. The following table shows the data type for unary expressions:

In logical expressions, such as (a <> start) OR (f = finish), the standard numeric conversion is applied to each operand. The following table shows the precedence that is used to determine the data type of the expression, where 1 is the highest precedence and 3 is the lowest. The data type of the expression is the data type of the operand that has the higher precedence.

In arithmetic expressions, such as a<>b or a + (b * c), the standard numeric conversion is applied to each operand.

The following table shows the precedence that is used to determine the data type of arithmetic expressions for the addition, subtraction, multiplication, and division operators, where 1 is the highest precedence and 3 is the lowest. The data type of the expression is the data type of the operand that has the higher precedence.

The following table shows the data type for arithmetic expressions that use the power operator:

In relational expressions, such as x <= y or i > 4, the standard conversion that is applied depends on the operand data types. The data type of the expression is always BOOLEAN, as shown in the following tables.

In concatenation expressions, such as a || b or x !! y, the standard character conversion is applied to each operand. The following table shows the precedence used to determine the data type of the expression, where 1 is the highest precedence and 2 is the lowest. The data type of the expression is the data type of the operand that has the higher precedence.