Table of Contents
Every HTSQL expression has an associated type. The type defines the set of valid values produced by the expression and permitted operations over the expression. The type also indicates how returned values are formatted in the output.
Since HTSQL wraps an SQL database, HTSQL data types are related to SQL data types. Although HTSQL does not expose SQL data types directly to the user, each SQL data type corresponds to some HTSQL data type and vice versa.
In this section we describe what data types HTSQL supports, how HTSQL types are mapped to SQL types, the format of input literals for each data type, etc.
HTSQL demands that every expression has an associated type. For example, in the query:
| 2+2=4 | count(school) | date(‘2010-04-15’)-6813 |
|---|---|---|
| true | 9 | 1991-08-20 |
the expressions 2+2=4, count(school), date('2010-04-15')-6813 have the types boolean, integer and date respectively. These are regular types.
The following table lists the default set of supported regular data types in HTSQL; more data types could be added by HTSQL extensions.
| Type | Description | Example Input |
|---|---|---|
| boolean | logical data type, with two values: TRUE and FALSE | true() |
| false() | ||
| integer | binary integer type | 4096 |
| decimal | arbitrary-precision exact numeric type | 124.49 |
| float | IEEE 754 floating-point inexact numeric type | 271828e-5 |
| string | text data type | string('HTSQL') |
| enum | enumeration data type, with predefined set of valid string values | |
| date | calendar date | date('2010-04-15') |
| time | time of day | time('20:13:04.5') |
| datetime | date and time combined | datetime('2010-04-15 20:13:04.5') |
| opaque | unrecognized data type |
Some HTSQL expressions do not produce a proper value and therefore cannot be assigned a regular data type. In this case, the expression is assigned one of the special data types: record, untyped or void.
Record entities are assigned the record type. This type is special since values of this type are never displayed directly and it has no corresponding SQL data type.
Quoted HTSQL literals have no intrinsic data type; their actual type is determined from the context. Until it is determined, HTSQL translator assign them a temporary untyped type.
Some expressions, such as assignments, produce no values and therefore have no meaningful data type. In this case, the assigned type is void.
The following table lists supported special data types.
| Type | Description | Example Input |
|---|---|---|
| record | type of record entities | school |
| untyped | initial type of quoted literals | 'HTSQL' |
| void | type without any valid values |
A literal expression is an atomic expression that represents a fixed value. HTSQL supports two types of literals: numeric (or unquoted) and quoted.
An unquoted literal is a number written in one of the following forms:
| 60 | 2.125 | 271828e-5 |
|---|---|---|
| 60 | 2.125 | 2.71828 |
Literals in these forms are assigned integer, decimal and float types respectively.
A quoted literal is an arbitrary string value enclosed in single quotes.
| ‘’ | ‘HTSQL’ | ‘O’‘Reilly’ |
|---|---|---|
| HTSQL | O’Reilly |
In this example, three literal expressions represent an empty string, HTSQL and O’Reilly respectively. Note that to represent a single quote in the value, we must duplicate it.
As opposed to numeric literals, quoted literals have no intrinsic type, their type is determined from the context. Specifically, the type of a quoted literal is inferred from the innermost expression that contains the literal. Until the actual data type of a quoted literal is determined, the literal is assigned an untyped type.
Consider a query:
Here, a quoted literal '4' is a right operand of an equality expression, and its left counterpart 2+2 has the type integer. Therefore, HTSQL processor is able to infer integer for the literal '4'.
There is no generic rule how to determine the type of a quoted literal; every operator and function have different rules how to treat untyped values. However the content of the literal is never examined when determining its data type. It is possible to explicitly specify the type of an unquoted literal by applying a cast function.
| string(‘2010-04-15’) | date(‘2010-04-15’) |
|---|---|
| 2010-04-15 | 2010-04-15 |
Here, the same quoted literal is converted to string and date data types respectively. Each data type has a set of quoted literals it accepts; it is an error when the quoted literal does not obey the format expected by a particular type.
invalid integer literal: expected an integer in a decimal format; got 'HTSQL'
While translating:
/{integer('HTSQL')}
^^^^^^^^^^^^^^^^
Note the error generated because 'HTSQL' is not a valid format for an integer literal.
Expressions of one type could be explicitly converted to another type using a cast function. A cast function is a regular function with one argument; the name of the function coincides with the name of the target type.
Not every conversion is permitted; for instance, an integer value could be converted to a string, but not to a date:
| 60 |
Implicit type conversion is called coercion. In an arithmetic formulas and other expressions that require homogeneous arguments, when the operands are of different types, values of less generic types are converted to the most generic type. The order of conversion is as follows:
For instance, if an arithmetic operation has an integer and a decimal operands, the integer operand is converted to decimal.
Type boolean is a logical data type with two values: TRUE and FALSE.
| boolean(‘true’) | boolean(‘false’) |
|---|---|
| true | false |
| true() | false() |
|---|---|
| true | false |
The following table maps the boolean type to respective native data types.
| Backend | Native types |
|---|---|
| sqlite | BOOL, BOOLEAN or any type containing BOOL in its name |
| pgsql | BOOLEAN |
| mysql | BOOL aka BOOLEAN aka TINYINT(1) |
| oracle | NUMBER(1) CHECK (_ IN (0, 1)) |
| mssql | BIT |
HTSQL supports three numeric types: integer, decimal and float.
The integer type is a type of binary integer values of a finite range. Typically, a value of an integer type takes 16, 32 or 64 bits.
The decimal type is an arbitrary precision exact numeric type. A value of a decimal type is represented as a sequence of decimal digits.
The float type is an IEEE 754 floating-point inexact numeric type.
Numeric literals are assigned to one of these types, depending on the literal notation:
| 60 | 2.125 | 271828e-5 |
|---|---|---|
| 60 | 2.125 | 2.71828 |
The following table maps integer to respective native data types:
| Backend | Native types |
|---|---|
| sqlite | INT, INTEGER or any type containing INT in its name |
| pgsql | SMALLINT, INTEGER, BIGINT |
| mysql | TINYINT except TINYINT(1), SMALLINT, and others |
| oracle | INTEGER aka NUMBER(38) |
| mssql | SMALLINT, INT BIGINT |
Note: Oracle does not have a native binary integer type, so it is emulated as a decimal type NUMBER(38).
The following table maps decimal to native data types:
| Backend | Native types |
|---|---|
| sqlite | None |
| pgsql | NUMERIC |
| mysql | DECIMAL |
| oracle | NUMBER except for NUMBER(38) |
| mssql | DECIMAL, NUMERIC |
Note: SQLite does not support arbitrary-precision exact numbers, any values of decimal types are cast to float.
The following table maps float to native data types:
| Backend | Native types |
|---|---|
| sqlite | REAL, FLOAT, DOUBLE or any type containing REAL, FLOA, DOUB in its name |
| pgsql | REAL, DOUBLE PRECISION |
| mysql | FLOAT, DOUBLE |
| oracle | BINARY_FLOAT, BINARY_DOUBLE |
| mssql | FLOAT, REAL |
The string data type represents all varieties of SQL character types. A value of a string type is a finite sequence of characters.
| Backend | Native types |
|---|---|
| sqlite | name contains CHAR, CLOB or TEXT |
| pgsql | CHAR, VARCHAR, TEXT |
| mysql | CHAR, VARCHAR TINYTEXT, TEXT, etc |
| oracle | CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, NCLOB |
| mssql | CHAR, NCHAR VARCHAR, NVARCHAR |
The enum type represents a finite, ordered set of fixed values. enum is a family of types, each element of the family is associated with the respective set of values.
| Backend | Native types |
|---|---|
| sqlite | None |
| pgsql | ENUM |
| mysql | ENUM |
| oracle | None |
| mssql | None |
Note: only PostgreSQL and MySQL support enum data type.
HTSQL presents three data type to express date and time values: date, time and datetime. The values of date type are dates, the values of time type represent time of a day, datetime is a combination of date and time.
Literal values of date type must have the form YYYY-MM-DD. Literal values of time type should have the form: hh:mm:ss.sss. Here, the seconds component is optional and could be omitted. Finally, a literal value of datetime type is a combination of date and time separated by a whitespace or character T.
| date(‘2010-04-15’) | time(‘20:13:04.5’) | datetime(‘2010-04-15 20:13:04.5’) |
|---|---|---|
| 2010-04-15 | 20:13:04.500000 | 2010-04-15 20:13:04.500000 |
The following table maps date to native data types:
| Backend | Native types |
|---|---|
| sqlite | DATE |
| pgsql | DATE |
| mysql | DATE |
| oracle | None |
| mssql | None |
Notes:
The following table maps time to native date types:
| Backend | Native types |
|---|---|
| sqlite | None |
| pgsql | TIME |
| mysql | TIME |
| oracle | None |
| mssql | None |
Notes:
The following table maps datetime to native types:
| Backend | Native types |
|---|---|
| sqlite | DATETIME, TIMESTAMP |
| pgsql | TIMESTAMP |
| mysql | DATETIME, TIMESTAMP |
| oracle | DATE, TIMESTAMP |
| mssql | DATETIME, SMALLDATETIME |
Note: SQLite does not have a native datetime type; a datetime value is represented as a string of the form 'YYYY-MM-DD hh:mm:ss'.