​Давно не было выпусков 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 знака после запятой. Так и храните сумму в центах или в сотых долях цента! Если по какой-то причине точность плавает, то возьмите либо максимальную, либо храните точность в отдельном поле.

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

Все выпуски SQL-WTF SQL-TIL
(чтобы вы могли пошарить):

postgresql edition:
1) https://news.1rj.ru/str/nosingularity/535
2) https://news.1rj.ru/str/nosingularity/541
3) https://news.1rj.ru/str/nosingularity/548
4) https://news.1rj.ru/str/nosingularity/572

snowflake edition:
5) https://news.1rj.ru/str/nosingularity/582
6) https://news.1rj.ru/str/nosingularity/602
7) https://news.1rj.ru/str/nosingularity/753 и https://news.1rj.ru/str/nosingularity/754
8) https://news.1rj.ru/str/nosingularity/762
9) https://news.1rj.ru/str/nosingularity/826

mix:
10) https://news.1rj.ru/str/nosingularity/755
11) https://news.1rj.ru/str/nosingularity/803 и https://news.1rj.ru/str/nosingularity/804
12) https://news.1rj.ru/str/nosingularity/808 и https://news.1rj.ru/str/nosingularity/809
13) https://news.1rj.ru/str/nosingularity/857

​Очень хочется сделать свою SQL бд?

Вот тут ребята из Querify Labs показывают на пальцах как навесить SQL на Apache Ignite.

Есть такая штука - Apache Calcite. На ней можно описать парсер и планер с оптимизатором. А потом научить этот план выполняться над целевой базой.

Кому-то захочется реализовать мою бредовую идею SQL для редис - пожалуйста.

У кого-то своя база на GPU но без SQL - пожалуйста.

Есть желающие заменить уродский ELK-синтаксис на нормальный? Велкам.

Короче, SQL в каждый дом :)

Как и год назад для 13 версии, Hewlett Packard выложили 80 страниц с описанием изменений в Postgresql 14 по сравнению с предыдущей версией.
Выделил самые, на мой взгляд, интересные:

LZ4 compression
TOAST колонки теперь можно жать.

CREATE INDEX
INCLUDE можно использовать для SP-GiST индексов

 CREATE INDEX idx1_gist1 ON gist1 USING spgist (c1) INCLUDE (c2) 

BRIN index
Можно делать Bloom-индексы

 CREATE INDEX idx1_data1 ON data1 USING brin (c1 numeric_bloom_ops (false_positive_rate = 0.05, n_distinct_per_range = 100)) 

Extended Statistics
Можно создавать статистику по выражениям

 CREATE STATISTICS extstat1_data1 ON MOD(c1, 10), MOD(c2, 10) FROM data1 

Multirange type

 SELECT '{[1, 5), (10, 20]}'::int8multirange 

Jsonb type
Более удобный синтаксис для JSONB

SELECT ('[1, "2", null]'::jsonb)[1] ;

SELECT ('{"age": 25}'::jsonb)['age'] ;

SELECT ('{"email":"pgsql-hackers@postgresql.org", "phone":"+3012345678"}'::jsonb) ['phone'] ;

Документ полностью

Distributed SQL Summit 2021 (кродеться) Sept 21 - 23

https://distributedsql.org/

Clickhouse теперь отдельная компания с $50m инвестиций.

Переживаю за Altinity…

https://vc.ru/services/295690-yandeks-s-fondami-otkryl-kompaniyu-clickhouse-ona-vypustit-servisy-na-osnove-sistem-upravleniya-bazami-dannyh

​Добавил в свою подборку необычных применений SQL еще парочку записей.
Внезапно обнаружилось сразу несколько проектов, реализующих SQL-интерфейс к облачной инфре:

cloudquery.io
steampipe.io
iasql.com

Последний не зарелижен, неизвестно будет ли opensource, но выглядит наиболее интересно. Если первые два дают возможность делать SELECT'ы, то последний еще и INSERT'ы (может еще и UPDATE'ы):

INSERT INTO aws_ec2 (ami_id, ec2_instance_type_id)
SELECT ami.id, ait.id
FROM ec2_instance_type as ait, (
    SELECT id
    FROM   amis
    WHERE  image_name LIKE 'amzn-ami-hvm-%'ORDER BY creation_date DESC
    LIMIT 1
) as ami
WHERE  ait.instance_name = 't2.micro';

Как вам идея?

Музыкальная пауза.

5 ноября выйдет "новый" альбом Radiohead - Kid A Mnesia
В кавычках, потому что это переиздание Kid A и Amnesiac + неизданное.

А пока можно послушать свежую "If You Say The Word"
где суровые мужики собирают по лесам заблудившихся клерков :)

PS: еще они замутили что-то c epic games. Но на игру не похоже...

Как выглядел бы sql если бы его придумали в 2021

select:
  items:
    - foo
    - bar
  where:
    - eq:
      - foo
      - bar

из нашего чата

Как заменить DISTINCT на recursive CTE с пользой:

https://www.depesz.com/2021/09/27/using-recursive-queries-to-get-distinct-elements-from-table/

Вы знаете какого доклада там не будет... :)


Программа конференции по дата-инжинирингу SmartData 2021 уже готова! Начинаем 11 октября 🔥

И не просто готова — в ней десятки крутейших докладов от спикеров со всего мира. Например:
✔️ Andy Pavlo, "Using Machine Learning to Automatically Optimize Database Configurations";
✔️ Tejas Chopra, "An experience report on strategies for working with Cloud Storage";
✔️ Владимир Озеров, "Архитектура высокопроизводительных распределенных SQL-движков";
✔️ Дмитрий Бугайченко, "Рабочее место D-people - опыт СБЕР".

И это только маленькая часть программы — в ней еще десятки докладов. Заходите на сайт конференции за подробностями и билетами.

И не забывайте, что по промокоду nosingularity2021JRPc еще можно успеть купить Personal Standard билет со скидкой.
До встречи на SmartData👋

В честь выхода Дюны, я так понимаю…

https://github.com/authzed/spicedb/releases/tag/v1.0.0

Мама, я в телевизоре…

Канал перевалил за 1000 подписчиков, очень радостное для меня число! Пока вы не успели отписаться, сделаю стандартный телеграм-пост с каналами об ИТ, которые я читаю.

* @oleg_log, @oleg_fov — Олег пишет об индустрии. Удивляюсь его продуктивности и читаю, чтобы держать руку на пульсе;
* @bigflatmappa — канал контрибутора ФП-библиотек с историями о том, что он туда контрибутит. Стоит подписаться, чтобы проникнуться духом 10х программирования;
* @yourcybergrandpa — дед ворчит на облака;
* @architect_says — дед ворчит на Agile;
* @nikitonsky_pub — Никита Прокопов ворчит на всё вокруг;
* @nosingularity — о базах данных и инструментарии для них;
* @dereference_pointer_there — личный блог без чётко очерченной тематики (но частенько про Rust);
* @pmdaily — о продуктовой разработке и взаимоотношениях программиста с бизнесом;
* @scala_channel_ru — важные новости и анонсы из мира Scala;
* @daily_ponv — в основном ссылки на сложные пейперы;
* @shark_in_it — резюме пейперов о распределённых системах и базах данных;
* @scalabin — Антон давно ничего не писал, но если вдруг напишет — точно будет интересно;
* @consensus_io — о распределённых системах.

И конечно же чаты самого дружелюбного в мире сообщества, в котором высококвалифицированные специалисты помогают всем желающим стать 10x скалистом:

@scala_ru, @scala_learn, @scala_jobs, @ru_zio, @akka_ru

​На днях вышел Postgresql 14 (подробности о новинках тут).

Но я пока продолжу сидеть на 12 версии...

Во-первых, потому что у меня managed база в облаке, а там все происходит небыстро (хотя в azure, говорят, уже есть)

А во-вторых, есть еще одна проблема, о которой я хотел бы сегодня рассказать.
Проблема называется error messages.

В Postgresql сообщения об ошибках далеки от идеала. Ты получаешь код ошибки и текстовое описание, в которое засунули sprintf'ом все нужные константы.
Т.е. если твой запрос может выкинуть один и тот же код ошибки по разным причинам, то единственное, что остается, это ассертить строку с ошибкой.

null value in column "column1" of relation "table1" violates not-null constraint 

Круто бы было, если бы "column1" и "table1" возвращались в отдельных параметрах, но жизнь такова, какова она есть, и больше ни какова.

Отдельная интересная задача - узнать все возможные сообщения об ошибках статическими методами, по тексту запроса. Такое когда-то планировалась сделать в рамках holistic.dev ...

Так вот, от версии к версии формат сообщений об ошибках тоже может меняться!
И если вам важна причина возникновения ошибки, то привет. Нужно переделывать все ассерты:

-- pg 13
null value in column "column1" of relation "table1" violates not-null constraint


-- pg 12
null value in column "column1" violates not-null constraint

Возможно, вы хотите сказать: "какая нафиг разница какая ошибка, падай в 500 да и все".
Но это только потому, что вы не видели моих запросов :)

​В мою подборку необычных применений SQL добавился еще один тул для git

askgit:

SELECT count(*) FROM commits WHERE author_email = 'user@email.com'