Data Type Conversion

The Type System is a set of rules and transformations that NuoDB applies to data types during query compilation. The goals of the process are:

1) To detect and correct mismatches in data types as early as possible in the query execution process

2) To prevent bugs in user applications caused by incorrect use of the query language.

All references to the term "type" used in this section refer to "data type".

Data type conversions are necessary because many operators and functions expect each of their arguments to be of a certain data type which allows for proper reconciliation of scale, precision, length, collation, etc. during comparisons and computations. In addition, data type conversions are necessary to choose a single data type for the results of conditional expressions and the items in the SELECT list UNION or UNION ALL.

Implicit vs. Explicit Data Type Conversion

NuoDB avoids implicit data conversions (coercions) that could lead to loss of precision and issues an error message in such cases. The user can use the CAST operator to allow a conversion that could lead to loss of precision or for overriding the implicit conversion behavior. Refer to the Data Type Conversion Table in this section.

Implications of Data Type Conversion

A data type conversion may change the semantics of comparison operations. Consider the following examples:

'00001' = 1 is true if the string '00001' is converted to a numeric value 1 but false if 1 is converted to the string '1';
'100' < 2 is false if the string '100' is converted to a numeric value 100 but true if 2 is converted to the string '2'.

Due to the differences in comparison semantics, indexes cannot be used when data conversion has been applied on a column in a comparison operator. Additionally, there may be overhead when converting long character string or large objects (CLOB or BLOB).

Sources for type information

NuoDB uses two sources to obtain type information: table column definitions and query constants.

NuoDB implements four types of constants: binary, boolean, number, and string.

Boolean Constants

NuoDB creates a boolean constant for the special words TRUE and FALSE. For more details, please read SQL-BooleanTypes.

String Constants

NuoDB creates constants of type STRING for the quoted literals in the query string. NuoDB does not provide ways to create VARCHAR or CHAR constants.

For more information on string types in NuoDB, see SQLString and Character Types

Number Constants

There are 3 formats interpreted as number constants in NuoDB:

• integer types (e.g. 1230)

• NUMERIC numbers (e.g. 12.30)

• scientific notation (e.g. 123e1)

NuoDB selects the smallest type in which the value can be stored. For example, the constant 100 will have a type SMALLINT while the constant 40001 will be of type INTEGER. For more information on numbers, see SQL Numeric Types.

Constants using the scientific notation will be integer types if they have a zero fractional part (e.g. 1e1 is interpreted as a SMALLINT). They will have a NUMERIC type if the fractional part is non-zero (e.g. 1e-1 is a NUMERIC(2, 1))

Binary Constants

NuoDB creates a constant with type BINARY for hexadecimal constants. For more information on binary constants, please read SQL Binary Types

Table columns

The type for table columns is defined when the column is created and can be altered with ALTER TABLE statements.

Functions and Operators

For the Type System, NuoDB implements two categories of functions and operators:

• Signature operators and functions (e.g. SUBSTRING, YEAR, etc. - see SQL Functions and Operations for a comprehensive list)

• Common Type operators and functions (e.g. COALESCE, IFNULL, etc. - see SQL Functions and Operations for a comprehensive list).

Signature Functions and Operators

Each signature function or operators has a list of signatures depending on the number of arguments it accepts and their type. As a separate step during query compilation, NuoDB deduces which signature should be applied based on the arguments of the operator or function. If there isn't an exact match, NuoDB uses type inference to deduce the closest matching signature and then applies implicit conversions (coercions) on the arguments to convert them to the desired type. If a coercion is not allowed between the type of the arguments and the type required by the signature, NuoDB returns an error. The following table shows the supported implicit conversions.

Data Type Conversion Table

FROM/TO String Char VarChar Clob Enum Boolean SmallInt Integer BigInt Float Double Numeric Number Date Timestamp Time Bytes Binary Varbinary Blob
String Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y
Char Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y
VarChar Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y
Clob Y Y Y Y Y N N N N N N N N N N N Y Y Y Y
Enum Y Y Y Y N Y Y Y Y Y Y Y Y N N N Y Y Y Y
Boolean Y Y Y N Y Y Y Y Y Y Y Y Y N N N Y N N N
SmallInt Y Y Y N Y Y Y Y Y Y Y Y Y N N N Y N N

N

Integer Y Y Y N Y Y Y Y Y Y Y Y Y N N N Y N N

N

BigInt Y Y Y N Y Y Y Y Y Y Y Y Y N N N Y N N

N

Float Y Y Y N Y Y Y Y Y Y Y Y Y N N N N N N

N

Double Y Y Y N Y Y Y Y Y Y Y Y Y N N N N N N

N

Numeric Y Y Y N Y Y Y Y Y Y Y Y Y N N N Y N N

N

Number Y Y Y N Y Y Y Y Y Y Y Y Y N N N Y N N

N

Date Y Y Y N N N N N N N N N N Y Y Y N N N

N

Timestamp Y Y Y N N N N N N N N N N Y Y Y N N N

N

Time Y Y Y N N N N N N N N N N Y Y Y N N N

N

Bytes Y Y Y Y Y Y Y Y Y N N Y Y N N N Y Y Y Y
Binary Y Y Y Y Y N N N N N N N N N N N Y Y Y Y
Varbinary Y Y Y Y Y N N N N N N N N N N N Y Y Y Y
Blob Y Y Y Y Y N N N N N N N N N N N Y Y Y Y

 

Example

The SUBSTRING function has the following two signatures:

(String, Integer) -> String
(String, Integer, Integer) -> String

For the following query:

select substring(123456, 1.0, '2') s1, substring('aaaa', '3') s2 from dual;

The Type System will use the signatures in the following ways:

• For s1, the first signature cannot be used because the number of arguments does not match. The only other option is the second signature and therefore each argument will be converted to the type it specifies.

• Similarly, for s2, the second signature cannot be applied due to the number of arguments and the only option left is to use the first signature.

After applying the signatures, the query was rewritten to:

select
     substring(coerce(123456 as string), coerce (1.0 as integer), coerce('2' as integer)) s1,
     substring('aaaa', coerce('3' as integer)) s2
from dual;

Common type

Operators  and functions that are handled using the Common Type approach operate on arguments of the same type. During compile time, NuoDB deduces the common type of the arguments using the following common type transition table:

Type

Transitions

String

String → Boolean → Bytes → Numeric → Double → Time → Timestamp

Char

Char → VarChar → String → Boolean → Bytes → Numeric → Double → Time → Timestamp

VarChar

VarChar → String → Boolean → Bytes → Numeric → Double → Time → Timestamp

SmallInt

SmallInt → Integer → BigInt → Numeric → Float → Double

Integer

Integer → BigInt → Numeric → Float → Double

BigInt

BigInt → Numeric → Double

Float

Float → Double

Double

Double

Date

Date → Timestamp

Timestamp

Timestamp

Time

Time → Timestamp

Blob

Blob → Bytes

Clob

Clob → String → Bytes

Numeric

Numeric → Double

Number

Number → Numeric → Double

Bytes

Bytes → BigInt → Numeric

Binary

Binary → VarBinary → Bytes

VarBinary

VarBinary → Bytes

Boolean

Boolean → SmallInt → Integer → BigInt → Numeric → Float → Double

The common type for any two types is computed by iterating over their respective transitions types and stopping at the first transition type that is common to both. When applying the common type algorithm on multiple arguments, NuoDB first evaluates the type of each of the arguments. Then, NuoDB computes the common type across all arguments. The operation proceeds from left to right by computing the common type between the current argument and the next argument until no more arguments remain. Note that the rule is not associative in this case; the resulting type depends on the order in which the arguments appear in the operator or function.

Example

COALESCE takes a variable number of arguments and returns the first one (from left to right) which is not null. To determine the return type of this function, NuoDB computes the common type of all the arguments, implicitly converts all of them to the common type and sets that type as the return type of the function:

select coalesce('1', 1, cast(2 as double)) from dual;

In this case, coalesce is called for types string, SmallInt and double. The common type between string and smallInt is numeric (the first match between them in the transition table). The common type between numeric and double is double. Thus, coalesce function will return double and all the arguments will be converted to a double. NuoDB internally rewrites the query to:

select coalesce(coerce('1' as double), coerce (1 as double), cast(2 as double)) from dual;

The COERCE operator

The COERCE operator performs the implicit conversions decided by the Type System.

The presence of this operator in a query plan indicates that the initial query contains type discrepancies and such discrepancies were corrected automatically by the NuoDB query compiler.

Functionally, the COERCE operator is similar to the CAST operator, but COERCE is not available as a separate call (it is only used internally). While the CAST operator performs truncation or rounding in cases when the value to be converted does not fit in the target type, the COERCE operator, throws an error instead if the conversion would require truncation or rounding.

To understand the difference between CAST and COERCE consider the following example:

SQL>create table t(col decimal(4, 2));
SQL>insert into t(col) values(2.1);
SQL>insert into t(col) values(2.6);
SQL>explain select substring('nuodb', col) from t;
Select 
  List 
   Alias:SUBSTR 
     Substr 
       "nuodb" 
          Coerce T.COL 
            as integer
Table Scan T  [cost: 33.00, rows: 11.00]

The second argument is wrapped in a COERCE operator because the type of this argument is decimal(4, 2) while SUBSTR expect an integer. If we were to execute this query, we would get an error because the value 2.1 cannot be converted to an integer without the loss of the decimal part:

SQL>select substring('nuodb', col) from t;
Numeric overflow converting integer of scale 1 to integer of scale 0

In this case, in order to achieve the desired behavior of rounding the value from the column COL to an integer, users must explicitly cast the value as an integer:

SQL>explain select substring('nuodb', cast(col as integer)) from t;
        Select
          List
            Alias:SUBSTR
              Substr
               "nuodb"
                Cast
                  T.COL
                   as integer
          Table Scan T  [cost: 33.00, rows: 11.00]

SQL>select col, substring('nuodb', cast(col as integer)) from t;
        COL  SUBSTR
        ---- -------
        2.10  uodb
        2.60  odb

Note that in this case the query plan does not contain a COERCE operator (since CAST already returns an integer type), and the decimal column is rounded to an integer.

Another important difference between the COERCE and CAST operators is that the COERCE operator can be removed automatically as NuoDB replaces expressions containing constants with their results (the so called "constant folding" optimization), while CAST is never optimized this way. This means that coercions might have been performed on constants at compile time and not be visible in the query plan. Using SUBSTRING as an example again:

SQL>explain (object_types on) select 1 const, substring(string_col, 1) from t1 ;
         Select
           List
            Alias:CONST
              "1" (smallint)
            Alias:SUBSTR
              Substr
                Field T1.STRING_COL
                "1" (integer)
Table Scan T1  [cost: 117.00, rows: 13.00]

The output shows the constant 1 as being of type SmallInt when used separately, but is of type integer when used as an argument for SUBSTRING. This occurs because the substring expression was rewritten to substring(string_col, coerce(1 as integer)) and then the second argument was optimized to the integer1.

Arithmetic expressions

The signature for arithmetic expressions (+, -, %, * and /) is defined as (Number, Number) -> Number where number can be any of the NuoDB number types.

The signature is applied in two steps. In the first step, each argument is coerced to one of the number types based on the common type transition table. In the second step, a common type is computed based on the updated arguments and this type will be the return type of the expression. If the resulting common type is a SmallInt or Integer, NuoDB upgrades it to the next larger type to allow room for overflowing. If the common type is SmallInt, the return type will be Integer; and if the common type is Integer, the result type will be BigInteger.

Example

For the query:

select '1.1' + 1 from dual;

the first step decides type Numeric for the string constant '1.1', because this is the only available transition from string to a number type. In step two, the return type of the expression is determined to be numeric based on the common type between numeric and SmallInt. The query is rewritten internally as:

select coerce('1.1' as numeric) + 1 from dual;

Precision and scale for operations on numeric arguments

When applied to numeric operations, NuoDB computes the precision and scale using the algorithm from the table below ("lhs" indicates the left argument, "rhs" the right).:

Expression Precision Scale
lhs + rhs 1 + max(lhs.precision, rhs.precision) max(lhs.scale, rhs.scale)

lhs - rhs

1 + max(lhs.precision, rhs.precision) max(lhs.scale, rhs.scale)
lhs % rhs min(lhs.precision - lhs.scale, rhs.precision - lhs.scale) + result.scale max(lhs.scale, rhs.scale)
lhs * rhs lhs.precision + rhs.precision lhs.scale + rhs.scale
lhs / rhs lhs.precision - lhs.scale + rhs.scale + result.scale max(6, lhs.scale + rhs.precision + 1)

Note that the same algorithm is also applied for the function variants of these expressions (such as DIV, MOD, etc.).

Query parameters

The SQL standard does not provide a way to specify the type of a query parameter at compile time. NuoDB considers query parameters to be of type String and allows the general algorithm to decide the type they should be coerced to. This approach allows for the correct result to be obtained in a wide range of situations:

SQL>select 1 / 1 as consts, 1 / ? query_parameters from dual;
Enter value: 1
			
CONSTS  query_parameters
------- ----------
1        1.000000

Because the type of the variable is string, the result of the second expression is Numeric. In additional, a default scale and precision are computed for it. The CAST operator can be used to overcome this limitation.

Comparison operators

Comparison operators also use the common type signature method. During query compilation, a common type is decided between the arguments of the comparison. Coercions are applied only if the operator would not otherwise be able to compare the two arguments. This means, for example, no coercion will be performed when comparing an integer toa decimal(10, 3). However, when comparing an integer with a string, the string will be coerced to a numeric type.

The query optimizer cannot correctly decide when an index scan can be performed if the column from the index is wrapped in a coerce operator. Therefore, NuoDB avoids unnecessary coercions in such cases.

Note: With type checking enabled, it is not possible to use an index when the column data type and comparison expression are of different data types. When comparing numbers of different types, NuoDB will use an index. An index will not be used in cases such as comparing a string column with a numeric expression. Type casting the expression value to agree with the column data type will utilize an available index.

The IN operator

The IN operator applies the comparison common type algorithm in two steps. First, it determines the common type between the elements in the IN list and then the common type between the left-hand argument and the common type of the IN list. The common type of the arguments on the right-hand side is computed from left to right by computing the common type between the currently computed common type and the next argument.

Conditional expressions

The conditional expressions (CASE, COALESCE, LEAST, GREATEST, IFNULL) are defined on a list of arguments (and search conditions, in the case of CASE) and return one of these arguments. The NuoDB Type System must make sure that all of the arguments have the same type (and precision and scale in case of decimal or numeric arguments) so that the type of the value computed during query evaluation is consistent across all the rows of the returned result.

Example:

SQL>create table t1(s smallint, i int, d1 decimal (10, 4), d2 decimal(14, 3));
SQL>insert into t1 values(1, 2, 3, 4);
SQL>insert into t1 values(2, 1, 3, 4);
SQL>insert into t1 values(2, 3, 1, 4);
SQL>insert into t1 values(2, 3, 4, 1);
SQL>explain select least(s, i, d1, d2) from t1;
Select
  List
    Alias:LEAST
      Least
        Coerce
          T1.S
            as numeric(15,4)
        Coerce
          T1.I
            as numeric(15,4)
        Coerce
          T1.D1
            as numeric(15,4)
        Coerce
          T1.D2
            as numeric(15,4)
Table Scan T1  [cost: 168.00, rows: 14.00]

In the example above, arguments of the least expression are all coerced to the same precision and scale in order to force a consistent result across multiple rows, as shown below:

SQL>select s, i, d1, d2, least(s, i, d1, d2) from t1;
S  I    D1    D2   LEAST
-- -- ------ ----- ------
1  2  3.0000 4.000 1.0000
2  1  3.0000 4.000 1.0000
2  3  1.0000 4.000 1.0000
2  3  4.0000 1.000 1.0000

CASE and CASE search

For both versions of CASE, NuoDB enforces the same type, precision, and scale for all the branches of the CASE expression. Considering the table t1 above, a case expression returning either the column i or the column d1 would have the following query plan:

SQL>explain select case when i = 1 then i when d1 = 1 then d1 end from t1;
Select
  List
   Case
     Eql
      T1.I
      "1"
     Coerce
       T1.I
       as numeric(13,4)
     Eql
       T1.D1
       "1"
     Coerce
       T1.D1
       as numeric(13,4)
Table Scan T1  [cost: 168.00, rows: 14.00]

and return the following result:

SQL>select i, d1, case when i = 1 then i when d1 = 1 then d1 end from t1;
I    D1
-- ------ ------
2  3.0000 <null>
1  3.0000 1.0000
3  1.0000 1.0000
3  4.0000 <null>

The type of the search condition is determined separately from the common type of the branches that return values. For the more general version of CASE, correctness is enforced on each condition in isolation:

SQL>explain select case when s < 2 then 2 when s < '3' then 3 end from t1;
Select
  List
    Case
      Lss
       Coerce
         T1.S
         as numeric
       "2"
      "2"
      Lss
        T1.S
       "3"
      "3"
Table Scan T1  [cost: 30.00, rows: 10.00]

In the first condition, s and the constant “2” are compared as numbers. In the second condition, s and the constant “3” are compared as strings.

For the CASE SEARCH variant, a common type is enforced on the first argument and on all the returned values:

SQL>explain select case s when 2 then 2 when '3' then 3 end from t1;
Select
  List
    CaseSearch
      Coerce
        T1.S
        as numeric
        "2"
        "2"
        "3"
        "3"
Table Scan T1  [cost: 30.00, rows: 10.00]

In the example above, all comparisons are performed on numbers.

UNION

NuoDB uses a common type to decide the type, precision, and scale of each column in the result of a query with UNION branches and coerces the columns in each branch to the common type. The common type is computed top down through the branches and is not commutative.

Example:

SQL>create table t1(i int, d date);
SQL>create table t2(dec decimal(7, 2), tm timestamp);
SQL>select i, d from t1 union select dec, tm from t2;
SQL>explain select i, d from t1 union select dec, tm from t2;
Select
  *** union fields ***
    Union
      Select
       List
        Alias:I
         Coerce
          T1.I
          as numeric(9,2)
         Alias:D
          Coerce
          T1.D
          as timestamp
       Table Scan T1  [cost: 60.00, rows: 10.00]
     Select
       List
         Alias:DEC
          Coerce
            T2.DEC
            as numeric(9,2)
          T2.TM
       Table Scan T2  [cost: 60.00, rows: 10.00]

In this above example, the types of the returned columns are decimal(9, 2) and timestamp, and coercions have been placed on all the columns which, initially, do not have the correct type. The t2.tm column is not coerced, because it is already a timestamp.