Sqlite - гранулярность с плавающей запятой, нарушающая уникальное ограничение?

Я использую SQLite 3.25.2 в Windows, скачал последние предварительно скомпилированные двоичные файлы с официальной страницы https://sqlite.org/download.html

Выполнение следующего кода

DROP TABLE IF EXISTS TestReal;
CREATE TABLE TestReal(A REAL UNIQUE);
INSERT INTO TestReal values (9223372036854775807.0);
INSERT INTO TestReal values (9223372036854775807.0 - 1);
INSERT INTO TestReal values (9223372036854775807.0 - 2);
INSERT INTO TestReal values (9223372036854775807.0 - 3);

не выполняется, как и ожидалось, поскольку 9223372036854775807.0 равно 2 ^ 63, эти числа выходят за пределы диапазона, в котором все целые числа точно могут быть представлены как двойные. я имею в виду

sqlite> select 9223372036854775807.0 = 9223372036854775807.0 - 1;
1
sqlite> select 9223372036854775807.0 = 9223372036854775807.0 - 512;
1

Столбец A уникален, поэтому имеет смысл вывести сообщение «Ошибка ограничения UNIQUE: TestReal.A». Но, похоже, есть непреднамеренный обходной путь

DROP TABLE IF EXISTS TestReal;
CREATE TABLE TestReal(A REAL UNIQUE);
INSERT INTO TestReal values (9223372036854775807);
INSERT INTO TestReal values (9223372036854775807 - 1);
INSERT INTO TestReal values (9223372036854775807 - 2);
INSERT INTO TestReal values (9223372036854775807 - 3);

работает без проблем. Следующие запросы подтверждают, что теперь в таблицу вставлено ровно 4 значения, но только одно отдельное значение, несмотря на наличие уникального ограничения.

sqlite> SELECT * FROM TestReal;
9.22337203685478e+18
9.22337203685478e+18
9.22337203685478e+18
9.22337203685478e+18
sqlite> SELECT DISTINCT(A) FROM TestReal;
9.22337203685478e+18
sqlite> .schema
CREATE TABLE TestReal(A REAL UNIQUE);

Итак, мой вопрос: это ошибка в SQLite? Или я не правильно понимаю, что на самом деле означает «уникальный»?

Я не знаю, как именно SQLite сравнивает значения INTEGER и REAL друг с другом, но я могу продублировать эту проблему на своей машине, а также отметить, что SELECT 9223372036854775807 = 9223372036854775807.0 возвращает 0 (ложь).

dan04 01.11.2018 00:47

Спасибо за тестирование! Я думаю, что вы что-то поняли в своем примере, потому что я действительно могу вставить оба этих значения в REAL UNIQUE столбец, как будто они разные, но DISTINCT (A) дает только одно значение. Похоже, что значения сначала проверяются на уникальность, и только ТОЛЬКО преобразуются в заданный тип столбца, что меняет способ их проверки на уникальность.

szmate1618 01.11.2018 01:04
3
2
76
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

С выпуском SQLite 3.29.0 это было решено, это никогда не было предполагаемым поведением, а было ошибкой. Ошибка все еще присутствует в 3.28.0, но не в 3.29.0, в новейших версиях оба моих фрагмента кода терпят неудачу, как и ожидалось.

Это коммит, содержащий исправление: https://www.sqlite.org/src/info/9b0915272f4d4052

Я подтвердил это, вручную применив эту фиксацию к исходному коду 3.28.0 и заменив MEM_IntReal на (MEM_Int | MEM_Real).

Билет об ошибке был открыт после того, как о возможной ошибке было сообщено здесь в официальном списке рассылки SQLite: https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg115214.html

Я тоже сообщил об этом, на самом деле немного раньше, но мой отчет не привлек особого внимания: https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg112655.html

Другие вопросы по теме