Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / SQLite Новый топик    Ответить
 Как такое может быть????  [new]
EugeneFrol
Member

Откуда:
Сообщений: 8
Что может случиться с базой???

К сообщению приложен файл. Размер - 57Kb
10 июл 18, 09:07    [21558284]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Dima T
Member

Откуда:
Сообщений: 13712
Что не так?
10 июл 18, 16:45    [21559972]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
EugeneFrol
Member

Откуда:
Сообщений: 8
Таблица Paint (Name TEXT, X1 REAL, Y1 REAL, X2 REAL, Y2 REAL) без индексов и т. д.
В нее добавлялись записи в процессе расчетов.
Среди этих записей имеются записи с одинаковыми значениями в полях X1, X2 и Y1, Y2 соответственно.
Если сделать выборку с поиском по X1, Y1 эти записи выводятся, а если с поиском по X2, Y2 - нет?!?!?!?!?!
Даже SELECT * FROM Paint WHERE x1 = x2 AND y1 = y2 ничего не находит.
На картинке в последнем запросе в ответ должны были попасть записи из первого запроса включительно.
Для SQLite 55.5 в X1 не равен 55.5 в X2, а так же 92.19 в Y1 не равен 92.19 в Y2.
Он считает эти значения не равнозначными.
Не может же sqlite3 при выводе на экран округлять REAL значения, а в таблице сравнивать с сотыми долями.
Можно было объяснить кривыми индексами, но их нет вообще.
Интересно то, что создав чистую базу с такой же таблицей и добавив эти же записи, выборка выполняется нормально!!!
Тогда что может произойти с исходно таблицей или с базой, что ответы на запросы становятся не адекватными????
10 июл 18, 22:19    [21560627]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
White Owl
Member

Откуда:
Сообщений: 12384
EugeneFrol,

REAL значения нельзя сравнивать на равенство, только на больше-меньше.
Причина: сложности конвертирования десятичных дробей в двоичные.
База тут совершенно ни при чем.
11 июл 18, 03:26    [21561094]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Dima T
Member

Откуда:
Сообщений: 13712
Как уже ответили - надо учитывать погрешность. Примерно так
SELECT * FROM Paint WHERE abs(x1 - x2) < 0.0001 AND abs(y1 - y2) < 0.0001

если конкретные величины, то так (чтобы индекс работал)
SELECT * FROM Paint WHERE (x1 between 55.49999 and 55.50001) AND (y1 between 92.18999 and 92.19001)

или хранить в INT, например умножив на 100, INT можно проверять на равенство.

Тут можешь почитать откуда погрешность берется
11 июл 18, 07:27    [21561153]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
EugeneFrol
Member

Откуда:
Сообщений: 8
Спасибо большое за помощь. В MSSQL у меня таких проблем не возникало.
К вашим предложениям я добавил еще свое:
SELECT * FROM Paint WHERE round(x2,2) = 55.5 AND round(y1,2) = 92.19;
или даже
SELECT * FROM Paint WHERE trim(x2)='55.5' AND trim(y2)='92.19';
И получил, что хотел.

Я рассуждал так: Если в полях все данных хранятся в текстовом виде, то при преобразовании в Double одно и тоже тестовое должно преобразоваться в одно и тоже значение Double и соответственно арифметически сравниваться на УРА.
А тут получается при каждом неявном преобразовании получаем разные Double. Вам не кажется это несколько странным?!
11 июл 18, 08:00    [21561174]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Dima T
Member

Откуда:
Сообщений: 13712
EugeneFrol
Спасибо большое за помощь. В MSSQL у меня таких проблем не возникало.

Там какой тип использовался? Если там использовать float, то будут те же проблемы.

EugeneFrol
К вашим предложениям я добавил еще свое:
SELECT * FROM Paint WHERE round(x2,2) = 55.5 AND round(y1,2) = 92.19;
или даже
SELECT * FROM Paint WHERE trim(x2)='55.5' AND trim(y2)='92.19';
И получил, что хотел.

Это нездоровое решение, т.к. при выборке не будет использован индекс, т.е. будет перебор всей таблицы.

EugeneFrol
Я рассуждал так: Если в полях все данных хранятся в текстовом виде, то при преобразовании в Double одно и тоже тестовое должно преобразоваться в одно и тоже значение Double и соответственно арифметически сравниваться на УРА.
А тут получается при каждом неявном преобразовании получаем разные Double. Вам не кажется это несколько странным?!

Никто не гарантирует что преобразование строка->число всегда происходит одним и тем же способом.

у double точность 15 десятичных разрядов. Если при конвертации в строку брать только 15, то строчное представление будет одинаково. Но в реале есть разница в 16-м разряде поэтому при сравнении в исходном виде эти числа не равны.
Выполни запрос
 SELECT x2 - 55.5, y1 - 92.19 FROM Paint WHERE round(x2,2) = 55.5 AND round(y1,2) = 92.19;
11 июл 18, 08:27    [21561187]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Alibek B.
Member

Откуда:
Сообщений: 3126
Dima T
Это нездоровое решение, т.к. при выборке не будет использован индекс, т.е. будет перебор всей таблицы.

Оно еще и неверное, так как нет никакой гарантии, что round(55.5,2)=55.5.
Правильное решение уже называли выше — либо сравнивать с учетом погрешности, либо использовать целочисленные сравнения.
11 июл 18, 09:44    [21561317]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
EugeneFrol
Member

Откуда:
Сообщений: 8
Dima T
Никто не гарантирует что преобразование строка->число всегда происходит одним и тем же способом.


Это меня и беспокоит. Принцип повторяемости алгоритма (при тех же входных - тот же результат) нарушается!!!
И тогда прикладное программирование превращается в битву с подводными камнями.

Ну хорошо.
Если я правильно понимаю при создании индексов можно использовать сложные индексные выражения с использованием core function, а так же указывать в SELECT использование конкретного индекса.

Если заставить SELECT сравнивать не арифметически, а побайтно trim(x2)='55.5'?
Тоже плохое решение?
11 июл 18, 10:37    [21561448]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Alibek B.
Member

Откуда:
Сообщений: 3126
Нужно почитать о том, что такое вещественные числа.
И понять, что они имеют мало общего с тем, что видит человек глазами.
И тогда никаких вопросов и неожиданностей с их использованием не будет.
11 июл 18, 10:52    [21561487]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
EugeneFrol
Member

Откуда:
Сообщений: 8
Я стандарт IEEE754 знаю наизусть. Но ведь дело не в этом.
Дело в том, что SELECT (55.5=55.5) в SQLite в разное время может дать true, а может false.
11 июл 18, 10:57    [21561498]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Alibek B.
Member

Откуда:
Сообщений: 3126
EugeneFrol
Я стандарт IEEE754 знаю наизусть.

Не похоже.

EugeneFrol
SELECT (55.5=55.5)

А разве в примере написано это? Там написано function(55.5)=55.5.
Так с вещественными числами работать нельзя.
11 июл 18, 11:10    [21561536]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Dima T
Member

Откуда:
Сообщений: 13712
EugeneFrol
Если я правильно понимаю при создании индексов можно использовать сложные индексные выражения с использованием core function, а так же указывать в SELECT использование конкретного индекса.

Не знаю, третий день в sqlite копаюсь, может и можно, план выполнения запроса надо смотреть, если задействует индекс, то можно.
EugeneFrol
Если заставить SELECT сравнивать не арифметически, а побайтно trim(x2)='55.5'?
Тоже плохое решение?

Работать будет, но будет делать слишком много ненужных действий для конвертации число->строка. В БД число, а сравниваем строки.

Чем тебе мой вариант не нравится 21561153 x1 between 55.49999 and 55.50001 ? Т.е. получить интервал с учетом погрешности и проверять попадание в интервал.
Букав побольше, но ненужных действий минимум и индекс задействует при наличии индекса.

PS В БД никогда double не использовал по причине из-за которой у тебя сейчас проблемы, тут заменил бы на INT со сдвигом запятой, т.е. если надо два знака после запятой, то хранить x*100.
11 июл 18, 11:34    [21561637]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
EugeneFrol
Member

Откуда:
Сообщений: 8
Dima T
Никто не гарантирует что преобразование строка->число всегда происходит одним и тем же способом.


А если не преобразовывать? А сравнивать строки?
11 июл 18, 11:42    [21561671]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Dima T
Member

Откуда:
Сообщений: 13712
EugeneFrol
Dima T
Никто не гарантирует что преобразование строка->число всегда происходит одним и тем же способом.


А если не преобразовывать? А сравнивать строки?

Где ты строку возьмешь если в БД лежит число?
11 июл 18, 11:44    [21561691]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Dima T
Member

Откуда:
Сообщений: 13712
Хранить в БД в виде строки тоже сомнительный вариант, сравнить конкретное значение сможешь, но невозможно сделать выборку диапазона.
11 июл 18, 11:48    [21561715]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
EugeneFrol
Member

Откуда:
Сообщений: 8
Мне нужно:
SELECT * FROM Paint WHERE X1 = X2 AND Y1 = Y2
Результат 0 записей.
Беру, например
SELECT * FROM Paint WHERE X1 || 'x' || Y1 = X2 || 'x' || Y2;
Результат все записи, где X1 = X2 AND Y1 = Y2.

Если SQLite хранит в таблице REAL значения в строковом виде ему не придется в этом случае преобразовывать туда-обратно?
Или я ошибаюсь? НИКТО НИЧЕГО НЕ ГАРАНТИРУЕТ
11 июл 18, 11:55    [21561761]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Dima T
Member

Откуда:
Сообщений: 13712
EugeneFrol
Мне нужно:
SELECT * FROM Paint WHERE X1 = X2 AND Y1 = Y2
Результат 0 записей.
Беру, например
SELECT * FROM Paint WHERE X1 || 'x' || Y1 = X2 || 'x' || Y2;
Результат все записи, где X1 = X2 AND Y1 = Y2.

Если SQLite хранит в таблице REAL значения в строковом виде ему не придется в этом случае преобразовывать туда-обратно?
Или я ошибаюсь? НИКТО НИЧЕГО НЕ ГАРАНТИРУЕТ

Чукча писатель?
Я же уже несколько раз написал: в БД хранится REAL, т.к. ты указал при создании таблицы X1 REAL.
Если ты пишешь x1 = 55.5 то 55.5 преобразуется в тип REAL и дальше сравнение с ним, т.е. сравниваются два значения REAL.

По некоторым причинам в REAL невозможно точно сохранить, гарантия только 15 десятичных знаков, т.е. 55.5 на самом деле может быть как 55.50000000000001 так и 55.49999999999999. Соответственно если БД первое, а 55.5 преобразовалось во второе, то они не равны при сравнении как REAL.

Но при преобразовании в строку учитываются только первые 15 разрядов и оба варианта преобразуются в строку '55.5', поэтому когда ты сравниваешь как строки, то все правильно работает.
X1 || 'x' || Y1 на выходе строка. trim(x1) тоже строка.

Понял?
11 июл 18, 12:13    [21561865]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Dima T
Member

Откуда:
Сообщений: 13712
EugeneFrol
Мне нужно:
SELECT * FROM Paint WHERE X1 = X2 AND Y1 = Y2

Есть стандартная практика сравнения двух значений double: ABS(X1 - X2) < Epsilon, где Epsilon допустимая погрешность. Уже писал про это 21561153
11 июл 18, 12:17    [21561878]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
EugeneFrol
Member

Откуда:
Сообщений: 8
Прошу прощения. Я ошибался. Строковый тип в запросе неявно преобразуется к REAL.
Из док.:
All values in SQL statements, whether they are literals embedded in SQL statement text or parameters bound to precompiled SQL statements have an implicit storage class. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.

Спасибо!
11 июл 18, 12:38    [21561956]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Alibek B.
Member

Откуда:
Сообщений: 3126
Dima T
гарантия только 15 десятичных знаков

Нет такой гарантии.
Для некоторых значений возможны изменения и в более высоких разрядах.
11 июл 18, 12:40    [21561967]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Dima T
Member

Откуда:
Сообщений: 13712
Alibek B.
Dima T
гарантия только 15 десятичных знаков

Нет такой гарантии.
Для некоторых значений возможны изменения и в более высоких разрядах.

Гарантия есть, т.к. мантисса 52 бита, следовательно вмещает 2^52 значений, что больше чем 10^15.
Другое дело что при вычислениях погрешность накапливается и в итоге может исказить 15-й разряд.
11 июл 18, 12:47    [21562002]     Ответить | Цитировать Сообщить модератору
 Re: Как такое может быть????  [new]
Alibek B.
Member

Откуда:
Сообщений: 3126
Dima T
Другое дело что при вычислениях погрешность накапливается и в итоге может исказить 15-й разряд.

Кроме накопления погрешности есть еще бесконечная периодическая двоичная дробность.
Вообще ТС я бы советовал почитать эту статью: https://habr.com/post/112953/
11 июл 18, 13:13    [21562126]     Ответить | Цитировать Сообщить модератору
Все форумы / SQLite Ответить