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

Особенно это касается cloud-only баз, которые могут быть обновлены фактически в момент работы запроса :)
Как минимум по этой причине круто было бы иметь инструмент, который может предупредить об этих особенностях.

Все, что касается PostgreSQL, больше вызывает эмоции "нифига себе, че бывает", в вот с локомотивом аналитических баз Snowflake все намного интереснее...

Я сделал несколько выпусков SQL-TIL, рекомендую:
t.me/nosingularity/607

Но сегодня будет неизданное про Snowflake.
Пожалуйста, дайте знать, если что-то из описанного является нормой и я зря гоню :)

Snowflake "special" behavior:

Никакие ограничения кроме NOT NULL в этой базе не применяются.
По определению PRIMARY KEY = UNIQUE + NOT NULL
Видимо, из-за NOT NULL имеются очень странные сайд-эффекты с PK:

1) Не запрещено делать многоколоночный PK/UNIQUE по одной и той же колонке:

CREATE TABLE t (id INT, CONSTRAINT pk PRIMARY KEY (id, id));

2) Только inline PK гарантирует NOT NULL

CREATE TABLE t1 (id INT PRIMARY KEY);
INSERT INTO t1(id) VALUES (null); -- not ok

CREATE TABLE t2 (id INT, CONSTRAINT pk PRIMARY KEY (id));
INSERT INTO t2(id) VALUES (null); -- ok

CREATE TABLE t3 (id INT);
ALTER TABLE t3 ADD CONSTRAINT pk PRIMARY KEY (id);
INSERT INTO t3(id) VALUES (null);  -- ok

3) Из предыдущего поведения автоматически следует, что многоколоночный PK не будет гарантировать ограничение NOT NULL.
Соответственно (1) так же не будет гарантировать NOT NULL.

4) Даже несмотря на то, что ограничение UNIQUE поддерживается только из-за совместимости со стандартом SQL, Невозможно создать UNIQUE после PK, а наоборот можно.

CREATE TABLE t1 (id INT PRIMARY KEY);
ALTER TABLE t1 ADD CONSTRAINT u UNIQUE (id); -- not ok

CREATE TABLE t2 (id INT UNIQUE);
ALTER TABLE t2 ADD CONSTRAINT pk PRIMARY KEY (id); -- ok

Казалось бы, да и черт с ними, раз UNIQUE все равно не применяется. Да, но теперь нельзя надеяться на NOT NULL, там где вы создали PK. Как тебе такое, Илон?

Тотальная нестрогость во всем
Наверное, плохо, если при загрузке данных из-за не валидных значений загрузка не будет выполнена. Что с этим можно сделать?
Будем молча обрабатывать как получится:

5) Допустимы невозможные параметры функции
Кажется, что с чем чем, но с округлением вопросов быть не должно:
CEIL - вверх
FLOOR - вниз
ROUND - или вверх, или вниз

Но в Snowflake что-то пошло не так...

SELECT CEIL(1::FLOAT, 1.1); -- 1.032626705
SELECT CEIL(1::FLOAT, 1.2); -- 1.009531751
SELECT CEIL(1::FLOAT, 1.5); -- 1.011928851
SELECT CEIL(1::FLOAT, 10); -- 1

SELECT CEIL(1::FLOAT, -1); -- 10
SELECT CEIL(1::FLOAT, -1.1); -- 12.589254118
SELECT CEIL(1::FLOAT, -1.2); -- 15.848931925
SELECT CEIL(1::FLOAT, -1.5); -- 31.622776602
SELECT CEIL(1::FLOAT, -10); -- 10000000000

SELECT FLOOR(1::FLOAT, 1.1); -- 0.9531938817
SELECT FLOOR(1::FLOAT, 1.2); -- 0.9464360167
SELECT FLOOR(1::FLOAT, 1.5); -- 0.9803060747
SELECT FLOOR(1::FLOAT, 10); -- 1

SELECT FLOOR(1::FLOAT, -1); -- 0

Я не представляю, что это значит, но мне почему-то страшно...
Попробуйте поискать эти числа в документации:
https://docs.snowflake.com/en/search.html#q=1.032626705

6) Давайте будем трактовать параметры функции по-разному, в зависимости от того, в какой диапазон эти параметры попадают
https://docs.snowflake.com/en/sql-reference/functions/to_date.html#usage-notes

7) Или давайте трактовать параметры как... как-нибудь!

SELECT next_day(current_date(), 'Friday-night'); -- ok!

Приблизительно так же при использовании COLLATE - там можно все :)

Я понимаю, что часто строгость бывает избыточной. Но представьте, что все эти функции используются в ETL процессах, после которых не узнать как данные выглядели в источнике.
Что мне делать с 1.032626705 после "округления"?

А что мне делать с датами, полученными из TO_DATE на границе перехода параметров?
Или на сколько можно доверять next_day, если в параметр попадет случайный кусок текста?

Вот эти два запроса вернут одинаковый результат:

SELECT next_day(current_date(), a)
FROM (VALUES
        ('friday'), ('fr'), ('fri')
) t(a);

SELECT next_day(current_date(), a)
FROM (VALUES
    ('freedom'), ('frozen'), ('france')
) t(a);

8) Алиасы могут совпадать с ключевыми словами.
Это валидный синтаксис:

SELECT * FROM EMPLOYEES LIMIT LIMIT 1;

9) Совершенно неочевидное автоматическое приведение типов.
Подозреваю, что не один я смущен таким поведением (Snowflake vs PostgreSQL):

-- SNOWFLAKE
SELECT
    typeof(1 % 2), -- INTEGER
    typeof(1.0 % 2), -- INTEGER
    typeof(1 % 2.0), -- INTEGER
    typeof(1 % 1.2), -- DECIMAL
    typeof(4.5 % 1.2) -- DECIMAL
;

-- POSTGRESQL
SELECT
    pg_typeof(1 % 2), -- integer
    pg_typeof(1.0 % 2), -- numeric
    pg_typeof(1 % 2.0), -- numeric
    pg_typeof(1 % 1.2), -- numeric
    pg_typeof(4.5 % 1.2) -- numeric
;

10) Возможно удалить объекты, имеющие зависимые объекты.
Запросто можно удалить таблицу, от которой зависит VIEW. Или таблицу, или у которой есть зависимые STREAMS.
Из VIEW станет невозможно получить данные, а все объекты-сироты будут видны при экспорте DDL.

Я отлично понимаю, что все что происходило на этой неделе тревожит довольно небольшое количество людей в реальной жизни.
Отвечу словами Марти Макфлая из "Назад в будущее-2":
Наверное, вы еще не готовы к такой музыке, но вашим детям она понравится.
:)

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

Смысл работы любого разработчика ПО заключается в уменьшение энтропии :)

Я стараюсь формализовать то, что раньше формализовать не получалось.

И, возможно, некоторым из вас это могло показаться перебором. Для таких я останусь еще одним сумасшедшим дедом :)

​Что может пойти не так с тайпкастом к строке, если вы работаете с несколькими базами?

Выяснилось, что dbt для генерации уникального id берет md5 от объединения приведенных к строкам значений из колонок.

Отдельный сюрприз будет ожидать, если у timestamp указана размерность...

Я тут крутую ORM-ку нашел!..

... для регекспов
... на расте

https://github.com/pemistahl/grex

https://twitter.com/mebeim/status/1373763490465333248?s=21

Продолжаем любить YAML

Геймдев это не только тотальный говнокод, но еще и очень много творческой работы.

Отличная документалка про бекстейдж суперигры Samorost:
https://www.youtube.com/watch?v=6W3kXEGy_GQ
Задроты 80lvl!

Пользуясь случаем напомню историю одного рекламного плаката для моего уже несуществующего интернет-магазина.

И еще пара постов на геймдевную тему:
https://news.1rj.ru/str/nosingularity/469
https://news.1rj.ru/str/nosingularity/570

​У меня новость из личной жизни.
Решил я завязать с этими постгресами, сноуфлейками, стартапами и этим вот всем.
Как говорится, если не можешь предотвратить бардак, то нужно его возглавить!

Короче, я принял оффер...
Теперь я MongoDB Senior Developer Advocate at Developer Relations Team (CEEMEA)

Друзья-стартаперы попросили пофорсить их вакансию:

https://news.1rj.ru/str/profunctor_jobs/1147

Иногда наваливается тупняк.
Но просто сидеть и ничего не делать не позволяет то, что стартап :) Как говорится, "никто, кроме нас".

Тогда приходится браться за какую-то автоматическую работу, которую можно выполнять без мозга.
К сожалению, и такая тоже есть :(

Например, для компилятора SQL-выражений нужно иметь описания всех операторов и функций в базе.

В PostgreSQL это делается просто - SELECT из information_schema и вот тебе все операторы и функции.
Кстати, в PostgreSQL можно делать свои операторы.

В Snowflake такой халявы не случилось, приходится открывать документацию и по документации создавать описание.
Максимально скучное занятие...

Но иногда приходится "просыпаться"...

Прежде чем приводить какие-то примеры стоит рассказать о системе типов в Snowflake.
Там всего десяток основных типов, а все остальное - производные от них.

Например, числовых всего два - NUMBER (фиксированная точка) и FLOAT (плавающая точка).
И все целые типы это NUMBER(38,0). Хочешь что-то этакого - задавай сам.

Зато даты и времени разных аж 5 штук.
А VARCHAR автоматом превращается в VARCHAR(16777216). Очень сильно надеюсь, что они не выделяют физически по 16 килобайт на каждую ячейку...

К слову, в PostgreSQL ШЕСТЬ разных числовых типов, не считая 3 разных SERIAL.

Как задается сложение в PostgreSQL?
integer + integer -> integer
bigint + bigint -> bigint

Как задается сложение в Snowflake?
При сложении двух NUMBER L1.S1 и L2.S2

Leading digits: L = max(L1, L2) + 1
Scale: S = max(S1, S2)
Precision: P = L + S

И надо не забыть проверить, чтобы L не превышал 38.

С.У.К.А.

Ладно, хоть с FLOAT + FLOAT -> FLOAT

Остальные операции задаются в том же духе

Если вдруг операндами будут числа, размерность которых не получится распознать на этапе компиляции (например, числа из JSON, которые имеют динамическую точность), то получится херня :)

На этом месте, я кстати, предвижу очень интересные эффекты в приложениях, которые работают со Snowflake. И скорее всего драйвера для разных языков работают с числами по-разному.

Но это еще не все!
Я же туплю и делаю справочник функций.
Для начала - в описании большей части функций отсутствует описание типов аргументов и результата. Приходится открывать DataGrip и экспериментировать. Собственно так я и обнаружил то странное поведение CEIL и FLOOR

Или вот агрегатные функции MIN и MAX

 The data type of the returned value is the same as the data type of the input values. 

Ага. Щаз!

 [22000][2016] SQL compilation error: Function MIN does not support ARRAY argument type 

Или еще вот такой лихой ход:
Агрегатная функция, которая параметром принимает только константу.
эээ....
Но это еще не все. Обязательно должна быть использована конструкция

 WITHIN GROUP (ORDER BY order_by_expr)

и тип результата зависит от типа выражения order_by_expr ... mic drop ...

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

Вот, кстати, эта красавица

Давайте-ка попробуем что там в качестве константы запихать можно...

 select system$typeof(PERCENTILE_CONT(0.11111111111111111111111111111111111111) within group (order by a))  from t; 

БАДУМС!

 [22023][1015] SQL compilation error: argument 0 to function PERCENTILE_CONT needs to be constant, found 'CAST(0.1111111111111111 AS NUMBER(18,0))' 

Сейчас вот не понял...
Почему, блин AS NUMBER(18,0) ? И почему разрядов после точки в ошибке меньше, чем я указал?
И почему если уменьшить количество разрядов на один, то все работает ?

Подождите расходиться, это еще не все :)

VAR_POP

 The data type of the returned value is NUMBER(precision, scale). The scale depends upon the values being processed. 

спасибо_нахер.jpg

PS: будем считать, что это очередной SQL-TIL :)

​Со всеми этими страртап челенджами забегался и забыл вам рассказать, что Mailru Cloud Solutions запилили нативную интеграцию с holistic.dev!

Теперь пользователям managed PostgreSQL в облаке MCS можно просто в пару кликов добавить расширение "Сервис для оптимизации Postgres (holistic)" в соответствующей вкладке в свойствах нужного инстанса.
Нужно будет указать имя базы, holisticdev api key, имя проекта в holisticdev

После этого синхронизируется схема указанной базы и раз в час будут отправляться запросы из pg_stat_statements, который тоже активируется автоматически.

В плане нативной интеграции Mail.ru оказались впереди планеты всей!

А для YandexCloud, Selectel и SberCloud есть легкие интеграции через FaaS, список которых можно найти здесь:
https://docs.holistic.dev/cloud-service-integrations

Если у вас есть желание и возможность помочь запилить FaaS-интеграции для aws/gcp/azure, постучите, пожалуйста мне: @antonrevyako

​​​Подумалось мне тут, что стоит сделать общий чатик для юзеров snowflake, где можно будет обсуждать всякое. Присоединяйтесь, давайте пошарим экспертизу:

https://news.1rj.ru/str/snowflakedbchat

Поглядываю на разные ключевики в твитере и вот такое попалось по "static analysis".
Может пригодится кому.

Static analysis tool for Android/iOS apps focusing on security issues outside the source code

https://github.com/nightwatchcybersecurity/truegaze

Сегодня какой-то день фронта, извините...

Король разработки оккупировал jsunderhood, а злой фронтенд разразился душной телегой на тему lodash.

И нет повода не напомнить вам про последнего :)

Как мы знаем, sqlite не нужен, но мне больше понравилась сцена с унижением mysql :)

Забыл спросить, зачем SQLite, когда есть Clickhouse-local ? (идите в главу "ClickHouse без сервера")

https://habr.com/ru/company/oleg-bunin/blog/551572/

Не все заинтересованные могли дойти до нашего нового чата, где мы обсуждаем snowflake, поэтому продублирую тут:

Привет. Я с новостями :)

Я как-то рассказывал о проблеме с функцией GET_DDL в Snowflake.
Она имеет одну особенность, которая не позволяет использовать результат ее работы по назначению:
объекты сортируются по алфавиту.

Например:

CREATE TABLE B (ID INT PRIMARY KEY);
CREATE TABLE A (ID INT REFERENCES B(ID));
SELECT GET_DDL('DATABASE', 'TEST', TRUE);

Вернет

create or replace TABLE TEST.PUBLIC.A (
  ID NUMBER(38,0),
  foreign key (ID) references TEST.PUBLIC.B(ID)
);
create or replace TABLE TEST.PUBLIC.B (
  ID NUMBER(38,0) NOT NULL,
  primary key (ID)
);

Этот DDL-скрипт не может быть выполнен из-за неправильного порядка объектов.

Мы сделали инструмент, который пересортирует объекты с учетом их связанности.
Применимо для всех объектов Snowflake (включая VIEW), понимает CREATE и ALTER, учитывает FOREIGN KEYS, CLONE, SELECT AS и другие виды зависимостей.

Умеет определять циклические зависимости.

Можно использовать онлайн без регистрации
https://parsers.dev/tools/ddl/reordering

или при помощи API:
https://docs.parsers.dev/#snowflake-get_ddl-function-result-reorder


Если у вас будут какие-то задачи, которые вы хотели бы автоматизировать, пишите тут. Попробуем решить :)

Самый амбициозный кроссовер (с)

https://github.com/fdietze/postgresqlite

Шах и мат sqlit'исты!

У меня есть проблема - я хочу все делать хорошо...

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

Конечно же, есть огромные шансы всем дружно "сделать хорошо, но никогда, чем плохо, но сейчас", но я стараюсь не :)

У меня было некоторое количество оффлайн проектов. Я уже как-то рассказывал как мой старинный друг делал плакат для интернет-магазина.

А еще была кофейня. После нее с я оффлайном завязал окончательно, но рассказ сегодня не об этом :)

Ко всему в этом проекте был серьезный подход - меню, подбор зерна, название, брендинг, фотографии блюд, ремонт.

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

И вот сегодня утром присылают мне фотку из кофейни Новосибирска с логотипом "моей прелести"...

Реакция была, конечно, неоднозначная.
С одной стороны - хорошие вещи постоянно тырят. (Поэтому все продукты теперь я буду делать только SaaS :) )

С другой... Как же убого у них выглядит все остальное!
Я заморачивался тематическим обновлением логотипа на наклейках на стаканы к разным праздникам. Аккаунты в соцсетях были оформлены особым образом и много чего еще!

А тут... Срамота.
Они сперли лого и детали оформления, но не взяли самого важного - отношения к своему делу :(
И, конечно же, продают франшизу.

Название воровать не стали, придумали свое - "Coffee Krok"
¯\_(ツ)_/¯

Здесь можно бы было написать пару стейтментов в духе "идеи ничего не стоят, стоит реализация". Или повторить то, что я уже писал в статье про интернет-магазин, о том, что только то, что делаешь с душой, приносит настоящее удовольствие.
Но это вы все и без меня знаете.

И да, будете в Новосибе, не ходите в эту кофейню...

yes.jpg