​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, вы узнаете из следующей главы нашего сегодняшнего дайджеста :)