Each value stored in a QDB database (or manipulated by the database engine) has one of the following storage classes:
Any column in a database except an INTEGER PRIMARY KEY may be used to store any type of value. The exception to this rule is described below under “Other Affinity Modes” as strict affinity mode.
All values supplied to QDB, whether as literals embedded in SQL statements or values bound to pre-compiled SQL statements are assigned a storage class before the SQL statement is executed. Under the circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.
Storage classes are initially assigned as follows:
Values specified as literals as part of SQL statements are assigned storage class TEXT if they are enclosed by single or double quotes, INTEGER if the literal is specified as an unquoted number with no decimal point or exponent, REAL if the literal is an unquoted number with a decimal point or exponent, and NULL if the value is a NULL. Literals with storage class BLOB are specified using the X'ABCD' notation.
The storage class of a value that is the result of an SQL scalar operator depends on the outermost operator of the expression.
In QDB, the type of a value is associated with the value itself, not with the column or variable in which the value is stored. (This is sometimes called manifest typing.) All other SQL databases engines that we are aware of use the more restrictive system of static typing where the type is associated with the container, not the value.
In order to maximize compatibility between QDB and other database engines, QDB supports the concept of “type affinity” on columns. The type affinity of a column is the recommended type for data stored in that column. The key here is that the type is recommended, not required. Any column can still store any type of data, in theory. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its affinity.
Each column in an QDB database is assigned one of the following type affinities:
A column with TEXT affinity stores all data using the storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity, it is converted to text form before being stored.
A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it is stored. If the conversion is successful, then the value is stored using the INTEGER or REAL storage class. If the conversion cannot be performed, the value is stored using the TEXT storage class. No attempt is made to convert NULL or blob values.
A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no floating point component (or text value that converts to such) is inserted, it is converted to an integer and stored using the INTEGER storage class.
A column with affinity NONE does not prefer one storage class over another. It makes no attempt to coerce data before it is inserted.
The type affinity of a column is determined by the declared type of the column, according to the following rules:
If you create a table using a CREATE TABLE table AS SELECT... statement, then all columns have no datatype specified, and they are given no affinity.
CREATE TABLE t1( t TEXT, nu NUMERIC, i INTEGER, no BLOB ); -- Storage classes for the following row: -- TEXT, REAL, INTEGER, TEXT INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0'); -- Storage classes for the following row: -- TEXT, REAL, INTEGER, REAL INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);
QDB features the binary comparison operators =, <, <=, >= and !=; IN, an operation to test for set membership; and the ternary comparison operator, BETWEEN.
The results of a comparison depend on the storage classes of the two values being compared, according to the following rules:
QDB may attempt to convert values between the numeric storage classes (INTEGER and REAL) and TEXT before performing a comparison. For binary comparisons, this is done in the cases enumerated below. The term “expression” below refers to any SQL scalar expression or literal other than a column value.
In QDB, the expression a BETWEEN b AND c is equivalent to a >= b AND a <= c, even if this means that different affinities are applied to a in each of the comparisons required to evaluate the expression.
Expressions of the type a IN (SELECT b ....) are handled by the rules enumerated above for binary comparisons (e.g. in a similar manner to a = b). For example, if b is a column value and a is an expression, then the affinity of b is applied to a before any comparisons take place.
QDB treats the expression a IN (x, y, z) as equivalent to a = z OR a = y OR a = z.
CREATE TABLE t1( a TEXT, b NUMERIC, c BLOB ); -- Storage classes for the following row: -- TEXT, REAL, TEXT INSERT INTO t1 VALUES('500', '500', '500'); -- 60 and 40 are converted to '60' and '40' and values are compared as TEXT. SELECT a < 60, a < 40 FROM t1; 1|0 -- Comparisons are numeric. No conversions are required. SELECT b < 60, b < 600 FROM t1; 0|1 -- Both 60 and 600 (storage class NUMERIC) are less than '500' -- (storage class TEXT). SELECT c < 60, c < 600 FROM t1; 0|0
All mathematical operators (which is to say, all operators other than the concatenation operator ||) apply NUMERIC affinity to all operands prior to being carried out. If one or both operands cannot be converted to NUMERIC, then the result of the operation is NULL.
For the concatenation operator, TEXT affinity is applied to both operands. If either operand cannot be converted to TEXT (because it is NULL or a BLOB) then the result of the concatenation is NULL.
When values are sorted by an ORDER BY clause, values with storage class NULL come first, followed by INTEGER and REAL values interspersed in numeric order, followed by TEXT values (usually in memcmp() order) and, finally, BLOB values in memcmp() order. No storage class conversions occur before the sort.
When grouping values with the GROUP BY clause, values with different storage classes are considered distinct, except for INTEGER and REAL values, which are considered equal if they are numerically equal. No affinities are applied to any values as the result of a GROUP BY clause.
The compound SELECT operators UNION, INTERSECT and EXCEPT perform implicit comparisons between values. Before these comparisons are performed, an affinity may be applied to each value. The same affinity, if any, is applied to all values that may be returned in a single column of the compound SELECT result set. The affinity applied is the affinity of the column returned by the left-most component SELECTs that has a column value (and not some other kind of expression) in that position. If for a given compound SELECT column, none of the component SELECTs return a column value, no affinity is applied to the values from that column before they are compared.
The above sections describe the operation of the database engine in normal affinity mode. QDB features two other affinity modes, as follows:
By default, when QDB compares two text values, the result of the comparison is determined using memcmp(), regardless of the encoding of the string. QDB lets you supply arbitrary comparison functions, known as user-defined collation sequences, to be used instead of memcmp(). See the chapter Writing User-Defined Functions for more information.
Aside from the default collation sequence BINARY, implemented using memcmp(), QDB features two extra built-in collation sequences intended for testing purposes, NOCASE and REVERSE:
Each column of each table has a default collation type. If a column requires a collation type other than BINARY, you can define the collation type by specifying a COLLATE clause as part of the CREATE TABLE column definition.
Whenever two text values are compared by QDB, a collation sequence is used to determine the results of the comparison according to the following rules.
For binary comparison operators (=, <, >, ≤, and ≥), if either operand is a column, then the default collation type of the column determines the collation sequence to use for the comparison. If both operands are columns, then the collation type for the left operand determines the collation sequence used. If neither operand is a column, then the BINARY collation sequence is used.
The expression x BETWEEN y and z is equivalent to x ≥ y AND x ≤ z. The expression x IN (SELECT y ...) is handled in the same way as the expression x = y for the purposes of determining the collation sequence to use. The collation sequence used for expressions of the form x IN (y, z ...) is the default collation type of x if x is a column, or BINARY otherwise.
An ORDER BY clause that is part of a SELECT statement may be assigned a collation sequence to be used for the sort operation explicitly. In this case, the explicit collation sequence is always used. Otherwise, if the expression sorted by an ORDER BY clause is a column, then the default collation type of the column is used to determine sort order. If the expression is not a column, then the BINARY collation sequence is used.
The examples below identify the collation sequences that would be used to determine the results of text comparisons that may be performed by various SQL statements. Note that a text comparison may not be required, and no collation sequence used, in the case of numeric, BLOB or NULL values.
CREATE TABLE t1( a, -- default collation type BINARY b COLLATE BINARY, -- default collation type BINARY c COLLATE REVERSE, -- default collation type REVERSE d COLLATE NOCASE -- default collation type NOCASE ); -- Text comparison is performed using the BINARY collation sequence. SELECT (a = b) FROM t1; -- Text comparison is performed using the NOCASE collation sequence. SELECT (a = d) FROM t1; -- Text comparison is performed using the BINARY collation sequence. SELECT (d = a) FROM t1; -- Text comparison is performed using the REVERSE collation sequence. SELECT ('abc' = c) FROM t1; -- Text comparison is performed using the REVERSE collation sequence. SELECT (c = 'abc') FROM t1; -- Grouping is performed using the NOCASE collation sequence -- (i.e. values 'abc' and 'ABC' are placed in the same group). SELECT count(*) GROUP BY d FROM t1; -- Grouping is performed using the BINARY collation sequence. SELECT count(*) GROUP BY (d || '') FROM t1; -- Sorting is performed using the REVERSE collation sequence. SELECT * FROM t1 ORDER BY c; -- Sorting is performed using the BINARY collation sequence. SELECT * FROM t1 ORDER BY (c || ''); -- Sorting is performed using the NOCASE collation sequence. SELECT * FROM t1 ORDER BY c COLLATE NOCASE;