Small. Fast. Reliable.
Choose any three.

SQLite Requirement Matrix Details
datatype3.html

Index Summary Markup Original

R-30470-29835-03618-00817-05721-31518-05673-45416 tcl slt th3 src

Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

th3/req1/datatype3_11.test:12

/* IMP: R-30470-29835 */

# EVIDENCE-OF: R-30470-29835 Any column in an SQLite version 3 database,
# except an INTEGER PRIMARY KEY column, may be used to store a value of
# any storage class.

R-17591-50446-37876-17822-10375-27592-36497-28987 tcl slt th3 src

Boolean values are stored as integers 0 (false) and 1 (true).

th3/req1/datatype3_01.test:19

/* IMP: R-17591-50446 */

# EVIDENCE-OF: R-17591-50446 Boolean values are stored as integers 0
# (false) and 1 (true).

R-48204-18881-64880-60099-58359-33874-53073-00752 tcl slt th3 src

Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

th3/req1/datatype3_01.test:26

/* IMP: R-48204-18881 */

# EVIDENCE-OF: R-48204-18881 Instead, the built-in Date And Time
# Functions of SQLite are capable of storing dates and times as TEXT,
# REAL, or INTEGER values: TEXT as ISO8601 strings ("YYYY-MM-DD
# HH:MM:SS.SSS"). REAL as Julian day numbers, the number of days since
# noon in Greenwich on November 24, 4714 B.C. according to the proleptic
# Gregorian calendar. INTEGER as Unix Time, the number of seconds since
# 1970-01-01 00:00:00 UTC.

R-64962-17428-03573-42931-48456-26794-13581-20203 tcl slt th3 src

Each column in an SQLite 3 database is assigned one of the following type affinities:

TEXT
NUMERIC
INTEGER
REAL
BLOB

th3/req1/datatype3_05.test:24

/* IMP: R-64962-17428 */

# EVIDENCE-OF: R-64962-17428 Each column in an SQLite 3 database is
# assigned one of the following type affinities: TEXT NUMERIC INTEGER
# REAL BLOB

R-21926-12440-51056-61142-44683-61094-56845-29582 tcl slt th3 src

A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB.

th3/req1/datatype3_01.test:49

/* IMP: R-21926-12440 */

# EVIDENCE-OF: R-21926-12440 A column with TEXT affinity stores all data
# using storage classes NULL, TEXT or BLOB.

R-54378-38553-30727-53168-14484-58684-10446-07107 tcl slt th3 src

If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.

th3/req1/datatype3_01.test:52

/* IMP: R-54378-38553 */

# EVIDENCE-OF: R-54378-38553 If numerical data is inserted into a column
# with TEXT affinity it is converted into text form before being stored.

R-64016-22984-52243-31154-04159-15253-33575-11293 tcl slt th3 src

A column with NUMERIC affinity may contain values using all five storage classes.

th3/req1/datatype3_01.test:68

/* IMP: R-64016-22984 */

# EVIDENCE-OF: R-64016-22984 A column with NUMERIC affinity may contain
# values using all five storage classes.

R-12079-51392-32585-64897-55283-05837-03077-12925 tcl slt th3 src

When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively.

th3/req1/datatype3_01.test:81

/* IMP: R-12079-51392 */

# EVIDENCE-OF: R-12079-51392 When text data is inserted into a NUMERIC
# column, the storage class of the text is converted to INTEGER or REAL
# (in order of preference) if the text is a well-formed integer or real
# literal, respectively.

R-27233-06819-06438-56184-33294-63005-30894-40602 tcl slt th3 src

If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL.

th3/req1/datatype3_01.test:142

/* IMP: R-27233-06819 */

# EVIDENCE-OF: R-27233-06819 If the TEXT value is a well-formed integer
# literal that is too large to fit in a 64-bit signed integer, it is
# converted to REAL.

R-25142-32967-13152-03974-64146-30247-48913-62281 tcl slt th3 src

For conversions between TEXT and REAL storage classes, only the first 15 significant decimal digits of the number are preserved.

th3/req1/datatype3_01.test:138

/* IMP: R-25142-32967 */

# EVIDENCE-OF: R-25142-32967 For conversions between TEXT and REAL
# storage classes, only the first 15 significant decimal digits of the
# number are preserved.

R-26894-41325-01569-09628-52380-05795-22160-61664 tcl slt th3 src

If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT.

th3/req1/datatype3_01.test:95

/* IMP: R-26894-41325 */

# EVIDENCE-OF: R-26894-41325 If the TEXT value is not a well-formed
# integer or real literal, then the value is stored as TEXT.

R-18404-60208-00331-08583-24532-43133-17599-55121 tcl slt th3 src

For the purposes of this paragraph, hexadecimal integer literals are not considered well-formed and are stored as TEXT. (This is done for historical compatibility with versions of SQLite prior to version 3.8.6 2014-08-15 where hexadecimal integer literals were first introduced into SQLite.)

th3/req1/datatype3_01.test:108

/* IMP: R-18404-60208 */

# EVIDENCE-OF: R-18404-60208 For the purposes of this paragraph,
# hexadecimal integer literals are not considered well-formed and are
# stored as TEXT. (This is done for historical compatibility with
# versions of SQLite prior to version 3.8.6 2014-08-15 where hexadecimal
# integer literals were first introduced into SQLite.)

R-22849-20349-24730-37218-10911-22554-30363-05607 tcl slt th3 src

If a floating point value that can be represented exactly as an integer is inserted into a column with NUMERIC affinity, the value is converted into an integer.

th3/req1/datatype3_01.test:123

/* IMP: R-22849-20349 */

# EVIDENCE-OF: R-22849-20349 If a floating point value that can be
# represented exactly as an integer is inserted into a column with
# NUMERIC affinity, the value is converted into an integer.

R-11628-62147-05445-61752-17620-56103-14750-56419 tcl slt th3 src

No attempt is made to convert NULL or BLOB values.

th3/req1/datatype3_01.test:166

/* IMP: R-11628-62147 */

# EVIDENCE-OF: R-11628-62147 No attempt is made to convert NULL or BLOB
# values.

R-36476-47203-56638-47160-44339-35126-05623-44628 tcl slt th3 src

A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer.

th3/req1/datatype3_01.test:176

/* IMP: R-36476-47203 */

# EVIDENCE-OF: R-36476-47203 A string might look like a floating-point
# literal with a decimal point and/or exponent notation but as long as
# the value can be expressed as an integer, the NUMERIC affinity will
# convert it into an integer.

R-05192-57965-21317-62763-55347-54447-59069-43394 tcl slt th3 src

Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 300000, not as the floating point value 300000.0.

th3/req1/datatype3_01.test:181

/* IMP: R-05192-57965 */

# EVIDENCE-OF: R-05192-57965 Hence, the string '3.0e+5' is stored in a
# column with NUMERIC affinity as the integer 300000, not as the
# floating point value 300000.0.

R-15334-58407-28961-30248-32119-35017-40136-56612 tcl slt th3 src

A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity.

th3/req1/datatype3_01.test:191

/* IMP: R-15334-58407 */

# EVIDENCE-OF: R-15334-58407 A column that uses INTEGER affinity behaves
# the same as a column with NUMERIC affinity.

R-07327-11967-60382-46381-13455-42235-00328-15671 tcl slt th3 src

The expression "CAST(4.0 AS INT)" returns an integer 4, whereas "CAST(4.0 AS NUMERIC)" leaves the value as a floating-point 4.0.

th3/req1/datatype3_01.test:157

/* IMP: R-07327-11967 */

# EVIDENCE-OF: R-07327-11967 The expression "CAST(4.0 AS INT)" returns
# an integer 4, whereas "CAST(4.0 AS NUMERIC)" leaves the value as a
# floating-point 4.0.

R-18885-42713-55126-22349-03815-15805-20030-42091 tcl slt th3 src

A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation.

th3/req1/datatype3_01.test:240

/* IMP: R-18885-42713 */

# EVIDENCE-OF: R-18885-42713 A column with REAL affinity behaves like a
# column with NUMERIC affinity except that it forces integer values into
# floating point representation.

R-31757-07599-53600-28407-17941-01600-55959-20235 tcl slt th3 src

As an internal optimization, small floating point values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out.

th3/req1/datatype3_12.test:12

/* IMP: R-31757-07599 */

# EVIDENCE-OF: R-31757-07599 As an internal optimization, small floating
# point values with no fractional component and stored in columns with
# REAL affinity are written to disk as integers in order to take up less
# space and are automatically converted back into floating point as the
# value is read out.

R-52422-13996-14748-65406-33477-57912-42976-47996 tcl slt th3 src

This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file.

th3/req1/datatype3_12.test:18

/* IMP: R-52422-13996 */

# EVIDENCE-OF: R-52422-13996 This optimization is completely invisible
# at the SQL level and can only be detected by examining the raw bits of
# the database file.

R-03366-15091-31390-29880-25655-21575-38320-18449 tcl slt th3 src

A column with affinity BLOB does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.

th3/req1/datatype3_01.test:291

/* IMP: R-03366-15091 */

# EVIDENCE-OF: R-03366-15091 A column with affinity BLOB does not prefer
# one storage class over another and no attempt is made to coerce data
# from one storage class into another.

R-27916-61221-17546-26882-05334-30041-14192-36305 tcl slt th3 src

the affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:

th3/req1/datatype3_01.test:424

/* IMP: R-27916-61221 */

# EVIDENCE-OF: R-27916-61221 the affinity of a column is determined by
# the declared type of the column, according to the following rules in
# the order shown:

R-07051-38416-38857-52812-51917-08930-07930-15860 tcl slt th3 src

If the declared type contains the string "INT" then it is assigned INTEGER affinity.

th3/req1/datatype3_01.test:309

/* IMP: R-07051-38416 */

# EVIDENCE-OF: R-07051-38416 If the declared type contains the string
# "INT" then it is assigned INTEGER affinity.

R-00243-07929-08239-44820-61576-27318-53216-12960 tcl slt th3 src

If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity.

th3/req1/datatype3_01.test:323

/* IMP: R-00243-07929 */

# EVIDENCE-OF: R-00243-07929 If the declared type of the column contains
# any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT
# affinity.

R-42648-01192-24711-57227-24478-04310-65154-26465 tcl slt th3 src

Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

th3/req1/datatype3_01.test:348

/* IMP: R-42648-01192 */

# EVIDENCE-OF: R-42648-01192 Notice that the type VARCHAR contains the
# string "CHAR" and is thus assigned TEXT affinity.

R-63063-00748-16445-34795-46686-39617-28312-04661 tcl slt th3 src

If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.

th3/req1/datatype3_01.test:359

/* IMP: R-63063-00748 */

# EVIDENCE-OF: R-63063-00748 If the declared type for a column contains
# the string "BLOB" or if no type is specified then the column has
# affinity BLOB.

R-59153-45869-47799-39794-18343-43894-28184-56568 tcl slt th3 src

If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.

th3/req1/datatype3_01.test:383

/* IMP: R-59153-45869 */

# EVIDENCE-OF: R-59153-45869 If the declared type for a column contains
# any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL
# affinity.

R-41025-56247-28274-26451-36451-44569-58431-11817 tcl slt th3 src

Otherwise, the affinity is NUMERIC.

th3/req1/datatype3_01.test:411

/* IMP: R-41025-56247 */

# EVIDENCE-OF: R-41025-56247 Otherwise, the affinity is NUMERIC.

R-14349-34154-22313-18444-03285-54966-26711-64366 tcl slt th3 src

Note that the order of the rules for determining column affinity is important.

th3/req1/datatype3_01.test:428

/* IMP: R-14349-34154 */

# EVIDENCE-OF: R-14349-34154 Note that the order of the rules for
# determining column affinity is important.

R-23153-04437-57288-59301-62447-22408-58278-59491 tcl slt th3 src

A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.

th3/req1/datatype3_01.test:431

/* IMP: R-23153-04437 */

# EVIDENCE-OF: R-23153-04437 A column whose declared type is "CHARINT"
# will match both rules 1 and 2 but the first rule takes precedence and
# so the column affinity will be INTEGER.

R-18085-46797-38544-57069-39269-06542-00638-19188 tcl slt th3 src

numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored

th3/req1/datatype3_02.test:196

/* IMP: R-18085-46797 */

# EVIDENCE-OF: R-18085-46797 numeric arguments in parentheses that
# following the type name (ex: "VARCHAR(255)") are ignored

R-29946-11104-19867-16845-18883-10746-06540-64125 tcl slt th3 src

Example Typenames From The CREATE TABLE Statement or CAST Expression	Resulting Affinity	Rule Used To Determine Affinity
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER	1
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT	2
BLOB no datatype specified	BLOB	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC	5

th3/req1/datatype3_02.test:11

/* IMP: R-29946-11104 */

# EVIDENCE-OF: R-29946-11104 Example Typenames From TheCREATE TABLE
# Statement or CAST Expression Resulting Affinity Rule Used To Determine
# Affinity INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG
# INT INT2 INT8 INTEGER 1 CHARACTER(20) VARCHAR(255) VARYING
# CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB
# TEXT 2 BLOB no datatype specified BLOB 3 REAL DOUBLE DOUBLE PRECISION
# FLOAT REAL 4 NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME NUMERIC 5

R-38971-13593-63134-41706-45438-37598-45923-53388 tcl slt th3 src

Note that a declared type of "FLOATING POINT" would give INTEGER affinity, not REAL affinity, due to the "INT" at the end of "POINT".

th3/req1/datatype3_02.test:171

/* IMP: R-38971-13593 */

# EVIDENCE-OF: R-38971-13593 Note that a declared type of "FLOATING
# POINT" would give INTEGER affinity, not REAL affinity, due to the
# "INT" at the end of "POINT".

R-30879-62015-53996-61326-18041-63884-46339-47880 tcl slt th3 src

And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

th3/req1/datatype3_02.test:187

/* IMP: R-30879-62015 */

# EVIDENCE-OF: R-30879-62015 And the declared type of "STRING" has an
# affinity of NUMERIC, not TEXT.

R-24564-50022-48872-56770-06110-00167-03462-09571 tcl slt th3 src

The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list, or has the same affinity as the affinity of the result set expression if the operand is a SELECT.

th3/cov1/affinity01.test:237 th3/req1/datatype3_05.test:556

/* IMP: R-24564-50022 */

# EVIDENCE-OF: R-24564-50022 The right-hand operand of an IN or NOT IN
# operator has no affinity if the operand is a list, or has the same
# affinity as the affinity of the result set expression if the operand
# is a SELECT.

R-63244-23082-15887-51901-20973-26143-20960-26958 tcl slt th3 src

When an expression is a simple reference to a column of a real table (not a VIEW or subquery) then the expression has the same affinity as the table column.

th3/req1/datatype3_05.test:29

/* IMP: R-63244-23082 */

# EVIDENCE-OF: R-63244-23082 When an expression is a simple reference to
# a column of a real table (not a VIEW or subquery) then the expression
# has the same affinity as the table column.

R-20728-12013-63789-14843-17871-12981-47663-15474 tcl slt th3 src

Parentheses around the column name are ignored. Hence if X and Y.Z are column names, then (X) and (Y.Z) are also considered column names and have the affinity of the corresponding columns.

th3/req1/datatype3_05.test:113

/* IMP: R-20728-12013 */

# EVIDENCE-OF: R-20728-12013 Parentheses around the column name are
# ignored. Hence if X and Y.Z are column names, then (X) and (Y.Z) are
# also considered column names and have the affinity of the
# corresponding columns.

R-08041-13327-32920-22476-39167-57805-60221-02084 tcl slt th3 src

Any operators applied to column names, including the no-op unary "+" operator, convert the column name into an expression which always has no affinity. Hence even if X and Y.Z are column names, the expressions +X and +Y.Z are not column names and have no affinity.

th3/req1/datatype3_05.test:223

/* IMP: R-08041-13327 */

# EVIDENCE-OF: R-08041-13327 Any operators applied to column names,
# including the no-op unary "+" operator, convert the column name into
# an expression which always has no affinity. Hence even if X and Y.Z
# are column names, the expressions +X and +Y.Z are not column names and
# have no affinity.

R-53367-44371-39189-42145-26716-53190-00022-58526 tcl slt th3 src

An expression of the form "CAST(expr AS type)" has an affinity that is the same as a column with a declared type of "type".

th3/req1/datatype3_05.test:252

/* IMP: R-53367-44371 */

# EVIDENCE-OF: R-53367-44371 An expression of the form "CAST(expr AS
# type)" has an affinity that is the same as a column with a declared
# type of "type".

R-22414-48736-05483-33334-64418-18410-20572-40044 tcl slt th3 src

A COLLATE operator has the same affinity as its left-hand side operand.

th3/req1/datatype3_05.test:168

/* IMP: R-22414-48736 */

# EVIDENCE-OF: R-22414-48736 A COLLATE operator has the same affinity as
# its left-hand side operand.

R-00220-34634-35173-21345-54000-63909-52170-35980 tcl slt th3 src

Otherwise, an expression has no affinity.

th3/req1/datatype3_05.test:256

/* IMP: R-00220-34634 */

# EVIDENCE-OF: R-00220-34634 Otherwise, an expression has no affinity.

R-16625-30785-18660-34070-49109-22249-51329-26220 tcl slt th3 src

CREATE TABLE t1(
    t  TEXT,     -- text affinity by rule 2
    nu NUMERIC,  -- numeric affinity by rule 5
    i  INTEGER,  -- integer affinity by rule 1
    r  REAL,     -- real affinity by rule 4
    no BLOB      -- no affinity by rule 3
);

-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text

-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|real

-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
DELETE FROM t1;
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|integer

-- BLOBs are always stored as BLOBs regardless of column affinity.
DELETE FROM t1;
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
blob|blob|blob|blob|blob

-- NULLs are also unaffected by affinity
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
null|null|null|null|null

th3/req1/datatype3_03.test:11

/* IMP: R-16625-30785 */

# EVIDENCE-OF: R-16625-30785 CREATE TABLE t1( t TEXT, -- text affinity
# by rule 2 nu NUMERIC, -- numeric affinity by rule 5 i INTEGER, --
# integer affinity by rule 1 r REAL, -- real affinity by rule 4 no BLOB
# -- no affinity by rule 3 ); -- Values stored as TEXT, INTEGER,
# INTEGER, REAL, TEXT. INSERT INTO t1 VALUES('500.0', '500.0', '500.0',
# '500.0', '500.0'); SELECT typeof(t), typeof(nu), typeof(i), typeof(r),
# typeof(no) FROM t1; text|integer|integer|real|text -- Values stored as
# TEXT, INTEGER, INTEGER, REAL, REAL. DELETE FROM t1; INSERT INTO t1
# VALUES(500.0, 500.0, 500.0, 500.0, 500.0); SELECT typeof(t),
# typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
# text|integer|integer|real|real -- Values stored as TEXT, INTEGER,
# INTEGER, REAL, INTEGER. DELETE FROM t1; INSERT INTO t1 VALUES(500,
# 500, 500, 500, 500); SELECT typeof(t), typeof(nu), typeof(i),
# typeof(r), typeof(no) FROM t1; text|integer|integer|real|integer --
# BLOBs are always stored as BLOBs regardless of column affinity. DELETE
# FROM t1; INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500',
# x'0500'); SELECT typeof(t), typeof(nu), typeof(i), typeof(r),
# typeof(no) FROM t1; blob|blob|blob|blob|blob -- NULLs are also
# unaffected by affinity DELETE FROM t1; INSERT INTO t1
# VALUES(NULL,NULL,NULL,NULL,NULL); SELECT typeof(t), typeof(nu),
# typeof(i), typeof(r), typeof(no) FROM t1; null|null|null|null|null

R-08551-61977-00536-43869-21824-42581-53338-42827 tcl slt th3 src

A value with storage class NULL is considered less than any other value (including another value with storage class NULL).

th3/req1/datatype3_04.test:12

/* IMP: R-08551-61977 */

# EVIDENCE-OF: R-08551-61977 A value with storage class NULL is
# considered less than any other value (including another value with
# storage class NULL).

R-14505-63184-58138-52966-27681-02252-30754-64236 tcl slt th3 src

An INTEGER or REAL value is less than any TEXT or BLOB value.

th3/req1/datatype3_04.test:30

/* IMP: R-14505-63184 */

# EVIDENCE-OF: R-14505-63184 An INTEGER or REAL value is less than any
# TEXT or BLOB value.

R-29765-45832-49120-26582-31576-30942-45829-65039 tcl slt th3 src

When an INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison is performed.

th3/req1/datatype3_04.test:57

/* IMP: R-29765-45832 */

# EVIDENCE-OF: R-29765-45832 When an INTEGER or REAL is compared to
# another INTEGER or REAL, a numerical comparison is performed.

R-64324-28127-56247-07957-53732-02733-48072-24920 tcl slt th3 src

A TEXT value is less than a BLOB value.

th3/req1/datatype3_04.test:78

/* IMP: R-64324-28127 */

# EVIDENCE-OF: R-64324-28127 A TEXT value is less than a BLOB value.

R-16735-29088-33516-22402-46874-28284-28835-57919 tcl slt th3 src

When two TEXT values are compared an appropriate collating sequence is used to determine the result.

th3/req1/datatype3_04.test:98

/* IMP: R-16735-29088 */

# EVIDENCE-OF: R-16735-29088 When two TEXT values are compared an
# appropriate collating sequence is used to determine the result.

R-13324-20915-18608-14135-49913-18477-24470-37665 tcl slt th3 src

When two BLOB values are compared, the result is determined using memcmp().

th3/req1/datatype3_04.test:115

/* IMP: R-13324-20915 */

# EVIDENCE-OF: R-13324-20915 When two BLOB values are compared, the
# result is determined using memcmp().

R-64839-61120-28231-56530-41272-62247-43805-43738 tcl slt th3 src

SQLite may attempt to convert values between the storage classes INTEGER, REAL, and/or TEXT before performing a comparison.

th3/req1/datatype3_05.test:14

/* IMP: R-64839-61120 */

# EVIDENCE-OF: R-64839-61120 SQLite may attempt to convert values
# between the storage classes INTEGER, REAL, and/or TEXT before
# performing a comparison.

R-26234-58592-26686-64458-63937-04823-26518-58770 tcl slt th3 src

Whether or not any conversions are attempted before the comparison takes place depends on the type affinity of the operands.

th3/req1/datatype3_05.test:18

/* IMP: R-26234-58592 */

# EVIDENCE-OF: R-26234-58592 Whether or not any conversions are
# attempted before the comparison takes place depends on the type
# affinity of the operands.

R-10393-27560-44275-02566-07675-36741-21342-01930 tcl slt th3 src

Affinity is applied to operands of a comparison operator prior to the comparison according to the following rules in the order shown:

th3/req1/datatype3_05.test:345

/* IMP: R-10393-27560 */

# EVIDENCE-OF: R-10393-27560 Affinity is applied to operands of a
# comparison operator prior to the comparison according to the following
# rules in the order shown:

R-10042-17899-51824-29189-33430-37310-31146-42510 tcl slt th3 src

If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand.

th3/cov1/affinity01.test:211 th3/req1/datatype3_05.test:265

/* IMP: R-10042-17899 */

# EVIDENCE-OF: R-10042-17899 If one operand has INTEGER, REAL or NUMERIC
# affinity and the other operand has TEXT or BLOB or no affinity then
# NUMERIC affinity is applied to other operand.

R-29161-32953-36932-45614-26636-25376-16033-00724 tcl slt th3 src

If one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand.

th3/cov1/affinity01.test:231 th3/req1/datatype3_05.test:311

/* IMP: R-29161-32953 */

# EVIDENCE-OF: R-29161-32953 If one operand has TEXT affinity and the
# other has no affinity, then TEXT affinity is applied to the other
# operand.

R-19178-27645-63083-26050-05481-47319-52943-38968 tcl slt th3 src

Otherwise, no affinity is applied and both operands are compared as is.

th3/cov1/affinity01.test:282 th3/req1/datatype3_05.test:335

/* IMP: R-19178-27645 */

# EVIDENCE-OF: R-19178-27645 Otherwise, no affinity is applied and both
# operands are compared as is.

R-46475-65007-45296-37609-03818-24981-55908-33234 tcl slt th3 src

The expression "a BETWEEN b AND c" is treated as two separate binary comparisons "a >= b AND a <= c", even if that means different affinities are applied to 'a' in each of the comparisons.

th3/req1/datatype3_05.test:411

/* IMP: R-46475-65007 */

# EVIDENCE-OF: R-46475-65007 The expression "a BETWEEN b AND c" is
# treated as two separate binary comparisons "a >= b AND a <= c",
# even if that means different affinities are applied to 'a' in each of
# the comparisons.

R-06232-47523-18449-58509-42241-16299-63084-14288 tcl slt th3 src

Datatype conversions in comparisons of the form "x IN (SELECT y ...)" are handled as if the comparison were really "x=y".

th3/req1/datatype3_05.test:469

/* IMP: R-06232-47523 */

# EVIDENCE-OF: R-06232-47523 Datatype conversions in comparisons of the
# form "x IN (SELECT y ...)" are handled as if the comparison were
# really "x=y".

R-64049-08691-55124-50671-44406-19697-22475-30547 tcl slt th3 src

The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR a = +y OR a = +z OR ...".

th3/req1/datatype3_05.test:561

/* IMP: R-64049-08691 */

# EVIDENCE-OF: R-64049-08691 The expression "a IN (x, y, z, ...)" is
# equivalent to "a = +x OR a = +y OR a = +z OR ...".

R-18219-48316-63080-50419-00617-04513-48558-33147 tcl slt th3 src

In other words, the values to the right of the IN operator (the "x", "y", and "z" values in this example) are considered to have no affinity, even if they happen to be column values or CAST expressions.

th3/req1/datatype3_05.test:564

/* IMP: R-18219-48316 */

# EVIDENCE-OF: R-18219-48316 In other words, the values to the right of
# the IN operator (the "x", "y", and "z" values in this example) are
# considered to have no affinity, even if they happen to be column
# values or CAST expressions.

R-65304-57881-32228-09146-30017-59454-39464-18055 tcl slt th3 src

CREATE TABLE t1(
    a TEXT,      -- text affinity
    b NUMERIC,   -- numeric affinity
    c BLOB,      -- no affinity
    d            -- no affinity
);

-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer

-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40,   a < 60,   a < 600 FROM t1;
0|1|1

-- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1

-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right.  Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur.  All
-- values are compared numerically.
SELECT b < 40,   b < 60,   b < 600 FROM t1;
0|0|1

-- Numeric affinity is applied to operands on the right, converting them
-- from text to integers.  Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1

-- No affinity conversions occur.  Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c < 40,   c < 60,   c < 600 FROM t1;
0|0|0

-- No affinity conversions occur.  Values are compared as TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1

-- No affinity conversions occur.  Right-hand side values all have
-- storage class INTEGER which compare numerically with the INTEGER
-- values on the left.
SELECT d < 40,   d < 60,   d < 600 FROM t1;
0|0|1

-- No affinity conversions occur.  INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d < '40', d < '60', d < '600' FROM t1;
1|1|1

th3/req1/datatype3_05.test:588

/* IMP: R-65304-57881 */

# EVIDENCE-OF: R-65304-57881 CREATE TABLE t1( a TEXT, -- text affinity b
# NUMERIC, -- numeric affinity c BLOB, -- no affinity d -- no affinity
# ); -- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER
# respectively INSERT INTO t1 VALUES('500', '500', '500', 500); SELECT
# typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
# text|integer|text|integer -- Because column "a" has text affinity,
# numeric values on the -- right-hand side of the comparisons are
# converted to text before -- the comparison occurs. SELECT a < 40, a
# < 60, a < 600 FROM t1; 0|1|1 -- Text affinity is applied to the
# right-hand operands but since -- they are already TEXT this is a
# no-op; no conversions occur. SELECT a < '40', a < '60', a <
# '600' FROM t1; 0|1|1 -- Column "b" has numeric affinity and so numeric
# affinity is applied -- to the operands on the right. Since the
# operands are already numeric, -- the application of affinity is a
# no-op; no conversions occur. All -- values are compared numerically.
# SELECT b < 40, b < 60, b < 600 FROM t1; 0|0|1 -- Numeric
# affinity is applied to operands on the right, converting them -- from
# text to integers. Then a numeric comparison occurs. SELECT b <
# '40', b < '60', b < '600' FROM t1; 0|0|1 -- No affinity
# conversions occur. Right-hand side values all have -- storage class
# INTEGER which are always less than the TEXT values -- on the left.
# SELECT c < 40, c < 60, c < 600 FROM t1; 0|0|0 -- No affinity
# conversions occur. Values are compared as TEXT. SELECT c < '40', c
# < '60', c < '600' FROM t1; 0|1|1 -- No affinity conversions
# occur. Right-hand side values all have -- storage class INTEGER which
# compare numerically with the INTEGER -- values on the left. SELECT d
# < 40, d < 60, d < 600 FROM t1; 0|0|1 -- No affinity
# conversions occur. INTEGER values on the left are -- always less than
# TEXT values on the right. SELECT d < '40', d < '60', d <
# '600' FROM t1; 1|1|1

R-54958-52840-02592-08757-44641-49324-05794-46061 tcl slt th3 src

All of the results in the example are the same if the comparisons are commuted - if expressions of the form "a<40" are rewritten as "40>a".

th3/req1/datatype3_05.test:659

/* IMP: R-54958-52840 */

# EVIDENCE-OF: R-54958-52840 All of the results in the example are the
# same if the comparisons are commuted - if expressions of the form
# "a<40" are rewritten as "40>a".

R-12720-41494-41537-21978-20786-40223-25082-25767 tcl slt th3 src

Mathematical operators (+, -, *, /, %, <<, >>, &, and |) interpret both operands as if they were numbers. STRING or BLOB operands automatically convert into REAL or INTEGER values.

th3/req1/datatype3_06.test:14

/* IMP: R-12720-41494 */

# EVIDENCE-OF: R-12720-41494 Mathematical operators (+, -, *, /, %,
# <<, >>, &, and |) interpret both operands as if they
# were numbers. STRING or BLOB operands automatically convert into REAL
# or INTEGER values.

R-36787-21388-40333-56236-13841-04160-18258-18988 tcl slt th3 src

If the STRING or BLOB looks like a real number (if it has a decimal point or an exponent) or if the value is outside the range that can be represented as a 64-bit signed integer, then it converts to REAL. Otherwise the operand converts to INTEGER.

th3/req1/datatype3_06.test:255

/* IMP: R-36787-21388 */

# EVIDENCE-OF: R-36787-21388 If the STRING or BLOB looks like a real
# number (if it has a decimal point or an exponent) or if the value is
# outside the range that can be represented as a 64-bit signed integer,
# then it converts to REAL. Otherwise the operand converts to INTEGER.

R-27875-27272-27361-08539-07716-12839-63715-05434 tcl slt th3 src

The implied type conversion of mathematical operands is slightly different from CAST to NUMERIC in that string and BLOB values that look like real numbers but have no fractional part are kept as REAL instead of being converted into INTEGER as they would be for CAST to NUMERIC.

th3/req1/datatype3_06.test:277

/* IMP: R-27875-27272 */

# EVIDENCE-OF: R-27875-27272 The implied type conversion of mathematical
# operands is slightly different from CAST to NUMERIC in that string and
# BLOB values that look like real numbers but have no fractional part
# are kept as REAL instead of being converted into INTEGER as they would
# be for CAST to NUMERIC.

R-56743-17266-53373-09142-21068-04228-42328-23175 tcl slt th3 src

The conversion from STRING or BLOB into REAL or INTEGER is performed even if it is lossy and irreversible.

th3/req1/datatype3_06.test:19

/* IMP: R-56743-17266 */

# EVIDENCE-OF: R-56743-17266 The conversion from STRING or BLOB into
# REAL or INTEGER is performed even if it is lossy and irreversible.

R-18364-25640-43471-65335-34300-00271-36361-11229 tcl slt th3 src

Some mathematical operators (%, <<, >>, &, and |) expect INTEGER operands. For those operators, REAL operands are converted into INTEGER in the same way as a CAST to INTEGER.

th3/req1/datatype3_06.test:307

/* IMP: R-18364-25640 */

# EVIDENCE-OF: R-18364-25640 Some mathematical operators (%, <<,
# >>, &, and |) expect INTEGER operands. For those operators,
# REAL operands are converted into INTEGER in the same way as a CAST to
# INTEGER.

R-24635-37065-50827-53824-24547-23611-48294-22857 tcl slt th3 src

The <<, >>, &, and | operators always return an INTEGER (or NULL) result, but the % operator returns either INTEGER or REAL (or NULL) depending on the type of its operands.

th3/req1/datatype3_06.test:312

/* IMP: R-24635-37065 */

# EVIDENCE-OF: R-24635-37065 The <<, >>, &, and |
# operators always return an INTEGER (or NULL) result, but the %
# operator returns either INTEGER or REAL (or NULL) depending on the
# type of its operands.

R-60116-58305-35895-50546-31025-26877-37972-30944 tcl slt th3 src

A NULL operand on a mathematical operator yields a NULL result.

th3/req1/datatype3_06.test:22

/* IMP: R-60116-58305 */

# EVIDENCE-OF: R-60116-58305 A NULL operand on a mathematical operator
# yields a NULL result.

R-65300-64550-00463-55023-40104-43666-12951-02448 tcl slt th3 src

An operand on a mathematical operator that does not look in any way numeric and is not NULL is converted to 0 or 0.0.

th3/req1/datatype3_06.test:25

/* IMP: R-65300-64550 */

# EVIDENCE-OF: R-65300-64550 An operand on a mathematical operator that
# does not look in any way numeric and is not NULL is converted to 0 or
# 0.0.

R-12314-13901-08300-54236-37161-34051-04965-51279 tcl slt th3 src

Division by zero gives a result of NULL.

th3/req1/datatype3_06.test:291

/* IMP: R-12314-13901 */

# EVIDENCE-OF: R-12314-13901 Division by zero gives a result of NULL.

R-12881-55998-05997-62769-52407-41437-37652-18188 tcl slt th3 src

When query results 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 in collating sequence order, and finally BLOB values in memcmp() order.

th3/req1/datatype3_04.test:132

/* IMP: R-12881-55998 */

# EVIDENCE-OF: R-12881-55998 When query results 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 in collating sequence order, and finally BLOB values in
# memcmp() order.

R-21555-60916-58264-59419-46832-30971-06241-53598 tcl slt th3 src

No storage class conversions occur before the sort.

th3/req1/datatype3_04.test:142

/* IMP: R-21555-60916 */

# EVIDENCE-OF: R-21555-60916 No storage class conversions occur before
# the sort.

R-51848-06142-00211-08018-28076-33620-42817-53867 tcl slt th3 src

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.

th3/req1/datatype3_07.test:11

/* IMP: R-51848-06142 */

# EVIDENCE-OF: R-51848-06142 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.

R-40564-64233-62609-55740-51668-02472-22352-36166 tcl slt th3 src

No affinities are applied to any values as the result of a GROUP by clause.

th3/req1/datatype3_07.test:16

/* IMP: R-40564-64233 */

# EVIDENCE-OF: R-40564-64233 No affinities are applied to any values as
# the result of a GROUP by clause.

R-11730-53816-33732-41455-13175-02323-60121-55502 tcl slt th3 src

The compound SELECT operators UNION, INTERSECT and EXCEPT perform implicit comparisons between values.

th3/req1/datatype3_07.test:34

/* IMP: R-11730-53816 */

# EVIDENCE-OF: R-11730-53816 The compound SELECT operators UNION,
# INTERSECT and EXCEPT perform implicit comparisons between values.

R-14014-59687-26564-37068-45971-48448-09846-38995 tcl slt th3 src

No affinity is applied to comparison operands for the implicit comparisons associated with UNION, INTERSECT, or EXCEPT - the values are compared as is.

th3/req1/datatype3_07.test:61

/* IMP: R-14014-59687 */

# EVIDENCE-OF: R-14014-59687 No affinity is applied to comparison
# operands for the implicit comparisons associated with UNION,
# INTERSECT, or EXCEPT - the values are compared as is.

R-63516-50117-63411-46910-42078-47460-16575-25841 tcl slt th3 src

When SQLite compares two strings, it uses a collating sequence or collating function (two terms for the same thing) to determine which string is greater or if the two strings are equal.

th3/req1/datatype3_09.test:11

/* IMP: R-63516-50117 */

# EVIDENCE-OF: R-63516-50117 When SQLite compares two strings, it uses a
# collating sequence or collating function (two terms for the same
# thing) to determine which string is greater or if the two strings are
# equal.

R-02402-03425-02840-42627-65060-52098-37241-46689 tcl slt th3 src

SQLite has three built-in collating functions: BINARY, NOCASE, and RTRIM.

th3/req1/datatype3_09.test:16

/* IMP: R-02402-03425 */

# EVIDENCE-OF: R-02402-03425 SQLite has three built-in collating
# functions: BINARY, NOCASE, and RTRIM.

R-53528-47027-65277-23622-06185-25684-15860-07330 tcl slt th3 src

BINARY - Compares string data using memcmp(), regardless of text encoding.

th3/req1/datatype3_09.test:38

/* IMP: R-53528-47027 */

# EVIDENCE-OF: R-53528-47027 BINARY - Compares string data using
# memcmp(), regardless of text encoding.

R-35835-22506-43266-17385-20539-20628-59606-19981 tcl slt th3 src

NOCASE - Similar to binary, except that it uses sqlite3_strnicmp() for the comparison. Hence the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed.

th3/req1/datatype3_09.test:84

/* IMP: R-35835-22506 */

# EVIDENCE-OF: R-35835-22506 NOCASE - Similar to binary, except that it
# uses sqlite3_strnicmp() for the comparison. Hence the 26 upper case
# characters of ASCII are folded to their lower case equivalents before
# the comparison is performed.

R-58640-02877-59661-28558-18785-36384-52815-36823 tcl slt th3 src

RTRIM - The same as binary, except that trailing space characters are ignored.

th3/req1/datatype3_09.test:122

/* IMP: R-58640-02877 */

# EVIDENCE-OF: R-58640-02877 RTRIM - The same as binary, except that
# trailing space characters are ignored.

R-14809-12532-29849-37931-55866-36288-34668-62105 tcl slt th3 src

Every column of every table has an associated collating function.

th3/req1/datatype3_09.test:135

/* IMP: R-14809-12532 */

# EVIDENCE-OF: R-14809-12532 Every column of every table has an
# associated collating function.

R-04136-04681-09628-27696-46047-23040-33903-21102 tcl slt th3 src

If no collating function is explicitly defined, then the collating function defaults to BINARY.

th3/req1/datatype3_09.test:142

/* IMP: R-04136-04681 */

# EVIDENCE-OF: R-04136-04681 If no collating function is explicitly
# defined, then the collating function defaults to BINARY.

R-39020-65330-19381-32057-25425-01859-32299-49676 tcl slt th3 src

The COLLATE clause of the column definition is used to define alternative collating functions for a column.

th3/req1/datatype3_09.test:170

/* IMP: R-39020-65330 */

# EVIDENCE-OF: R-39020-65330 The COLLATE clause of the column definition
# is used to define alternative collating functions for a column.

R-54614-40903-43648-00840-19083-45681-47455-60151 tcl slt th3 src

The rules for determining which collating function to use for a binary comparison operator (=, <, >, <=, >=, !=, IS, and IS NOT) are as follows:

th3/req1/datatype3_09.test:334

/* IMP: R-54614-40903 */

# EVIDENCE-OF: R-54614-40903 The rules for determining which collating
# function to use for a binary comparison operator (=, <, >,
# <=, >=, !=, IS, and IS NOT) are as follows:

R-29702-55194-05446-48408-53607-29729-56782-07687 tcl slt th3 src

If either operand has an explicit collating function assignment using the postfix COLLATE operator, then the explicit collating function is used for comparison, with precedence to the collating function of the left operand.

th3/req1/datatype3_09.test:191

/* IMP: R-29702-55194 */

# EVIDENCE-OF: R-29702-55194 If either operand has an explicit collating
# function assignment using the postfix COLLATE operator, then the
# explicit collating function is used for comparison, with precedence to
# the collating function of the left operand.

R-11423-52711-44857-10860-28486-08502-59467-30884 tcl slt th3 src

If either operand is a column, then the collating function of that column is used with precedence to the left operand.

th3/req1/datatype3_09.test:221

/* IMP: R-11423-52711 */

# EVIDENCE-OF: R-11423-52711 If either operand is a column, then the
# collating function of that column is used with precedence to the left
# operand.

R-56101-50212-09292-39432-28945-47695-30385-48702 tcl slt th3 src

For the purposes of the previous sentence, a column name preceded by one or more unary "+" operators and/or CAST operators is still considered a column name.

th3/req1/datatype3_09.test:249

/* IMP: R-56101-50212 */

# EVIDENCE-OF: R-56101-50212 For the purposes of the previous sentence,
# a column name preceded by one or more unary "+" operators and/or CAST
# operators is still considered a column name.

R-51243-03259-14628-62602-22056-51212-17455-10922 tcl slt th3 src

Otherwise, the BINARY collating function is used for comparison.

th3/req1/datatype3_09.test:307

/* IMP: R-51243-03259 */

# EVIDENCE-OF: R-51243-03259 Otherwise, the BINARY collating function is
# used for comparison.

R-54056-32692-17641-45055-32204-64266-46866-10760 tcl slt th3 src

An operand of a comparison is considered to have an explicit collating function assignment (rule 1 above) if any subexpression of the operand uses the postfix COLLATE operator.

th3/req1/datatype3_09.test:206

/* IMP: R-54056-32692 */

# EVIDENCE-OF: R-54056-32692 An operand of a comparison is considered to
# have an explicit collating function assignment (rule 1 above) if any
# subexpression of the operand uses the postfix COLLATE operator.

R-32922-61395-36624-37075-37487-29134-31632-26826 tcl slt th3 src

Thus, if a COLLATE operator is used anywhere in a comparison expression, the collating function defined by that operator is used for string comparison regardless of what table columns might be a part of that expression.

th3/req1/datatype3_09.test:275

/* IMP: R-32922-61395 */

# EVIDENCE-OF: R-32922-61395 Thus, if a COLLATE operator is used
# anywhere in a comparison expression, the collating function defined by
# that operator is used for string comparison regardless of what table
# columns might be a part of that expression.

R-04965-15290-07639-47076-03173-25539-55321-39216 tcl slt th3 src

If two or more COLLATE operator subexpressions appear anywhere in a comparison, the left most explicit collating function is used regardless of how deeply the COLLATE operators are nested in the expression and regardless of how the expression is parenthesized.

th3/req1/datatype3_09.test:287

/* IMP: R-04965-15290 */

# EVIDENCE-OF: R-04965-15290 If two or more COLLATE operator
# subexpressions appear anywhere in a comparison, the left most explicit
# collating function is used regardless of how deeply the COLLATE
# operators are nested in the expression and regardless of how the
# expression is parenthesized.

R-00640-12624-18808-26941-05585-54150-12700-55718 tcl slt th3 src

The expression "x BETWEEN y and z" is logically equivalent to two comparisons "x >= y AND x <= z" and works with respect to collating functions as if it were two separate comparisons.

th3/req1/datatype3_10.test:11

/* IMP: R-00640-12624 */

# EVIDENCE-OF: R-00640-12624 The expression "x BETWEEN y and z" is
# logically equivalent to two comparisons "x >= y AND x <= z" and
# works with respect to collating functions as if it were two separate
# comparisons.

R-57928-57565-35172-43517-28321-39002-52243-13899 tcl slt th3 src

The expression "x IN (SELECT y ...)" is handled in the same way as the expression "x = y" for the purposes of determining the collating sequence.

th3/req1/datatype3_10.test:45

/* IMP: R-57928-57565 */

# EVIDENCE-OF: R-57928-57565 The expression "x IN (SELECT y ...)" is
# handled in the same way as the expression "x = y" for the purposes of
# determining the collating sequence.

R-06868-44093-33901-50180-63066-19596-24201-39627 tcl slt th3 src

The collating sequence used for expressions of the form "x IN (y, z, ...)" is the collating sequence of x.

th3/req1/datatype3_10.test:71

/* IMP: R-06868-44093 */

# EVIDENCE-OF: R-06868-44093 The collating sequence used for expressions
# of the form "x IN (y, z, ...)" is the collating sequence of x.

R-33393-31741-20908-00667-39287-64680-56276-36828 tcl slt th3 src

Terms of the ORDER BY clause that is part of a SELECT statement may be assigned a collating sequence using the COLLATE operator, in which case the specified collating function is used for sorting.

th3/req1/datatype3_10.test:84

/* IMP: R-33393-31741 */

# EVIDENCE-OF: R-33393-31741 Terms of the ORDER BY clause that is part
# of a SELECT statement may be assigned a collating sequence using the
# COLLATE operator, in which case the specified collating function is
# used for sorting.

R-05898-54598-22699-61791-29592-33550-33097-15159 tcl slt th3 src

Otherwise, if the expression sorted by an ORDER BY clause is a column, then the collating sequence of the column is used to determine sort order.

th3/req1/datatype3_10.test:106

/* IMP: R-05898-54598 */

# EVIDENCE-OF: R-05898-54598 Otherwise, if the expression sorted by an
# ORDER BY clause is a column, then the collating sequence of the column
# is used to determine sort order.

R-45200-50363-59708-20315-54778-07324-53392-01839 tcl slt th3 src

If the expression is not a column and has no COLLATE clause, then the BINARY collating sequence is used.

th3/req1/datatype3_10.test:127

/* IMP: R-45200-50363 */

# EVIDENCE-OF: R-45200-50363 If the expression is not a column and has
# no COLLATE clause, then the BINARY collating sequence is used.

R-53354-47195-06587-01867-57045-45178-06835-07822 tcl slt th3 src

CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);
                   /* x   a     b     c       d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');
 
/* Text comparison a=b is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3

/* Text comparison a=b is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collating sequence. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3

/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

/* Sorting or column c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

/* Sorting of (c||'') is performed using the BINARY collating sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1

/* Sorting of column c is performed using the NOCASE collating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
--result 2 4 3 1

th3/req1/datatype3_08.test:11

/* IMP: R-53354-47195 */

# EVIDENCE-OF: R-53354-47195 CREATE TABLE t1( x INTEGER PRIMARY KEY, a,
# /* collating sequence BINARY */ b COLLATE BINARY, /* collating
# sequence BINARY */ c COLLATE RTRIM, /* collating sequence RTRIM */ d
# COLLATE NOCASE /* collating sequence NOCASE */ ); /* x a b c d */
# INSERT INTO t1 VALUES(1,'abc','abc', 'abc ','abc'); INSERT INTO t1
# VALUES(2,'abc','abc', 'abc', 'ABC'); INSERT INTO t1
# VALUES(3,'abc','abc', 'abc ', 'Abc'); INSERT INTO t1
# VALUES(4,'abc','abc ','ABC', 'abc'); /* Text comparison a=b is
# performed using the BINARY collating sequence. */ SELECT x FROM t1
# WHERE a = b ORDER BY x; --result 1 2 3 /* Text comparison a=b is
# performed using the RTRIM collating sequence. */ SELECT x FROM t1
# WHERE a = b COLLATE RTRIM ORDER BY x; --result 1 2 3 4 /* Text
# comparison d=a is performed using the NOCASE collating sequence. */
# SELECT x FROM t1 WHERE d = a ORDER BY x; --result 1 2 3 4 /* Text
# comparison a=d is performed using the BINARY collating sequence. */
# SELECT x FROM t1 WHERE a = d ORDER BY x; --result 1 4 /* Text
# comparison 'abc'=c is performed using the RTRIM collating sequence. */
# SELECT x FROM t1 WHERE 'abc' = c ORDER BY x; --result 1 2 3 /* Text
# comparison c='abc' is performed using the RTRIM collating sequence. */
# SELECT x FROM t1 WHERE c = 'abc' ORDER BY x; --result 1 2 3 /*
# Grouping is performed using the NOCASE collating sequence (Values **
# 'abc', 'ABC', and 'Abc' are placed in the same group). */ SELECT
# count(*) FROM t1 GROUP BY d ORDER BY 1; --result 4 /* Grouping is
# performed using the BINARY collating sequence. 'abc' and ** 'ABC' and
# 'Abc' form different groups */ SELECT count(*) FROM t1 GROUP BY (d ||
# '') ORDER BY 1; --result 1 1 2 /* Sorting or column c is performed
# using the RTRIM collating sequence. */ SELECT x FROM t1 ORDER BY c, x;
# --result 4 1 2 3 /* Sorting of (c||'') is performed using the BINARY
# collating sequence. */ SELECT x FROM t1 ORDER BY (c||''), x; --result
# 4 2 3 1 /* Sorting of column c is performed using the NOCASE collating
# sequence. */ SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x; --result 2
# 4 3 1

SQLite Requirement Matrix Details datatype3.html

Index Summary Markup Original

SQLite Requirement Matrix Details
datatype3.html