​Давно не было выпусков SQL-WTF SQL-TIL, поэтому давайте сегодня я

исправлюсь и разберу один кейс, с которым столкнулся соучастник нашего Snowflake чатика.

Реляционщики, не расходитесь! Кейс может быть воспроизведен в любой БД :) Да, и в Postgresql тоже.

Вводные: есть несложный вопрос с парой джойнов, группировкой и having. Упростим его значимую часть до такой:

SELECT id
FROM t1
LEFT JOIN t2 ON t2.t1id=t1.id
GROUP BY id
HAVING SUM(amount) = 0
Логика такая: необходимо выбрать все объекты, за которые заплатили, но деньги за них были возвращены в полном объеме, т.е. сумма всех платежей = 0.

Проблема заключалась в следующем: запрос возвращает 2 строки, но при добавлении имени таблицы в условие группировки ( GROUP BY t1.id ), возвращает одну.
Разночтений быть не может, поле id только в одной из таблиц.

Выглядит как лютейший баг.

Но план запроса показывает, что количество строк в обоих случаях одинаково на всех шагах, за исключением последнего: HAVING SUM(amount) = 0 во втором случае отбрасывает один нужный ряд.
Очевидно, что какая-то проблема с суммированием, но без данных понять какая на глаз довольно сложно.
Но и после предоставления реальных данных баг воспроизвелся не так, как было в условии. Но причина была та же - у колонки AMOUNT тип FLOAT!

Каждому разработчику стоит запомнить первое правило бойцовского клуба: НЕ ХРАНИТЬ ФИНАНСОВЫЕ ДАННЫЕ В ТИПЕ FLOAT!

Так почему сумма FLOAT может быть не равна нулю, там где она должна быть равна нулю?

Не, snowflake не сломан. Баг есть во всех базах.

А разгадка одна — безблагодатность IEEE 754 standard.
С примерами можно ознакомиться тут: 0.30000000000000004.com
Если коротко, математические операции над числами с плавающей точкой сломаны во всех языках программирования и вам стоит избегать их, если требуется точная математика :)

Ну ок, допустим, но почему имя таблицы аффектит математику, спросите вы.

А оно и не аффектит :)
Фокус в том, что эти фантомные знаки после запятой зависят от мест слагаемых! Да, забудьте все то, чему вас учили в школе, добро пожаловать в реальный мир :)
SELECT
0.1::FLOAT - 0.1::FLOAT + 0.2::FLOAT - 0.2::FLOAT,
0.1::FLOAT + 0.2::FLOAT - 0.1::FLOAT - 0.2::FLOAT
даст 0 и 0.000000000000000027755575615628914.

Т.е. разный изначальный порядок значений в колонке amount даст разные значения при суммировании, и соответственно, HAVING SUM(amount) будет равен нулю не всегда...

А почему меняется порядок значений?
И тут предъявлять претензии к Snowflake так же будет безосновательным.
Все дело в том, что порядок значений не гарантирован без явного указания сортировки практически в любой базе.
Да, именно так. Если не указан ORDER BY, то в общем случае порядок записей будет рандомным.
Все будет зависеть от того как планировщик посчитает нужным доставать данные в текущий момент. Когда у вас есть джойны, группировки и тд - энтропия только увеличивается, потому что алгоритм, по которому будет произведен JOIN может различаться от запуска к запуску. То же самое касается и GROUP BY.

Отдельный вопрос, конечно, почему указание имени таблицы как-то на это повлияло, но общую ситуацию это никак не меняет. В следующий раз стрельнуло бы не это, так другое.
Например, у меня описанное поведение воспроизводилось при добавлении/удалении JOIN.

Что же стоит сделать прямо сейчас, чтобы не пролюбить все полимеры?

1) найти руками все места в вашем проекте, где используется FLOAT и проверить, не участвует ли он в математических операциях.
2) для хранения финансовых данных заменить FLOAT на NUMERIC, а в приличных базах лучше на INT.
3) дождаться реализацию автоматической проверки в holistic.dev для вашей базы :)

Почему int/bigint лучше numeric?
В 99 случаев из 100 вы знаете с какой точностью вам нужно хранить дробные числа. Обычно это 2 или 4 знака после запятой. Так и храните сумму в центах или в сотых долях цента! Если по какой-то причине точность плавает, то возьмите либо максимальную, либо храните точность в отдельном поле.

Но при этом вы получите прирост скорости в операциях агрегации местами на несколько сотен процентов!

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

Да для денег лучше просто цифры использовать вместо всех этих десималов и флоатов. Просто хранить кол-во денег в минимальной единице например в копейках.

Округлять вы их куда будете? А если, не дай Ктулху, это биткоины?

Зачем округлять? Биткоины в сатоши тоже хранить можно

А теперь продайте этот сатош ваш за рубли. Или купите сатошев на сто рублей.

Можно же просто умножить по курсу сатоши к копейкам

А округлять куда? Сколько там сатош стоит на чёрном рынке?

Если пользователю потом показывать в интерфейсе сатоши как биткоины то конечно придётся округлять

Замечательно.

Только выберите уж либо сэтошы, если решили коверкать под английский, либо правильные сатоси, а не эту полумеру.

Странная у вас манера английский звук «sh» читать.

А это не английский, а транслитерация японского через латиницу.

Ну так и пишите иероглифами, если надо выпендриться.

Вы утверждаете, что два преобразования с потерями лучше одного. Это чушь.

Я утверждаю, что вы выпендриваетесь. И приписываете мне ваши измышления. А потом сами эти измышления называете чушью.

Тогда зачем для получения русского звучания японского слова вы идёте через английский, а не напрямую?

В многих статьях говорят сатоши поэтому так говорю. Я не лингвист, незнаю как слова правильно говорить.

Кто вам сказал, что я куда-то иду из японского языка?! Вопрос риторический и флудить прекращаю.

Ваше сообщение сказало, что вы идёте через английский https://t.me/pgsql/328999. Не учёл, что вы можете не знать, что не всякую латиницу можно читать по правилам английского.

Чувак, ты реально надоел.

При сравнении двух float надо сравнивать модуль их разности с эпсилон.