Почему в запросе 1С падает группировка на СУБД Oracle и как это исправить?

← На главную

Разработчики 1С нередко сталкиваются с ситуацией, когда сложный запрос без проблем выполняется в файловой базе данных или на сервере MS SQL Server, однако при переносе информационной базы на СУБД Oracle завершается критической ошибкой СУБД. Чаще всего проблема кроется в блоке СГРУППИРОВАТЬ ПО (GROUP BY), особенно если в выборке используются сложные условия ВЫБОР КОГДА, составные типы данных или проверки типов.

Проанализируем детально, почему возникает такое поведение платформы, как транслятор 1С генерирует нативный SQL для Oracle и какими способами можно гарантированно обойти эту проблему.

Причины возникновения ошибки на стороне Oracle

Выясним причину различий в поведении СУБД. В стандарте SQL и, в частности, в реализации оптимизатора Oracle действует строгое правило (ошибка ORA-00979: not a GROUP BY expression): каждое выражение в секции SELECT, не являющееся агрегатной функцией (такой как СУММА, МИНИМУМ, МАКСИМУМ, КОЛИЧЕСТВО), обязано в точности совпадать с выражением, указанным в секции GROUP BY.

В платформе 1С:Предприятие для каждого типа СУБД используется собственный механизм трансляции внутреннего языка запросов в нативный диалект SQL. При обработке сложных выражений возникают следующие нюансы:

Поля составного типа данных: В физической структуре таблиц 1С реквизит составного типа раскладывается на несколько системных колонок (признак типа, идентификатор таблицы, ссылки и значения). Если в секции ВЫБРАТЬ и СГРУППИРОВАТЬ ПО присутствуют функции определения типов ТИПЗНАЧЕНИЯ или конструкции ВЫБОР КОГДА над составными типами, транслятор для Oracle может сгенерировать несовпадающие наборы колонок в секциях SELECT и GROUP BY.
Разница в оптимизаторах СУБД: Файловый движок 1С и оптимизатор Microsoft SQL Server способны сглаживать неточности сопоставления выражений при компиляции плана выполнения, в то время как Oracle блокирует выполнение запроса при малейшем расхождении.
Ошибки генератора запросов платформы: В некоторых версиях платформы 1С присутствуют зарегистрированные ошибки транслятора, когда корректный с точки зрения синтаксиса 1С запрос транслируется в некорректный для Oracle код SQL.

Способ 1. Разделение запроса через временные таблицы (Рекомендуемый)

Рассмотрим наиболее надежный и универсальный метод решения проблемы. Он заключается в том, чтобы разбить один перегруженный запрос на два последовательных шага с использованием Временных таблиц (МенеджерВременныхТаблиц или пакетный запрос):

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

Посмотрим на пример рефакторинга проблемного запроса. Допустим, исходный запрос выполнял сложные ветвления и группировку в одном месте:


// Исходный проблемный вариант (падает на Oracle)
ВЫБРАТЬ
    ВЫБОР
        КОГДА ТИПЗНАЧЕНИЯ(ДокументОснование) = ТИП(Документ.ПоступлениеТоваровУслуг)
            ТОГДА ДокументОснование.ДоговорКонтрагента
        ИНАЧЕ НЕОПРЕДЕЛЕНО
    КОНЕЦ КАК Договор,
    СУММА(СуммаДокумента) КАК Сумма
ИЗ
    Документ.СчетФактураПолученный КАК СФ
СГРУППИРОВАТЬ ПО
    ВЫБОР
        КОГДА ТИПЗНАЧЕНИЯ(ДокументОснование) = ТИП(Документ.ПоступлениеТоваровУслуг)
            ТОГДА ДокументОснование.ДоговорКонтрагента
        ИНАЧЕ НЕОПРЕДЕЛЕНО
    КОНЕЦ

Перепишем данный фрагмент с применением пакета запросов и временной таблицы:


// Исправленный вариант с использованием временной таблицы
ВЫБРАТЬ
    ВЫБОР
        КОГДА ТИПЗНАЧЕНИЯ(СФ.ДокументОснование) = ТИП(Документ.ПоступлениеТоваровУслуг)
            ТОГДА СФ.ДокументОснование.ДоговорКонтрагента
        ИНАЧЕ НЕОПРЕДЕЛЕНО
    КОНЕЦ КАК Договор,
    СФ.СуммаДокумента КАК СуммаДокумента
ПОМЕСТИТЬ ВТ_ПодготовленныеДанные
ИЗ
    Документ.СчетФактураПолученный КАК СФ
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.Договор КАК Договор,
    СУММА(ВТ.СуммаДокумента) КАК Сумма
ИЗ
    ВТ_ПодготовленныеДанные КАК ВТ
СГРУППИРОВАТЬ ПО
    ВТ.Договор

В результате такого разделения во второй части запроса СУБД Oracle видит только группировку по готовой колонке ВТ.Договор, что полностью исключает ошибку трансляции выражений.

Способ 2. Явное приведение типов с помощью конструкции ВЫРАЗИТЬ

Разберем ситуацию, когда во временную таблицу выносить данные нежелательно, а поле имеет составной тип. Чтобы транслятор 1С однозначно понимал структуру типа и не создавал лишних системных полей в блоке GROUP BY, применим оператор ВЫРАЗИТЬ(...).


ВЫБРАТЬ
    ВЫРАЗИТЬ(СФ.ДокументОснование КАК Документ.ПоступлениеТоваровУслуг) КАК ДокументОснование,
    СУММА(СФ.СуммаДокумента) КАК Сумма
ИЗ
    Документ.СчетФактураПолученный КАК СФ
ГДЕ
    СФ.ДокументОснование ССЫЛКА Документ.ПоступлениеТоваровУслуг
СГРУППИРОВАТЬ ПО
    ВЫРАЗИТЬ(СФ.ДокументОснование КАК Документ.ПоступлениеТоваровУслуг)

Использование ВЫРАЗИТЬ совместно с проверкой ССЫЛКА ограничивает выборку строго одной таблицей базы данных, благодаря чему генератор SQL формирует прозрачный и валидный для Oracle список полей.

Способ 3. Замена выражений в группировке на агрегатные функции

Если некоторое вычисляемое выражение функционально зависит от основных полей группировки и для каждой группы гарантированно возвращает одно и то же значение, его не обязательно дублировать в секции СГРУППИРОВАТЬ ПО. Вместо этого обернем выражение в агрегатную функцию МАКСИМУМ или МИНИМУМ.


ВЫБРАТЬ
    СФ.Ссылка КАК СчетФактура,
    МАКСИМУМ(ВЫБОР
        КОГДА СФ.ВидСчетаФактуры = ЗНАЧЕНИЕ(Перечисление.ВидСчетаФактуры.НаАванс)
            ТОГДА ИСТИНА
        ИНАЧЕ ЛОЖЬ
    КОНЕЦ) КАК ЭтоАванс,
    СУММА(СФ.СуммаДокумента) КАК Сумма
ИЗ
    Документ.СчетФактураПолученный КАК СФ
СГРУППИРОВАТЬ ПО
    СФ.Ссылка

В этом случае сложное условие ВЫБОР уходит из секции GROUP BY нативного SQL-запроса, выполняясь как стандартная агрегатная операция, что полностью снимает конфликт синтаксиса в Oracle.

Способ 4. Использование ВЫБРАТЬ РАЗЛИЧНЫЕ вместо группировки

Если в запросе не вычисляются числовые агрегаты (СУММА, КОЛИЧЕСТВО), а группировка применялась исключительно для устранения дубликатов строк, заменим секцию СГРУППИРОВАТЬ ПО на ключевое слово ВЫБРАТЬ РАЗЛИЧНЫЕ (эквивалент DISTINCT в SQL).

Это исключает формирование секции GROUP BY на уровне СУБД, перекладывая задачу устранения повторов на операцию сортировки и фильтрации результирующего набора.

Итоги и рекомендации

При адаптации или разработке запросов для баз данных под управлением Oracle придерживаемся следующих правил:

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