Почему запрос с ВЫБРАТЬ ПЕРВЫЕ и ОБЪЕДИНИТЬ ВСЕ возвращает разный результат?

Разберем типичную, но неочевидную проблему, с которой сталкиваются разработчики при написании сложных запросов в платформе 1С:Предприятие. Ситуация заключается в том, что при попытке объединить выборки с ограничением количества записей ВЫБРАТЬ ПЕРВЫЕ и сортировкой УПОРЯДОЧИТЬ ПО результат запроса оказывается непредсказуемым или отличается при сохранении во временную таблицу по сравнению с прямой выборкой.

Проанализируем исходную структуру запроса, чтобы понять корень проблемы.

ВЫБРАТЬ
    1 КАК Поле1
ПОМЕСТИТЬ вт
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 2
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 3
// ... заполнение таблицы значениями от 0 до 9
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ ПЕРВЫЕ 5
    вт.Поле1 КАК Поле1
ПОМЕСТИТЬ ви2
ИЗ
    вт КАК вт

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ ПЕРВЫЕ 5
    вт.Поле1
ИЗ
    вт КАК вт
УПОРЯДОЧИТЬ ПО
    Поле1 УБЫВ
;

Причины нестабильного поведения запроса

Рассмотрим подробнее, почему СУБД возвращает неожиданные данные при таком синтаксисе:

1. Область действия секции УПОРЯДОЧИТЬ ПО при объединении запросов. В синтаксисе языка запросов 1С предложение УПОРЯДОЧИТЬ ПО, расположенное в самом конце блока с операторами ОБЪЕДИНИТЬ или ОБЪЕДИНИТЬ ВСЕ, применяется исключительно к итоговому результату всего объединения, а не к каждой объединяемой ветке по отдельности.
2. Недетерминированность выборки первых строк без локальной сортировки. Конструкция ВЫБРАТЬ ПЕРВЫЕ N без явно заданной сортировки внутри конкретной выборки выбирает произвольные N строк. СУБД считывает данные в том физическом порядке, в котором они хранятся на диске или в буфере памяти.
3. Разница в планах выполнения при использовании ПОМЕСТИТЬ. Когда запрос сохраняется во временную таблицу (ПОМЕСТИТЬ ви2), СУБД строит план выполнения для операции вставки (INSERT INTO), который кардинально отличается от плана обычной выборки данных (SELECT). В результате первая ветка запроса без явного порядка может взять один набор строк при создании временной таблицы и совершенно другой — при прямом выводе клиенту.
4. Отсутствие гарантированного порядка во временных таблицах. Реляционная таблица сама по себе представляет собой неупорядоченное множество. Даже если исходная выборка была отсортирована перед помещением во временную таблицу, последующий запрос ВЫБРАТЬ ви2.Поле1 ИЗ ви2 без собственной секции УПОРЯДОЧИТЬ ПО не гарантирует сохранение исходного порядка строк.

Правильное решение: использование вложенных запросов

Выясним, как правильно изолировать сортировку для каждой ветки объединения. Платформа 1С не позволяет указывать секцию УПОРЯДОЧИТЬ ПО внутри отдельной ветки объединения на верхнем уровне. Чтобы ограничение ВЫБРАТЬ ПЕРВЫЕ гарантированно отбирало нужные данные с сортировкой, каждую ветку необходимо обернуть во вложенный запрос.

Посмотрим на исправленный и гарантированно рабочий вариант запроса:

ВЫБРАТЬ
    Вложенный1.Поле1 КАК Поле1
ПОМЕСТИТЬ ви2
ИЗ
    (ВЫБРАТЬ ПЕРВЫЕ 5
        вт.Поле1 КАК Поле1
    ИЗ
        вт КАК вт
    УПОРЯДОЧИТЬ ПО
        вт.Поле1 УБЫВ) КАК Вложенный1

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Вложенный2.Поле1
ИЗ
    (ВЫБРАТЬ ПЕРВЫЕ 5
        вт.Поле1 КАК Поле1
    ИЗ
        вт КАК вт
    УПОРЯДОЧИТЬ ПО
        вт.Поле1 УБЫВ) КАК Вложенный2
;

Обязательная сортировка при финальной выборке

Разберем заключительный шаг. Если нам необходимо получить данные из временной таблицы в строго определенном порядке, мы обязаны явно указать секцию УПОРЯДОЧИТЬ ПО в финальном запросе пакета:

ВЫБРАТЬ
    ви2.Поле1 КАК Поле1
ИЗ
    ви2 КАК ви2
УПОРЯДОЧИТЬ ПО
    Поле1 УБЫВ;

Главное правило: при использовании конструкции ВЫБРАТЬ ПЕРВЫЕ всегда используйте УПОРЯДОЧИТЬ ПО в рамках того же уровня вложенности запроса, а при извлечении строк из временных таблиц всегда задавайте явный порядок вывода.