Разработка сложных механизмов ценообразования в среде «1С:Предприятие» часто требует обращения к иерархическим свойствам справочника Номенклатура. Довольно типичная задача — установить наценки в разрезе товарных групп (родителей номенклатуры). Однако разработчики нередко сталкиваются с ситуацией, когда логическое ветвление внутри запроса отрабатывает некорректно: условная конструкция ВЫБОР КОГДА упорно уходит в ветку ИНАЧЕ, даже если визуально в результирующей выборке значения сопоставляемых полей кажутся одинаковыми.
В этой статье мы подробно разберем, почему конструкция сравнения родителей (например, ТаблТоваров.Родительь = ТаблНаценок1.Родителььь) может возвращать ложное значение, проанализируем физику работы СУБД с неопределенными значениями, выявим архитектурную ошибку в логике левого соединения и рассмотрим пути оптимизации производительности запроса.
Главная и наиболее частая причина, из-за которой сравнение полей в запросе возвращает ложное значение — это наличие значений NULL в одном или обоих сравниваемых полях. В нашем исходном примере происходит левое соединение (ЛЕВОЕ СОЕДИНЕНИЕ) таблицы товаров с таблицей наценок. Если для какого-то товара в таблице наценок не нашлось подходящей записи по цене или настройке загрузки, СУБД принудительно заполнит все поля из таблицы наценок (включая искомую группу номенклатуры Родителььь) значением NULL.
Также значение NULL может возникнуть в поле Родительь самой таблицы товаров. Это происходит, если у элемента номенклатуры отсутствует родитель второго уровня (товар лежит в корне справочника или на первом уровне иерархии), либо если товар в принципе не сопоставлен (в запросе используется пустая ссылка номенклатуры).
Согласно стандарту SQL, СУБД использует так называемую трехзначную логику (True, False, Unknown). Любая операция сравнения со значением NULL (будь то равенство, неравенство, сравнение на больше или меньше) всегда вычисляется как Unknown (неизвестно), что в логических условиях конструкции ВЫБОР трактуется СУБД как ЛОЖЬ. Таким образом:
Родитель1 = NULL возвращает ЛОЖЬ.NULL = Родитель2 возвращает ЛОЖЬ.NULL = NULL возвращает ЛОЖЬ!Именно поэтому конструкция, написанная автором темы:
ВЫБОР
КОГДА ТаблТоваров.Родительь = ТаблНаценок1.Родителььь
ТОГДА 0
ИНАЧЕ 1
КОНЕЦ КАК ЦенаПродажи
в случаях отсутствия родителя или отсутствия сопоставленной строки наценки гарантированно вычисляла ЛОЖЬ и возвращала значение 1.
Решение проблемы: Для корректного сравнения полей, которые потенциально могут содержать неопределенные значения, необходимо использовать функцию ЕСТЬNULL. Она позволяет заменить NULL на пустую ссылку соответствующего типа перед выполнением операции сравнения. Рассмотрим правильный пример:
ВЫБОР
КОГДА ЕСТЬNULL(ТаблТоваров.Родительь, ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)) =
ЕСТЬNULL(ТаблНаценок1.Родителььь, ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка))
ТОГДА 0
ИНАЧЕ 1
КОНЕЦ КАК ЦенаПродажи
В этом случае, если у товара нет родителя второго уровня и в таблице наценок группа также не задана, запрос сравнит пустую ссылку с пустой ссылкой, получит результат ИСТИНА и корректно вернет 0.
Проанализируем структуру связей в исходном запросе автора. Левое соединение таблиц выполняется следующим образом:
ИЗ
ТаблТоваров КАК ТаблТоваров
ЛЕВОЕ СОЕДИНЕНИЕ ТаблНаценок1 КАК ТаблНаценок1
ПО ТаблТоваров.НастройкаЗагрузки = ТаблНаценок1.НастройкаЗагрузки
И ТаблТоваров.Цена >= ТаблНаценок1.PriceНач
И ТаблТоваров.Цена <= ТаблНаценок1.PriceКон
Обратите внимание: в секции ПО (условии связи таблиц) полностью отсутствует сопоставление по самой номенклатуре или её иерархии! Связь устанавливается исключительно по настройке и диапазону цен.
Это приводит к серьезной логической ошибке. СУБД сначала связывает каждую строку из ТаблТоваров со всеми строками из ТаблНаценок1, у которых совпадает настройка и цена укладывается в диапазон. Если для данного ценового диапазона в таблице наценок заведено несколько разных правил для разных групп товаров, то одна строка товара продублируется в выборке столько раз, сколько наценок попало в диапазон. И только потом, на этапе вычисления полей ВЫБОР КОГДА, система пытается проверить соответствие родителей. Но к этому моменту декартово произведение таблиц уже произошло, что искажает конечный результат расчета цен.
Решение проблемы: Условие принадлежности товара к определенной группе наценки должно находиться непосредственно в секции соединения ПО (JOIN), а не внутри условного выбора полей. Посмотрим, как переписать это соединение правильно:
ИЗ
ТаблТоваров КАК ТаблТоваров
ЛЕВОЕ СОЕДИНЕНИЕ ТаблНаценок1 КАК ТаблНаценок1
ПО ТаблТоваров.НастройкаЗагрузки = ТаблНаценок1.НастройкаЗагрузки
И ТаблТоваров.Цена >= ТаблНаценок1.ЦенаНач
И ТаблТоваров.Цена < ТаблНаценок1.ЦенаКон
И (ТаблТоваров.Родительь = ТаблНаценок1.Родителььь
ИЛИ ТаблНаценок1.Родителььь ЕСТЬ NULL) // Условие для общих наценок, не привязанных к группе
Такой подход гарантирует, что к строке товара присоединится только та строка наценки, которая предназначена именно для его товарной группы, либо общая наценка, у которой группа не заполнена (равна NULL).
Проанализируем еще одну критическую проблему в запросе — многократное обращение через точку к реквизитам ссылочных полей:
ЦеныПоставщиковСрезПоследних.Номенклатура.Номенклатура.Родитель.Родитель КАК Родительь
Каждая точка в таком выражении при трансляции запроса платформой 1С в SQL-код превращается в дополнительное неявное левое соединение (LEFT JOIN) таблиц в СУБД. В данном случае для получения родителя второго уровня СУБД вынуждена выполнить цепочку из четырех соединений:
НоменклатураКонтрагентов (поставщика).НоменклатураКонтрагентов -> Основной справочник Номенклатура.Номенклатура -> Справочник Номенклатура (Родитель 1 уровня).Номенклатура (Родитель 1) -> Справочник Номенклатура (Родитель 2 уровня).Если в базе данных обрабатываются прайс-листы поставщиков на десятки тысяч позиций, такой запрос неизбежно приведет к существенному замедлению работы СУБД, пиковой загрузке процессора на сервере баз данных и, возможно, к ошибкам превышения времени ожидания блокировок.
Решение проблемы: Оптимизируем получение данных. Избавимся от глубокого разыменования через точку путем поэтапной работы с временными таблицами.
Разобьем тяжелую операцию на последовательные шаги:
Номенклатура для получения родителей. При этом связывание будет происходить быстро по индексированным полям.Рассмотрим пример реализации такой оптимизации:
// Шаг 1: Получаем базовый список сопоставленных товаров
ВЫБРАТЬ
ЦеныПоставщиков.Номенклатура КАК НоменклатураПоставщика,
ЦеныПоставщиков.Номенклатура.Номенклатура КАК НашаНоменклатура,
ЦеныПоставщиков.ЦенаПоставщикаВал КАК Цена
ПОМЕСТИТЬ Вт_БазовыеТовары
ИЗ
РегистрСведений.ЦеныПоставщиков.СрезПоследних КАК ЦеныПоставщиков
ИНДЕКСИРОВАТЬ ПО
НашаНоменклатура
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем родителей без длинных цепочек точек в одном запросе
ВЫБРАТЬ
Вт_БазовыеТовары.НоменклатураПоставщика,
Вт_БазовыеТовары.НашаНоменклатура,
Вт_БазовыеТовары.Цена,
ЕСТЬNULL(СпрНоменклатура.Родитель, ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)) КАК Родитель1,
ЕСТЬNULL(СпрНоменклатура.Родитель.Родитель, ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)) КАК Родитель2
ПОМЕСТИТЬ ТаблТоваровОптимизированная
ИЗ
Вт_БазовыеТовары КАК Вт_БазовыеТовары
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СпрНоменклатура
ПО Вт_БазовыеТовары.НашаНоменклатура = СпрНоменклатура.Ссылка
;
Попытка жестко завязаться на конструкцию Родитель.Родитель (второй уровень иерархии) делает алгоритм расчета цен крайне уязвимым. Если структура каталога товаров изменится (например, менеджер добавит промежуточную папку-бренд или перенесет товары на уровень выше/глубже), логика расчета сломается, так как наценка перестанет определяться.
В типовых конфигурациях 1С (таких как «Управление торговлей» или «ERP») задачи расчета скидок и наценок по иерархии решаются с использованием концепции приоритетов. Вместо жесткого указания уровней родителей, система ищет наценку по цепочке от частного к общему:
Чтобы реализовать подобную логику в запросе без потери производительности, мы можем использовать левое соединение со всеми возможными уровнями иерархии одновременно и выбирать наиболее подходящее значение с помощью функции ЕСТЬNULL. Проанализируем пример такого построения условного выбора цены:
ВЫБРАТЬ
ТаблТоваров.НашаНоменклатура,
// Выбираем процент наценки по приоритету:
// Сначала индивидуальный, потом родитель 1, потом родитель 2, иначе значение по умолчанию (0)
ЕСТЬNULL(НаценкиИндивидуальные.ПроцентНаценки,
ЕСТЬNULL(НаценкиРодителя1.ПроцентНаценки,
ЕСТЬNULL(НаценкиРодителя2.ПроцентНаценки, 0))) КАК ИтоговыйПроцент
ИЗ
ТаблТоваров КАК ТаблТоваров
// Присоединяем индивидуальные наценки на товар
ЛЕВОЕ СОЕДИНЕНИЕ ТаблНаценок КАК НаценкиИндивидуальные
ПО ТаблТоваров.НашаНоменклатура = НаценкиИндивидуальные.Номенклатура
// Присоединяем наценки на группу 1-го уровня
ЛЕВОЕ СОЕДИНЕНИЕ ТаблНаценок КАК НаценкиРодителя1
ПО ТаблТоваров.Родитель1 = НаценкиРодителя1.Номенклатура
// Присоединяем наценки на группу 2-го уровня
ЛЕВОЕ СОЕДИНЕНИЕ ТаблНаценок КАК НаценкиРодителя2
ПО ТаблТоваров.Родитель2 = НаценкиРодителя2.Номенклатура
Такой подход обеспечивает высокую гибкость решения. Даже если структура каталога номенклатуры изменится, система продолжит корректно обрабатывать те уровни иерархии, которые совпали с настройками наценок в информационной базе, не вызывая дублирования строк или непредвиденных результатов.