Типы данных в SQLite

1. Типы данных в SQLite

Большинство ядер баз данных SQL (насколько нам известно, все ядра баз данных SQL, кроме SQLite) используют статическую, строгую типизацию. При статической типизации тип данных значения определяется его контейнером — конкретным столбцом, в котором хранится значение.

В SQLite используется более общая система динамической типизации. В SQLite тип данных связан с самим значением, а не с его контейнером. Динамическая система типизации SQLite обратно совместима с более распространёнными статическими системами типов других ядер баз данных: операторы SQL, работающие со статически типизированными базами данных, работают в SQLite так же. Однако динамическая типизация в SQLite позволяет выполнять операции, невозможные в традиционных базах данных со строгой типизацией. Гибкая типизация — это возможность SQLite, а не ошибка.

Обновление: начиная с версии 3.37.0 (2021-11-27), SQLite предоставляет таблицы STRICT со строгой проверкой типов — для разработчиков, которым нужен такой подход.

2. Классы хранения и типы данных

Каждое значение, хранящееся в базе данных SQLite (или обрабатываемое ядром базы данных), относится к одному из следующих классов хранения:

• NULL. Значение NULL.

• INTEGER. Целое число со знаком, хранящееся в 0, 1, 2, 3, 4, 6 или 8 байтах в зависимости от величины значения.

• REAL. Число с плавающей точкой, хранящееся как 8-байтовое число с плавающей точкой IEEE.

• TEXT. Текстовая строка, хранящаяся с использованием кодировки базы данных (UTF-8, UTF-16BE или UTF-16LE).

• BLOB. Блок данных, хранящийся в точности в том виде, в котором он был введён.

Класс хранения является более общим понятием, чем тип данных. Например, класс хранения INTEGER включает 7 различных целочисленных типов данных разной длины. Это имеет значение при хранении на диске. Однако сразу после чтения значений INTEGER с диска в память для обработки они преобразуются в наиболее общий тип данных (8-байтовое целое число со знаком). Поэтому в большинстве случаев «класс хранения» неотличим от «типа данных», и эти термины можно использовать взаимозаменяемо.

Любой столбец базы данных SQLite версии 3, кроме столбца INTEGER PRIMARY KEY, может хранить значение любого класса хранения.

Все значения в операторах SQL — будь то литералы, встроенные в текст оператора SQL, или параметры, связанные с предварительно скомпилированными операторами SQL, — имеют неявный класс хранения. В описанных ниже обстоятельствах ядро базы данных во время выполнения запроса может преобразовывать значения между числовыми классами хранения (INTEGER и REAL) и TEXT.

2.1. Логический тип данных

В SQLite нет отдельного класса хранения для логических значений. Вместо этого логические значения хранятся как целые числа 0 (ложь) и 1 (истина).

Начиная с версии 3.23.0 (2018-04-02), SQLite распознаёт ключевые слова "TRUE" и "FALSE", однако эти ключевые слова фактически являются лишь альтернативными обозначениями целочисленных литералов 1 и 0 соответственно.

2.2. Тип данных даты и времени

В SQLite нет класса хранения, предназначенного для хранения дат и/или времени. Вместо этого встроенные функции даты и времени SQLite позволяют хранить даты и время в виде значений TEXT, REAL или INTEGER:

• TEXT — строки в формате ISO8601 ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL — числа юлианского дня, то есть количество дней, прошедших с полудня 24 ноября 4714 года до н. э. по григорианскому календарю с продолжением назад во времени.
• INTEGER — время Unix, то есть количество секунд с 1970-01-01 00:00:00 UTC.

Приложения могут хранить даты и время в любом из этих форматов и свободно преобразовывать их между форматами с помощью встроенных функций даты и времени.

3. Типовая близость

Ядра баз данных SQL со строгой типизацией обычно пытаются автоматически преобразовывать значения в соответствующие типы данных. Рассмотрим следующий пример:

CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);

База данных со строгой типизацией перед вставкой преобразует строку '123' в целое число 123, а целое число 456 — в строку '456'.

Чтобы обеспечить максимальную совместимость SQLite с другими ядрами баз данных и позволить приведённому выше примеру работать в SQLite так же, как в других ядрах баз данных SQL, SQLite поддерживает понятие «типовой близости» столбцов. Типовая близость столбца — это рекомендуемый тип данных для хранимых в нём данных. Важно, что тип рекомендуется, а не является обязательным. Любой столбец по-прежнему может хранить данные любого типа. Просто некоторые столбцы при наличии выбора предпочитают один класс хранения другому. Предпочтительный класс хранения столбца называется его «типовой близостью».

Каждому столбцу базы данных SQLite 3 назначается одна из следующих типовых близостей:

• TEXT
• NUMERIC
• INTEGER
• REAL
• BLOB

(Историческая справка: типовая близость "BLOB" раньше называлась "NONE". Однако этот термин легко было спутать с «отсутствием типовой близости», поэтому его переименовали.)

Столбец с типовой близостью TEXT хранит все данные, используя классы хранения NULL, TEXT или BLOB. Если в столбец с типовой близостью TEXT вставляются числовые данные, перед сохранением они преобразуются в текст.

Столбец с типовой близостью NUMERIC может содержать значения всех пяти классов хранения. Если в столбец NUMERIC вставляются текстовые данные, класс хранения текста преобразуется в INTEGER или REAL (в порядке предпочтения), если текст является корректно сформированным целочисленным или вещественным литералом соответственно. Если значение TEXT является корректно сформированным целочисленным литералом, слишком большим для 64-разрядного целого числа со знаком, оно преобразуется в REAL. При преобразовании между классами хранения TEXT и REAL сохраняется около 15,95 значащих десятичных цифр числа. (Точность преобразования ограничена использованием кодировки IEEE 754 binary64, или «double», для чисел с плавающей точкой.) Если значение TEXT не является корректно сформированным целочисленным или вещественным литералом, оно сохраняется как TEXT. Для целей этого абзаца шестнадцатеричные целочисленные литералы не считаются корректно сформированными и сохраняются как TEXT. (Это сделано для исторической совместимости с версиями SQLite до версии 3.8.6 от 2014-08-15, в которой шестнадцатеричные целочисленные литералы впервые появились в SQLite.) Если в столбец с типовой близостью NUMERIC вставляется значение с плавающей точкой, которое можно точно представить как целое число, оно преобразуется в целое число. Значения NULL и BLOB не преобразуются.

Строка может выглядеть как литерал с плавающей точкой, содержащий десятичную точку и/или экспоненциальную запись, но если значение можно представить как целое число, типовая близость NUMERIC преобразует его в целое число. Поэтому строка '3.0e+5' в столбце с типовой близостью NUMERIC хранится как целое число 300000, а не как число с плавающей точкой 300000.0.

Столбец с типовой близостью INTEGER ведёт себя так же, как столбец с типовой близостью NUMERIC. Разница между типовыми близостями INTEGER и NUMERIC проявляется только в выражении CAST: выражение "CAST(4.0 AS INT)" возвращает целое число 4, тогда как "CAST(4.0 AS NUMERIC)" оставляет значение числом с плавающей точкой 4.0.

Столбец с типовой близостью REAL ведёт себя как столбец с типовой близостью NUMERIC, за исключением того, что целочисленные значения принудительно преобразуются в представление с плавающей точкой. (В целях внутренней оптимизации небольшие значения с плавающей точкой без дробной части, хранящиеся в столбцах с типовой близостью REAL, записываются на диск как целые числа, чтобы занимать меньше места, и автоматически преобразуются обратно в числа с плавающей точкой при чтении. Эта оптимизация совершенно незаметна на уровне SQL, и обнаружить её можно только при изучении необработанных битов файла базы данных.)

Столбец с типовой близостью BLOB не отдаёт предпочтения ни одному классу хранения, и данные не пытаются преобразовать из одного класса хранения в другой.

3.1. Определение типовой близости столбца

Для таблиц, не объявленных как STRICT, типовая близость столбца определяется объявленным типом столбца в соответствии со следующими правилами, применяемыми в указанном порядке:

1. Если объявленный тип содержит строку "INT", столбцу назначается типовая близость INTEGER.

2. Если объявленный тип столбца содержит любую из строк "CHAR", "CLOB" или "TEXT", столбец получает типовую близость TEXT. Обратите внимание: тип VARCHAR содержит строку "CHAR", поэтому ему назначается типовая близость TEXT.

3. Если объявленный тип столбца содержит строку "BLOB" или тип не указан, столбец получает типовую близость BLOB.

4. Если объявленный тип столбца содержит любую из строк "REAL", "FLOA" или "DOUB", столбец получает типовую близость REAL.

5. В противном случае типовой близостью будет NUMERIC.

Обратите внимание: порядок правил определения типовой близости столбца важен. Объявленный тип столбца "CHARINT" соответствует и правилу 1, и правилу 2, однако приоритет имеет первое правило, поэтому типовой близостью столбца будет INTEGER.

3.1.1. Примеры названий типовой близости

В следующей таблице показано, как названия распространённых типов данных из более традиционных реализаций SQL преобразуются в типовые близости с помощью пяти правил из предыдущего раздела. В таблице приведена лишь небольшая часть названий типов данных, которые принимает SQLite. Обратите внимание: числовые аргументы в скобках после названия типа (например, "VARCHAR(255)") игнорируются SQLite — SQLite не накладывает ограничений на длину строк, BLOB-объектов или числовых значений (кроме общего ограничения SQLITE_MAX_LENGTH).

Примеры названий типов данных в операторе CREATE TABLE или выражении CAST	Результирующая типовая близость	Правило определения типовой близости
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 тип данных не указан	BLOB	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC	5

Обратите внимание: объявленный тип "FLOATING POINT" получит типовую близость INTEGER, а не REAL, из-за подстроки "INT" в конце слова "POINT". А объявленный тип "STRING" получит типовую близость NUMERIC, а не TEXT.

3.2. Типовая близость выражений

Каждый столбец таблицы имеет типовую близость (одну из BLOB, TEXT, INTEGER, REAL или NUMERIC), однако выражения не обязательно имеют типовую близость.

Типовая близость выражения определяется следующими правилами:

• Правый операнд оператора IN или NOT IN не имеет типовой близости, если операнд является списком, и имеет ту же типовую близость, что и выражение результирующего набора, если операнд является оператором SELECT.

• Если выражение представляет собой простую ссылку на столбец обычной таблицы (не VIEW и не подзапроса), оно имеет ту же типовую близость, что и столбец таблицы.

  • Скобки вокруг имени столбца игнорируются. Поэтому, если X и Y.Z — имена столбцов, выражения (X) и (Y.Z) также считаются именами столбцов и имеют типовую близость соответствующих столбцов.

  • Любые операторы, применённые к именам столбцов, включая унарный оператор "+" без эффекта, преобразуют имя столбца в выражение, которое всегда не имеет типовой близости. Поэтому, даже если X и Y.Z — имена столбцов, выражения +X и +Y.Z не являются именами столбцов и не имеют типовой близости.

• Выражение вида "CAST(expr AS type)" имеет типовую близость, совпадающую с типовой близостью столбца, объявленный тип которого — "type".

• Оператор COLLATE имеет ту же типовую близость, что и его левый операнд.

• В противном случае выражение не имеет типовой близости.

3.3. Типовая близость столбцов представлений и подзапросов

«Столбцы» VIEW или подзапроса в предложении FROM на самом деле являются выражениями в результирующем наборе оператора SELECT, реализующего VIEW или подзапрос. Поэтому типовая близость столбцов VIEW или подзапроса определяется приведёнными выше правилами для типовой близости выражений. Рассмотрим пример:

CREATE TABLE t1(a INT, b TEXT, c REAL);
CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;

Типовая близость столбца v1.x будет такой же, как у t1.b (TEXT), поскольку v1.x напрямую соответствует t1.b. Однако столбцы v1.y и v1.z не имеют типовой близости, поскольку они соответствуют выражению a+c и значению 42, а выражения всегда не имеют типовой близости.

3.3.1. Типовая близость столбцов составных представлений

Если оператор SELECT, реализующий VIEW или подзапрос в предложении FROM, является составным оператором SELECT, типовая близость каждого столбца VIEW или подзапроса будет совпадать с типовой близостью соответствующего результирующего столбца одного из отдельных операторов SELECT, составляющих составной оператор. Однако невозможно заранее определить, какой именно оператор SELECT будет использован для определения типовой близости. В разные моменты вычисления запроса для определения типовой близости могут использоваться разные составляющие операторы SELECT. Выбор может различаться в разных версиях SQLite. Он может меняться от одного запроса к следующему в одной и той же версии SQLite. Он может различаться в разные моменты выполнения одного запроса. Поэтому нельзя быть уверенным в том, какая типовая близость будет использована для столбцов составного оператора SELECT, если типовые близости в составляющих подзапросах различаются.

Рекомендуется не смешивать типовые близости в составном операторе SELECT, если для вас важен тип данных результата. Смешивание типовых близостей в составном операторе SELECT может приводить к неожиданным и нелогичным результатам. См., например, сообщение на форуме 02d7be94d7.

3.4. Пример работы типовой близости столбца

В следующем примере SQL показано, как SQLite использует типовую близость столбца для преобразования типов при вставке значений в таблицу.

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

4. Выражения сравнения

В SQLite версии 3 используется обычный набор операторов сравнения SQL, включая "=", "==", "<", "<=", ">", ">=", "!=", "", "IN", "NOT IN", "BETWEEN", "IS" и "IS NOT".

4.1. Порядок сортировки

Результаты сравнения зависят от классов хранения операндов в соответствии со следующими правилами:

• Значение с классом хранения NULL считается меньшим любого другого значения (включая другое значение с классом хранения NULL).

• Значение INTEGER или REAL меньше любого значения TEXT или BLOB. При сравнении INTEGER или REAL с другим значением INTEGER или REAL выполняется числовое сравнение.

• Значение TEXT меньше значения BLOB. При сравнении двух значений TEXT результат определяется с помощью соответствующей последовательности сортировки.

• При сравнении двух значений BLOB результат определяется с помощью memcmp().

4.2. Преобразование типов перед сравнением

Перед выполнением сравнения SQLite может попытаться преобразовать значения между классами хранения INTEGER, REAL и/или TEXT. Попытки преобразования перед сравнением зависят от типовой близости операндов.

«Применить типовую близость» означает преобразовать операнд в определённый класс хранения тогда и только тогда, когда преобразование не приводит к потере существенной информации. Числовые значения всегда можно преобразовать в TEXT. Значения TEXT можно преобразовать в числовые значения, если текст содержит корректно сформированный целочисленный или вещественный литерал, но не шестнадцатеричный целочисленный литерал. Значения BLOB преобразуются в TEXT простым прочтением двоичного содержимого BLOB как текстовой строки в текущей кодировке базы данных.

Перед сравнением к операндам оператора сравнения применяется типовая близость в соответствии со следующими правилами, применяемыми в указанном порядке:

• Если один операнд имеет типовую близость INTEGER, REAL или NUMERIC, а другой — TEXT, BLOB или не имеет типовой близости, к другому операнду применяется типовая близость NUMERIC.

• Если один операнд имеет типовую близость TEXT, а другой не имеет типовой близости, к другому операнду применяется типовая близость TEXT.

• В противном случае типовая близость не применяется, и оба операнда сравниваются без изменений.

Выражение "a BETWEEN b AND c" обрабатывается как два отдельных бинарных сравнения: "a >= b AND a <= c"; при этом к 'a' в каждом сравнении могут применяться разные типовые близости. Преобразование типов в сравнениях вида "x IN (SELECT y ...)" выполняется так, как если бы сравнение имело вид "x=y". Выражение "a IN (x, y, z, ...)" эквивалентно выражению "a = +x OR a = +y OR a = +z OR ...". Иными словами, значения справа от оператора IN (в этом примере значения "x", "y" и "z") считаются не имеющими типовой близости, даже если это значения столбцов или выражения CAST.

4.3. Пример сравнения

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

Все результаты в примере остаются такими же, если поменять операнды сравнений местами — например, переписать выражения вида "a<40" как "40>a".

5. Операторы

Математические операторы (+, -, *, /, %, <<, >>, & и |) интерпретируют оба операнда как числа. Операнды STRING или BLOB автоматически преобразуются в значения REAL или INTEGER. Если STRING или BLOB выглядит как вещественное число (содержит десятичную точку или экспоненту) либо значение выходит за диапазон, представимый 64-разрядным целым числом со знаком, оно преобразуется в REAL. В противном случае операнд преобразуется в INTEGER. Неявное преобразование типов математических операндов немного отличается от CAST в NUMERIC: значения STRING и BLOB, похожие на вещественные числа, но не имеющие дробной части, остаются значениями REAL, а не преобразуются в INTEGER, как при CAST в NUMERIC. Преобразование STRING или BLOB в REAL или INTEGER выполняется, даже если оно приводит к потере данных и необратимо. Некоторые математические операторы (%, <<, >>, & и |) требуют операнды INTEGER. Для этих операторов операнды REAL преобразуются в INTEGER так же, как при CAST в INTEGER. Операторы <<, >>, & и | всегда возвращают результат INTEGER (или NULL), а оператор % возвращает INTEGER или REAL (либо NULL) в зависимости от типа операндов. Операнд NULL в математическом операторе даёт результат NULL. Операнд математического оператора, который никак не похож на число и не является NULL, преобразуется в 0 или 0.0. Деление на ноль даёт результат NULL.

6. Сортировка, группировка и составные операторы SELECT

При сортировке результатов запроса с помощью предложения ORDER BY сначала идут значения с классом хранения NULL, затем значения INTEGER и REAL, перемежающиеся в числовом порядке, далее значения TEXT в порядке последовательности сортировки и, наконец, значения BLOB в порядке memcmp(). Перед сортировкой преобразование классов хранения не выполняется.

При группировке значений с помощью предложения GROUP BY значения с разными классами хранения считаются различными, за исключением значений INTEGER и REAL, которые считаются равными, если они численно равны. Предложение GROUP BY не применяет типовую близость ни к каким значениям.

Составные операторы SELECT UNION, INTERSECT и EXCEPT выполняют неявное сравнение значений. Для неявных сравнений, связанных с UNION, INTERSECT или EXCEPT, к операндам сравнения типовая близость не применяется — значения сравниваются без изменений.

7. Последовательности сортировки

При сравнении двух строк SQLite использует последовательность сортировки или функцию сортировки (это два названия одного и того же понятия), чтобы определить, какая строка больше или равны ли строки. В SQLite есть три встроенные функции сортировки: BINARY, NOCASE и RTRIM.

• BINARY — сравнивает строковые данные с помощью memcmp() независимо от кодировки текста.
• NOCASE — работает аналогично BINARY, но для сравнения использует sqlite3_strnicmp(). Поэтому 26 прописных букв ASCII перед сравнением преобразуются в соответствующие строчные. Обратите внимание: регистр изменяется только для символов ASCII. SQLite не выполняет полноценное преобразование регистра UTF из-за большого размера необходимых таблиц. Также обратите внимание: любые символы U+0000 в строке считаются завершающими символами строки при сравнении.
• RTRIM — работает так же, как BINARY, но конечные пробелы игнорируются.

Приложение может зарегистрировать дополнительные функции сортировки с помощью интерфейса sqlite3_create_collation().

Функции сортировки имеют значение только при сравнении строковых значений. Числовые значения всегда сравниваются численно, а BLOB-объекты — побайтно с помощью memcmp().

7.1. Назначение последовательностей сортировки в SQL

У каждого столбца каждой таблицы есть связанная с ним функция сортировки. Если функция сортировки явно не задана, по умолчанию используется BINARY. Предложение COLLATE в определении столбца используется для назначения столбцу альтернативной функции сортировки.

Правила определения функции сортировки для бинарного оператора сравнения (=, <, >, <=, >=, !=, IS и IS NOT) таковы:

1. Если одному из операндов явно назначена функция сортировки с помощью постфиксного оператора COLLATE, для сравнения используется эта функция сортировки; приоритет имеет функция сортировки левого операнда.

2. Если один из операндов является столбцом, используется функция сортировки этого столбца; приоритет имеет левый операнд. Для целей предыдущего предложения имя столбца, перед которым стоит один или несколько унарных операторов "+" и/или операторов CAST, всё равно считается именем столбца.

3. В противном случае для сравнения используется функция сортировки BINARY.

Операнд сравнения считается явно связанным с функцией сортировки (правило 1 выше), если в любом подвыражении этого операнда используется постфиксный оператор COLLATE. Поэтому, если оператор COLLATE используется где-либо в выражении сравнения, для сравнения строк применяется заданная им функция сортировки независимо от того, какие столбцы таблиц могут входить в это выражение. Если в сравнении встречается два или более подвыражения с оператором COLLATE, используется самая левая явно заданная функция сортировки независимо от глубины вложенности операторов COLLATE и расстановки скобок в выражении.

Выражение "x BETWEEN y and z" логически эквивалентно двум сравнениям "x >= y AND x <= z" и с точки зрения функций сортировки обрабатывается как два отдельных сравнения. Для определения последовательности сортировки выражение "x IN (SELECT y ...)" обрабатывается так же, как выражение "x = y". Для выражений вида "x IN (y, z, ...)" используется последовательность сортировки x. Если для оператора IN требуется явно указать последовательность сортировки, её следует применить к левому операнду, например: "x COLLATE nocase IN (y,z, ...)".

Элементам предложения ORDER BY оператора SELECT можно назначить последовательность сортировки с помощью оператора COLLATE; в этом случае для сортировки используется указанная функция сортировки. В противном случае, если сортируемое предложением ORDER BY выражение является столбцом, для определения порядка сортировки используется последовательность сортировки этого столбца. Если выражение не является столбцом и не содержит предложения COLLATE, используется последовательность сортировки BINARY.

7.2. Примеры последовательностей сортировки

В приведённых ниже примерах указаны последовательности сортировки, которые используются для определения результатов сравнения текста в различных операторах SQL. Обратите внимание: сравнение текста может не потребоваться, а последовательность сортировки может не использоваться для числовых значений, значений BLOB или NULL.

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