Type Conversions

Type Conversion Definitions

binary data type
refers to the VARBINARY and BINARY data type.
character data type
refers to the CHAR, VARCHAR, NCHAR, and NVARCHAR data types.
coercible data type
a data type that can be converted to multiple data types, not just a character data type.
date/time data type
refers to the DATE, TIME, and TIMESTAMP data types.
non-coercible data type
a data type that can be converted only to a character data type.
numeric data type
refers to the DECIMAL, NUMERIC, DOUBLE (or FLOAT), REAL, BIGINT, INT, SMALLINT, and TINYINT data types.
standard character conversion
if an expression is not one of the character data types, it is converted to a CHAR data type.
standard numeric conversion
if an expression has a coercible, non-numeric data type, it is converted to a DOUBLE data type.

Overview of 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.

Type Conversion for Unary 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:
Data Type Conversion for Unary Expressions
Expression Type
Expression Data Type
Unary plus
same as the operand or DOUBLE for converted operands
Unary minus
same as the operand or DOUBLE for converted operands
Unary not
INTEGER

Type Conversion for Logical 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.
Data Type Conversion for Logical Expressions
Precedence
Data Type of Either Operand
Expression Data Type
1
DOUBLE
DOUBLE
2
BIGINT
BIGINT
3
all other numeric data types
INTEGER

Type Conversion for Arithmetic Expressions

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.
Type Conversion for Addition, Subtraction, Multiplication, and Division Expressions
Precedence
Data Type of Either Operand
Expression Data Type
1
DOUBLE
DOUBLE
2
BIGINT
BIGINT
3
all other numeric data types
INTEGER
The following table shows the data type for arithmetic expressions that use the power operator:
Data Type Conversion for the Min, Max, and Power Operator Expressions
Operator
Operator Data Type
Expression Data Type
**
all numeric data types
DOUBLE

Type Conversion for Relational Expressions

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.
Data Type Conversion for Relational Expressions except IN Expressions
Order of Data Type Resolution
Data Type of Either Operand
Standard Conversion
Expression Data Type
1
any numeric data type
numeric
BOOLEAN
2
CHAR/NCHAR
character
BOOLEAN
3
DATE, TIME, TIMESTAMP
none, data types must match
BOOLEAN
4
all other data types
none, error returned
not applicable
Data Type Conversion for IN Expressions
Operand
Operand Conversion
Expression Data Type
all
standard numeric or standard character
BOOLEAN

Type Conversion for Concatenation Expressions

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.
Data Type Conversion for Concatenation Expressions
Precedence
Data Type of Either Operand
Expression Data Type
1
if either is type NCHAR
NCHAR
2
CHAR
CHAR
Last updated: February 23, 2017