пятница, 11 октября 2013 г.

Где кончается SELECT?

Тема, которая сегодня неожиданно возникла, удивила и заставила задуматься о вещах, которые ранее воспринимались совершенно утилитарно.
Ну кто не пользовался оператором SELECT? - Все пользовались. А вот где он кончается и начинается следующий? ;-)
То, что я скажу далее — очень спорно, об этом нигде (в известных мне местах) не говорится, и далее следует исключительно мнение автора, т. е. моё личное.

Сейчас занимаемся представлением SQL в виде набора объектов, описывающих элементы этого языка. Зачем это нужно? - Ну, диалектов SQL очень много, поэтому разбор SQL затруднён его спецификой, кроме того требуется проводить верификацию запроса на предмет попытки доступа к тому, к чему у пользователя доступа нет, автоматически вносить в условия запроса дополнительные ограничения на доступ к данным.
Да много для чего это может потребоваться.
При создании объектов логично полагаться на диаграммы, описывающие этот язык. Каждую конструкцию можно отразить соответствующим объектом. Такая структура запроса позволяет дополнительную обработку и по ней очень удобно потом генерировать итоговый SQL, который и будет отправлен СУБД на исполнение.
В принципе, в диаграммах, о которых я говорю, всё есть :-) Но есть ещё и человеческий фактор, о проявлении которого хочу сейчас рассказать. Я говорю о себе, как о человеке, которому приходится иметь дело с SQL разной сложности каждый день. Так получается что в конструкции GROUP BY и ORDER BY начинают восприниматься как элементы оператора SELECT. Т.е. конструкции, которые появляются в контексте этого оператора.
Проблема в том, что «элементы оператора» и «появляются в контексте оператора» - это не операционный язык в данном случае. Т.е. терминология, не точно описывающая предмет или явление.
Действительно, давайте бросим взгляд на то, как оператор SELECT определяется в ORACLE.

Ну, казалось бы, всё совершенно понятно, из блистательного описания с однозначностью вытекает, что когда я пользуюсь SELECT, к моим услугам и FROM, и WHERE, и GROUP BY и ORDER BY, если мне потребовалась сортировка.
Так ли это? - Да, почти :-) До тех пор, пока меня не угораздит воспользоваться UNION, INTERSECT или MINUS. Вот здесь возникает интересное обстоятельство, заключающееся в том, что GROUP BY, к примеру, относится к SELECT, а ORDER BY – относится к его результатам. Звучит как масло масляное? ;-) - Да, я понимаю.

Давайте рассмотрим простейший пример, запрос практически бессмысленный, но он очень простой, над ним и поэкспериментируем. Кстати, если хотите проверить, что я не вру, воспользуйтесь Oracle SQL Developer. Даже если у Вас в БД нет таблицы INVOICES (которую я только что придумал — у меня её тоже нет, зато есть другая таблица, на которой проводились эксперименты) это Вам не помешает, поскольку критерием успешности будет сообщение Oracle об ошибке, что объекта (например INVOICES) в БД не существует.
SELECT INVOICES_0.id, INVOICES_0.unit_price
FROM INVOICES INVOICES_0
WHERE INVOICES_0.amount > 500
UNION ALL
SELECT INVOICES_1.id, INVOICES_1.unit_price
FROM INVOICES INVOICES_1
WHERE INVOICES_1.amount < 300
ORDER BY id;
Это выглядит очень просто и это работает так, как ожидается.
Давайте теперь расставим скобки, чтобы явно очертить каждый из запросов, участвующих в выборке:
(SELECT INVOICES_0.id,
  INVOICES_0.unit_price
FROM INVOICES INVOICES_0
WHERE INVOICES_0.amount > 500
)
UNION ALL
  (SELECT INVOICES_1.id,
    INVOICES_1.unit_price
  FROM INVOICES INVOICES_1
  WHERE INVOICES_1.amount < 300
  )
ORDER BY id;
И здесь всё хорошо, и всё работает. Правда, ORDER BY относится уже не к конкретному SELECT (их два), а скорее — к UNION ALL, объединяющему две выборки.
Что будет, если я попытаюсь внести ORDER BY в первую скобку (или вторую — совершенно несущественно):
(SELECT INVOICES_0.id,
  INVOICES_0.unit_price
FROM INVOICES INVOICES_0
WHERE INVOICES_0.amount > 500
ORDER BY INVOICES_0.id
)
UNION ALL
  (SELECT INVOICES_1.id,
    INVOICES_1.unit_price
  FROM INVOICES INVOICES_1
  WHERE INVOICES_1.amount < 300
  )
- Ничего хорошего.
ORACLE скажет что-то вроде:
ORA-00907: отсутствует правая скобка
00907. 00000 -  "missing right parenthesis"
Error at Line: 5 Column: 1
Легко убедиться, что ошибка возникает на ORDER BY. Но как же так? - Что я делаю неправильно? - Наверное, следует предположить, что синтаксически ORDER BY может относиться не к подзапросам, соединяемым посредством UNION, а к результату этого соединения.

Таким образом, конструкцию SELECT, на мой взгляд, было бы уместнее определить так:
SELECT sub_select_query [UNION clause|INTERSECT clause|MINUS clause] [ORDER BY clause]...
Где sub_select_query :: columns_clause [FROM clause] [WHERE clause] [GROUP BY clause] … но не ORDER BY clause!

Т.е., отвечая на вопрос поста "Где кончается SELECT?" я бы сказал, что он (SELECT) заканчивается на одной из конструкций: UNION, INTERSECT или MUNUS. Всё, что может следовать за ними, к SELECT не относится, а относится к результатам его выполнения (выборка, группировка, объединение и т.д.).

Вам это было и так известно (очевидно)? - Я Вам завидую.
Вам известны СУБД, где это не так? - Я с интересом жду Ваших комментариев.
-----

2 комментария :

  1. Испольуйте правильные СУБД :) Я уже достаточно давно утверждаю, что Oracle какой-либо логичностью обычно не заморачивается (как в описании ошибок, так и при выполнении запросов). Кстати, есть же стандарт (последняя версия аж SQL2011), как там трактуется такой запрос?
    ------
    В postgres выполнил запрос: (SELECT INVOICES_0.id,
    INVOICES_0.username
    FROM auth_user INVOICES_0
    WHERE INVOICES_0.last_login < '2012-01-01'
    ORDER BY INVOICES_0.id
    )
    UNION ALL
    (SELECT INVOICES_1.id,
    INVOICES_1.username
    FROM auth_user INVOICES_1
    WHERE INVOICES_1.last_login > '2013-01-01'
    )
    результат - ожидаемая выборка.
    ------
    MySQL тоже выполнился:
    (SELECT INVOICES_0.id,
    INVOICES_0.Name
    FROM Teachers INVOICES_0
    WHERE INVOICES_0.Cost > 2000
    ORDER BY INVOICES_0.id DESC
    )
    UNION ALL
    (SELECT INVOICES_1.id,
    INVOICES_1.Name
    FROM Teachers INVOICES_1
    WHERE INVOICES_1.Cost < 300
    )

    ОтветитьУдалить
  2. Спасибо Виктор за содержательный ответ.
    Как раз сейчас учитываем то, что артефакт для Oracle для *нормальных* СУБД - штатная ситуация :-)

    «В postgres выполнил запрос:»
    -- Ну, это же PostgreSQL... :-) Признаюсь, испытываю к нему плохо скрываемую слабость...

    ОтветитьУдалить