Type promotion can occur when the following operators or functions are used:

Operators

---

UNION, INTERSECT, or EXCEPT in a SELECT statement

When one of these operators is used, the type, collation, length, and scale of the column in the result set is determined by the type promotion rules outlined below using the corresponding columns of each SELECT statement involved as the inputs to the type promotion process.

Functions

---

CASE
IF
IFNULL
NULLIF
COALESCE


When one of these functions is used, the type, collation, length, and scale of their result values are determined by the type promotion rules outlined below using their multiple input arguments as the inputs to the type promotion process.

Rules

---

The rules for type promotion are as follows:

The precedence for CHAR, VARCHAR, and CLOB types is:

   CLOB (highest)
   VARCHAR
   CHAR

If the resulting type is a CHAR or VARCHAR, then the resulting length is the greatest of all of the input lengths.

The resulting collation is determined by the input that was selected according to the type precedence above.

The precedence for BYTE, VARBYTE, and BLOB types is:

   BLOB (highest)
   VARBYTE
   BYTE

If the resulting type is a BYTE or VARBYTE, then the resulting length is the greatest of all of the input lengths.

The precedence for SMALLINT, INTEGER, LARGEINT, DECIMAL, and FLOAT types is:

   FLOAT (highest)
   DECIMAL
   LARGEINT
   INTEGER
   SMALLINT

If the resulting type is a DECIMAL, then the resulting scale is the greatest of all of the input scales.

SQL 2003 Standard Deviations

---

The following areas are where ElevateDB deviates from the SQL 2003 standard: