Почему левое соединение в запросе 1С возвращает пустой результат при подстановке параметров?

← На главную

Каждый разработчик на платформе 1С:Предприятие рано или поздно сталкивается с ситуацией, когда запрос, использующий ЛЕВОЕ СОЕДИНЕНИЕ, внезапно возвращает пустой результат или «теряет» строки, которые гарантированно должны присутствовать в выборке. Особенно часто эта проблема проявляется при реализации механизмов контроля остатков в модулях проведения документов, когда остатки по регистру еще не накоплены.

В этой статье мы подробно разберем классическую ошибку, связанную со спецификой работы трехзначной логики SQL при использовании левого соединения и условий в секции ГДЕ. Мы детально проанализируем, почему возникает эта проблема, рассмотрим сопутствующие ошибки производительности и напишем эталонный код проведения документа в соответствии со стандартами разработки фирмы «1С».

Выясняем причину: почему пропадают строки при левом соединении?

Рассмотрим практическую ситуацию. В конфигурации «1С:Управление торговлей 11» у нас есть документ Заказ клиента. Нам необходимо при его проведении проверить остатки по нашему собственному регистру накопления Условия договоров. Автор исходного запроса написал следующий код в обработке проведения:


Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
               |    ЗаказКлиентаТовары.Номенклатура КАК Товар,
               |    ЕСТЬNULL(УсловияДоговоровОстатки.КоличествоОстаток, 0) КАК Остаток,
               |    ЕСТЬNULL(ЗаказКлиентаТовары.Количество, 0) КАК КоличествоТовара
               |ИЗ
               |    Документ.ЗаказКлиента.Товары КАК ЗаказКлиентаТовары
               |    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.УсловияДоговоров.Остатки КАК УсловияДоговоровОстатки
               |    ПО ЗаказКлиентаТовары.Номенклатура = УсловияДоговоровОстатки.Товар
               |        И ЗаказКлиентаТовары.Ссылка.Соглашение = УсловияДоговоровОстатки.Соглашение
               |ГДЕ
               |    УсловияДоговоровОстатки.Соглашение = &Соглашение
               |    И ЗаказКлиентаТовары.Ссылка = &Ссылка";
Запрос.УстановитьПараметр("Соглашение", Соглашение);
Запрос.УстановитьПараметр("Ссылка", Ссылка);

Когда мы выполняем этот запрос для документа, по соглашению которого еще нет движений в регистре остатков, результат оказывается абсолютно пустым. Но ведь мы использовали левое соединение! По логике левого соединения, все строки из левой таблицы (табличная часть Товары нашего заказа) должны были попасть в результат, а отсутствующие остатки из правой таблицы должны были заполниться значениями NULL, которые функция ЕСТЬNULL превратила бы в нули. Выясним, почему же запрос пуст.

Всё дело в порядке выполнения разделов запроса СУБД и поведении значения NULL.

В реляционных базах данных значение NULL означает отсутствие данных. Это не пустая строка, не ноль и не ложь — это особое состояние «неизвестности». Логика СУБД при работе с NULL является трехзначной (истина, ложь, неизвестно). Любая операция сравнения, в которой участвует NULL, всегда вычисляется в значение «неизвестно» (которое в секции фильтрации трактуется как ложь).

Когда СУБД выполняет запрос, она сначала производит соединение таблиц по условиям, указанным в секции ПО. На этом этапе левое соединение отрабатывает корректно: левая таблица сохраняется полностью, а для товаров, по которым нет остатков в регистре, все поля правой таблицы (включая УсловияДоговоровОстатки.Соглашение) заполняются значениями NULL.

Затем вступает в силу секция ГДЕ. В ней написано жесткое условие фильтрации:

УсловияДоговоровОстатки.Соглашение = &Соглашение

Для строк, по которым остатков не оказалось, поле Соглашение содержит значение NULL. СУБД пытается вычислить выражение: NULL = &Соглашение. Этот предикат возвращает ложь. В результате строка не проходит проверку условием и полностью исключается из финальной выборки.

Таким образом, наложение фильтра по полям правой таблицы в секции ГДЕ фактически превращает наше ЛЕВОЕ СОЕДИНЕНИЕ во ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN). Все строки, для которых в правой таблице не нашлось соответствия, отсекаются.

Проанализируем сопутствующие ошибки производительности и стандарты 1С

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

Отсутствие параметров у виртуальной таблицы остатков. Это одна из самых распространенных ошибок проектирования запросов в 1С. В исходном запросе мы обращаемся к таблице РегистрНакопления.УсловияДоговоров.Остатки без параметров. СУБД вынуждена будет считать из базы данных абсолютно все записи остатков по всем соглашениям и товарам, выполнить соединение и только потом отфильтровать их в секции ГДЕ. Это порождает колоссальную избыточную нагрузку на СУБД.
Разыменование ссылочных полей через точку. Конструкция ЗаказКлиентаТовары.Ссылка.Соглашение в условии соединения заставляет платформу делать неявное соединение физической таблицы табличной части с таблицей шапки документа. Это замедляет выполнение запроса.
Проблема расчета остатков на «конец времен». Виртуальная таблица остатков вызывается без указания периода. Это означает, что остатки будут получены на самую актуальную дату в системе (3999 год). Если пользователь проводит документ задним числом, проверка остатков будет некорректной, так как она учтет движения всех будущих документов. При проведении документов остатки всегда необходимо контролировать на момент времени проводимого документа.
Отсутствие свертки табличной части документа. Если в заказе одна и та же номенклатура введена в нескольких строках, запрос вернет несколько строк для одной и той же номенклатуры. При этом в каждой строке будет проверяться полный остаток, что приведет к ложным отказам в проведении (ошибка «самоперекрытия» остатка).

Разберем по шагам правильное решение задачи

Для того чтобы исправить все ошибки и написать оптимальный, быстродействующий и надежный код проведения, мы выполним несколько шагов.

Шаг 1. Очистка существующих движений. Перед тем как проверять остатки в процессе проведения, крайне важно очистить и записать движения проводимого документа по регистру. Если этого не сделать, документ при перепроведении будет проверять остатки с учетом своих же собственных старых движений.

Шаг 2. Использование объекта Граница. Для получения остатков на момент проведения документа (не включая движения самого этого документа) мы используем специальный объект платформы Граница с видом границы Исключая.

Шаг 3. Группировка данных во временной таблице. Мы предварительно выберем товары из табличной части документа, сгруппируем их по номенклатуре и поместим во временную таблицу. Это позволит СУБД построить эффективный план запроса.

Шаг 4. Наложение условий в параметрах виртуальной таблицы. Мы передадим отбор по соглашению и периоду непосредственно в параметры виртуальной таблицы остатков. СУБД выберет только те остатки, которые соответствуют нашему соглашению и периоду, используя соответствующие индексы базы данных.

Реализация эталонного решения на языке 1С

Рассмотрим правильный вариант реализации процедуры проведения документа с использованием временных таблиц и правильным расчетом границы времени:


Процедура ОбработкаПроведения(Отказ, РежимПроведения)
    
    // Шаг 1: Очищаем движения документа по регистру контроля, чтобы он не проверял сам себя
    Движения.УсловияДоговоров.Записать();
    
    // Шаг 2: Формируем правильную границу времени для анализа остатков
    ГраницаКонтроля = Новый Граница(Ссылка.МоментВремени(), ВидГраницы.Исключая);
    
    Запрос = Новый Запрос;
    Запрос.Текст = 
        "ВЫБРАТЬ
        |    ЗаказТовары.Номенклатура КАК Товар,
        |    СУММА(ЗаказТовары.Количество) КАК КоличествоТовара
        |ПОМЕСТИТЬ ВТ_ТоварыДокумента
        |ИЗ
        |    Документ.ЗаказКлиента.Товары КАК ЗаказТовары
        |ГДЕ
        |    ЗаказТовары.Ссылка = &Ссылка
        |СГРУППИРОВАТЬ ПО
        |    ЗаказТовары.Номенклатура
        |;
        |
        |////////////////////////////////////////////////////////////////////////////////
        |ВЫБРАТЬ
        |    ВТ_Товары.Товар КАК Товар,
        |    ЕСТЬNULL(УсловияДоговоровОстатки.КоличествоОстаток, 0) КАК Остаток,
        |    ВТ_Товары.КоличествоТовара КАК КоличествоТовара
        |ИЗ
        |    ВТ_ТоварыДокумента КАК ВТ_Товары
        |    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.УсловияДоговоров.Остатки(&Период, Соглашение = &Соглашение) КАК УсловияДоговоровОстатки
        |    ПО ВТ_Товары.Товар = УсловияДоговоровОстатки.Товар";
        
    Запрос.УстановитьПараметр("Ссылка", Ссылка);
    Запрос.УстановитьПараметр("Соглашение", Соглашение); 
    Запрос.УстановитьПараметр("Период", ГраницаКонтроля);
    
    РезультатЗапроса = Запрос.Выполнить();
    Если Не РезультатЗапроса.Пустой() Тогда
        Выборка = РезультатЗапроса.Выбрать();
        Пока Выборка.Следующий() Цикл
            Нехватка = Выборка.КоличествоТовара - Выборка.Остаток;
            Если Нехватка > 0 Тогда
                Сообщить("Проведение невозможно. По соглашению не хватает " + Нехватка + " ед. товара " + Выборка.Товар);
                Отказ = Истина;
            КонецЕсли;
        КонецЦикла;
    КонецЕсли;

КонецПроцедуры

Заключение

Перенос условий фильтрации из секции ГДЕ в параметры виртуальной таблицы не только решает проблему исчезновения строк при отсутствии остатков, но и кардинально увеличивает скорость работы системы. Использование временных таблиц, группировки данных и правильное позиционирование на оси времени с помощью объекта Граница гарантирует отказоустойчивость вашей конфигурации 1С при любых нагрузках.