я знал, конечно, что японцы не с этой планеты, но ...

https://news.1rj.ru/str/CGIT_Vines/714

​Пятничный SQL-WTF
Понедельничный SQL-TIL

"Хочешь разобраться в чём-то - напиши статью"

Когда я начинал работать над текущим проектом, я программировал уже около 25 лет и с базами данных работал больше 20 из них.
Казалось, что я видел большую часть проблем, связанных с разработкой на SQL.
Но стандарт SQL и частные его реализации не перестают удивлять меня до сих пор.

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

Скорее всего вы никогда не столкнетесь с таким синтаксисом в реальной жизни. Но зато сможете блеснуть эрудицией перед своими коллегами :)


1 wtf из 5 - пустые таблицы
Можно создать таблицу совсем без колонок. Или удалить все колонки из таблицы. И это не ошибка.
Зачем это может быть нужно я придумать не смог, но на всякий случай у нас об этом есть notice :)


2 wtf из 5 - системные колонки и зарезервированные слова
В каждой таблице в PostgreSQL есть специальные колонки tableoid, xmin, cmin, xmax, cmax, ctid, которые создаются автоматически и содержат системную информацию о строках.
Создавать свои колонки с такими именами запрещено. Значения из этих колонок можно читать, но изменять ни значение, ни сами колонки нельзя.
Но если про системные колонки база вам любезно сообщит, то использование зарезервированных слов без кавычек вызывает ошибку парсинга и не всегда можно понять что вообще происходит.

Валидно

CREATE TABLE "all" (a INT);

Не валидно

CREATE TABLE all (a INT);

Это причина, по которой все ORM генерируют запросы с кавычками для имен таблиц и колонок.
Но, что и предсказуемо, ORM ничего не знает про системные колонки и ошибку вы получите уже в рантайме применения миграции.

Все это может показаться ерундой, но наверняка много страданий принесет при необходимости в переезде с одной базы на другую. Например, в процессе импортозамещения.
В идеале стоило бы избегать зарезервированных имен из всех стандартов SQL. И у есть нас соответствующий notice.


3 wtf из 5 - сокращенный синтаксис для SELECT * FROM tbl
Можно короче? Да изи!

TABLE tbl

Никаких дополнительных условий этот синтаксис не предполагает.


4 wtf из 5 - SELECT tbl FROM tbl
Если в таблице нет поля, имя которого совпадает с именем таблицы, то следующие два запроса будут эквивалентны

SELECT tbl FROM tbl;
SELECT ROW(tbl.*) FROM tbl;

И даже больше!

SELECT * FROM tbl1, tbl2 WHERE tbl1 = tbl2;
SELECT * FROM tbl1, tbl2 WHERE ROW(tbl1) = ROW(tbl2);

ROW() - создает тип RECORD. Это кортеж, содержащий значение всех колонок по очереди их следования в источнике данных.
Возможно не все знают, но такие кортежи можно сравнивать.
Это очень удобно, когда нужно сравнить попарно несколько наборов значений.

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


5 wtf из 5 - замена имен полей в подзапросах
Если подзапрос возвращает одно поле (field1) и это поле сравнивается во внешнем условии с полем field2, то из первого подзапроса можно вернуть поле с именем field2:

WITH a(field1) AS (
    VALUES(1)
),
b(field2) AS (
    VALUES(1)
)
SELECT * FROM b WHERE field2 IN (SELECT field2 FROM a)

oO

Такого поворота не ожидал даже я...
Мы об этом пока не предупреждаем, но обязательно будем.

Это, конечно же, не все, чем я могу вас удивить :) Продолжение в следующих сериях :)

​Пока подутих шум вокруг срача Epic с Apple, предлагаю вам посмотреть видос про Flash-игры.

Flash это та технология, которая заставляла компы крутить вентиляторами при заходах на страницы с большим количеством рекламы.
Это та технология, которую использовал youtube в первые 5 лет своего существования.
Это та технология, на которой были сделаны все игрушки в vk.
Это та технология, которая дала миру стриминговый протокол RTMP.
Это та технология, которую Джобс так и не пустил на Ios.


Я тоже занимался разработкой игр на Flash. Причем начинал еще тогда, когда во встроенном языке программирования не было массивов :)
Массивы эмулировались через составные имена переменных - varname = 'arr_'+1+'_'+1;
Это было еще в прошлом веке, до того как компанию Macromedia купил Abobe.
Позже, когда язык стал более функциональным, мы стали делать красочные и динамичные игрушки, которые позволяли нам зарабатывать довольно приличные по тем временам деньги :)

2020 станет последним годом для Flash. Хотя многие из нас уже не помнят, когда в последний раз видели Flash-игры, но все-таки немного жаль, что все так заканчивается.

Press F to pay respect.

https://www.youtube.com/watch?v=UJcPoNBuxlE

PS: спасибо старому боевому flash-товарищу Олегу https://news.1rj.ru/str/ITmoonIT за ссылку :)
PPS: картинка врет, флеш существует с 1996 года.

Как продавать в микрософт:


https://news.1rj.ru/str/devfounder/63

«Особое мнение» на минималках.

PS: у palantir скоро IPO


https://twitter.com/abebab/status/1301260094051676165

Фейсбучные меморизы помогают мне понять, что ФБ теряет часть комментов - возможно, с концами. Вот пример: на скрине недавнего поста человек отвечает отсутствующему мне на вопрос "какие обстоятельства заставляют вас так считать"?" Это не настройки безопасности, третьи стороны эти комменты тоже не видят. В лучше случае, связка сообщений и базы нарушена, в худшем - часть базы грохнулась без бекапа. И это одна из крупнейших технокомпаний с десятками тысяч инженеров! А колективные руки - из жопы...

​1/2
Пятничный SQL-WTF #2
Понедельничный SQL-TIL #2

Первый выпуск был принят довольно тепло, поэтому я продолжу делиться с вами несколькими внезапными синтаксическими конструкциями SQL и неочевидным поведением.
Как выяснилось, моё допущение о том, что описанное поведение повторится в любой базе, оказалось неверным. Поэтому, на всякий случай предупрежу, что все эксперименты проводились в PostgreSQL.
Btw, есть хорошая табличка сравнения поддержки синтаксиса SQL в разных базах.

Скорее всего вы никогда не столкнетесь с таким синтаксисом в реальной жизни. Но зато сможете блеснуть эрудицией перед своими коллегами :)


1 wtf из 5 - implicit typecast
Неявное приведение типов используется, как мне кажется, во всех современных языках программирования.
В SQL вы тоже довольно часто с этим сталкиваетесь. Например

SELECT NOW() > '2020-12-31'

Так же почти все знают, что true может быть автоматически приведено к 1, а false к 0 в большинстве языков программирования. И в обратную сторону - число > 0 всегда приводится к true, 0 приводится к false.
В этом нет ничего неожиданного, но будет полезно знать, что существуют константы, которые могут быть неявно приведены к другому типу, хотя при этом не являются валидными значениями с точки зрения целевого типа:

SELECT 'today' :: DATE;

Есть несколько строковых констант, которые можно скастить к DATE или TIMESTAMP - 'yesterday', 'today', 'now', 'tomorrow' и 'epoch'
К TIME и TIMESTAMP можно скастить 'now' и к TIME можно скастить 'allballs'.
Так же есть специальные константы, обозначающие бесконечно далекие точки в будущем и прошлом: 'infinity', '-infinity'.
Радует, что бесконечности можно сравнивать через обычный знак равенства :)

Так же к BOOLEAN можно привести целые числа, строки 'true'/'false', 't'/'f', '1'/'0', 'yes'/'no', 'y'/'n', 'on'/'off'


2 wtf из 5 - nested rows
Этот wtf вытекает из четвертого wtf в прошлом выпуске. Т.к. теперь подобный синтаксис больше не вызывает недоумения, во втором выпуске я решил понизить его до 2 wtf из 5.
Эти два запроса будут эквивалентны:

SELECT (t1, t2) from t1, t2
SELECT (ROW(t1.*), ROW(t2.*)) from t1, t2

Что может пойти не так?
Все верно, но PostgreSQL не умеет работать со вложенными ROW(). ROW внутри ROW сериализуется в строку. Поэтому

SELECT ROW(ROW(1,2), ROW(3,4))

вернет ROW с двумя строками внутри:

("(1,2)","(3,4)")

а не то, что мы могли бы ожидать.

holistic.dev умеет не только предупреждать вас об изъянах в схеме и запросах, но и определять типы результата запроса. В playground'е типы находятся в разделе Export result внизу страницы.
Типы так же можно будет получить через API и использовать эту информацию, например, для автотестов в CI.


3 wtf из 5 - nested rows nullability
Внезапные эффекты можно получить, сравнивая ROW на IS NULL. Да, ROW будет IS NULL, если все его элементы будут NULL. Но что будет с вложенными ROW?

SELECT
(NULL) IS NULL, -- true
(NULL, (NULL)) IS NULL, -- true
(NULL, NULL, NULL) IS NULL, -- true
(NULL, (NULL, (NULL))) IS NULL  -- false

На самом деле это предсказуемое поведение, т.к. в последней строке PostgreSQL сериализует вложенный ROW в строку. Почему такого же не происходит во второй строке?
ROW c одним NULL - элементом не сериализуется в строку.
И тут появляется еще одна тонкость. Хотя ROW(NULL) в качестве результата вернет ROW без элементов, на самом деле результат не является тем, чем кажется :) Следующий запрос выполнится с ошибкой

SELECT ROW(NULL) = ROW()

т.к. мы пытаемся сравнивать между собой ROW с разным количеством элементов.
Тогда что за фигня происходит? Типы результата смогут приоткрыть завесу тайны :)
А что такое UNKNOWN, вы узнаете из следующей главы нашего сегодняшнего дайджеста :)

​2/2
Пятничный SQL-WTF #2
Понедельничный SQL-TIL #2

4 wtf из 5 - NULL
В PostgreSQL есть два типа NULL - типизированный и не типизированный...
Звучит как какой-то треш :)
Но если вы знакомы с функциональным программированием, то эту конструкцию можно сравнить с монадой maybe.

Объясняется это довольно просто. Если у вас есть таблица с nullable полем, то при выборке из таблицы NULL будет того же типа, что и колонка.
Но какой тип будет у NULL в следующем выражении:

SELECT NULL

Вот и PostgreSQL не знает...
Поэтому для тех случаев, когда тип не ясен, полю будет присвоен специальный тип - UNKNOWN
Такой же тип вы получите для неопределенного булева выражения:

SELECT
  NULL IS UNKNOWN, -- true
  (1 = NULL) IS UNKNOWN -- true

Вы же знаете, что проверку на NULL нельзя делать через оператор =, а нужно использовать IS NULL?

Но это еще не все. Если UNKNOWN возвращается из подзапроса, то он обретает тип:

SELECT
    NULL, -- unknown
    (SELECT NULL) -- text

Почти такая же история, как и с вложенным ROW().

Наш парсер знает о таком поведении.


5 wtf из 5 - updatable views
Многие из нас используют представления (VIEW) в своих базах. Но мало кто знает, что с представлениями могут быть использованы команды INSERT, UPDATE и DELETE.
Не со всеми и не всегда, но могут. Для этого представление должно быть создано с определенными ограничениями, главное из которых - оно должно иметь только 1 источник данных.

Кому и зачем это может быть нужно?

Для начала нужно помнить, что если у таблицы, над которой сделали VIEW, переименовать столбец, то ничего не сломается. VIEW будет иметь старое имя столбца и при этом ссылаться на переименованный:

CREATE TABLE t (a INT);

CREATE VIEW t_view AS SELECT a from t ;

ALTER TABLE t RENAME COLUMN a TO b;

Но это еще не все. Если мы по каким-то причинам не можем обновлять приложение при изменении схемы базы, то мы можем изменять данные в целевых таблицах через представления. Если при этом изменятся имена колонок в таблице, то во VIEW все останется как было. Т.е. для таблицы, описанной выше, сработает:

UPDATE t_view SET a = 1 WHERE a = 2;

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

​https://twitter.com/ryanchenkie/status/1303781123546324994

Пикантности добавляет то, что чувак работает в GraphQL Dev Rel в prisma :)

Повезло кому-то с работой...

TLDR: как сджойнить 1 миллион таблиц

К слову, в настройках PostgreSQL есть параметр join_collapse_limit, который отвечает за оптимизацию джойнов. По умолчанию он равен 8. Так вот, если в запросе джойнится больше 8 элементов, оптимизатор говорит, что у него лапки и просто джойнит все по очереди. У нас на этот случай, конечно же, есть warning :)

https://twitter.com/postgresql_007/status/1304308970790813703

​Пятничный SQL-WTF #3
Понедельничный SQL-TIL #3

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

Скорее всего вы никогда не столкнетесь с таким синтаксисом в реальной жизни. Но зато сможете блеснуть эрудицией перед своими коллегами :)

1 wtf из 5 - implicit and explicit record
Под прошлым выпуском часто голосовали "так никто не пишет", когда я рассказывал о тонкостях использования ROW. Очень даже пишут :) В частности сравнение ROW() позволяет прилично сократить количество писанины и одновременно уменьшить вероятность ошибки:

UPDATE t 
SET (a,b) = (1,2)
WHERE (a,b) = (0,0)

или даже так:

SELECT ROW(1,2) IN (SELECT a,b FROM t)

Есть два способа объявления ROW - явный, в виде

ROW()

и неявный в виде

()

Так вот, неявный способ не работает для одного параметра, конструктор просто игнорируется:

SELECT pg_typeof(ROW(1)), pg_typeof((1))
-- record, integer

И соответственно, сравнить их между собой не удастся.

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


2 wtf из 5 - IS [NOT] DISTINCT FROM
Вы наверняка знаете, что результат сравнения с NULL дает NULL. Не true или false, а NULL:

SELECT * FROM t
WHERE a = NULL

Не вернет ни одной записи. Конечно же на этот случай у нас есть правило :)
Для проверки на NULL используется конструкция

IS [NOT] NULL

Если мы хотим сравнить 2 колонки, каждая из которых может принимать значение NULL и при этом нас устроит, что NULL = NULL, мы будем вынуждены сделать так:

SELECT * FROM t1, t2
WHERE 
    t1.a = t2.a OR t1.a IS NULL AND t2.a IS NULL
    AND
    t1.b = t2.b OR t1.b IS NULL AND t2.b IS NULL

Писать не удобно, читать еще хуже... Но есть выражение, которое может сократить запись:

SELECT * FROM t1, t2
WHERE (t1.a, t1.b) IS NOT DISTINCT FROM (t2.a, t2.b)

Само собой, оно работает и для сравнивания колонок и значений, а не только типов record.


3 и 4 wtf из 5 - точность формулировки функциональных индексов
В рейтинге частотности правил SQL анализатора holistic.dev на третьем месте оказалось архитектурное правило de-morgan-laws, и я приводил пример во что раскрывается выражение

NOT (a IS TRUE)

и почему оно не эквивалентно

a = false

При использовании функциональных индексов, база производит сравнение точного выражения с константой, записанной в индексе и не пытается ничего вычислять.
However, the index expressions are not recomputed during an indexed search, since they are already stored in the index.

Если помнить об этом, то можно сильно сэкономить время, нервы и деньги. Пример, иллюстрирующий ситуацию:
Особенности использования индексов в PostgreSQL

Заголовок не отражает суть проблемы, т.к. никакой специфики Postgresql тут нет - так работает везде.

VALUE IS NOT NULL

эквивалентно

(VALUE IS NULL) = false

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

Хотел подкрепить свои слова в демке совершенно убойного проекта Cosette, который выводит математическую эквивалентность двух SQL выражений, но он не понимает IS NULL :(

В holistic.dev мы еще не закончили работу над системой, которая будет понимать сможет ли запрос использовать один из существующих индексов. Но мы активно над этим работаем :) В одном из следующих выпусков я расскажу что происходит при парсинге выражений WHERE и ON. Это очень непросто :)


5 wtf из 5 - views with check option
Помните, в прошлом выпуске историю про updatable views?
Так вот, это еще не конец :)
Вьюхи можно создавать так, чтобы при вставке или изменении через них возникало исключение, если добавляемые данные не соответствуют условию фильтрации внутри самой вьюхи! Просто добавьте WITH CHECK OPTION после описания запроса при создании :)

я знаю, чем все будут заниматься ближайшие дни...


https://toonify.justinpinkney.com/