​Пятничный 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 после описания запроса при создании :)