При разработке отчетов и обработок в системе 1С:Предприятие программисты часто сталкиваются с задачей анализа складских запасов. Типичный сценарий — выборка товаров, текущий остаток которых опустился ниже минимально допустимого уровня (порога заказа). Однако на практике разработчики часто замечают, что позиции, остаток которых на складе равен нулю (то есть товар полностью отсутствует), странным образом исчезают из результирующей выборки. Это приводит к критическим ошибкам в снабжении: вовремя не заказываются самые востребованные позиции, запасы которых полностью исчерпаны.
В этой статье мы подробно разберем, почему возникает эта проблема, детально проанализируем поведение СУБД при выполнении соединений таблиц, изучим природу значения NULL в платформе 1С и рассмотрим наиболее эффективные и оптимальные способы решения этой задачи с точки зрения стандартов разработки.
Рассмотрим ситуацию, с которой столкнулся автор темы на форуме. Ему необходимо сопоставить целевые товарные ограничения (минимальный запас) из регистра сведений и реальные свободные остатки из регистра накопления. Исходный запрос автора выглядел следующим образом:
ВЫБРАТЬ
ТоварныеОграничения.Склад КАК Склад,
ТоварныеОграничения.Номенклатура.Родитель КАК НоменклатураРодитель,
ТоварныеОграничения.Номенклатура КАК Номенклатура,
ТоварныеОграничения.МинимальноеКоличествоЗапаса КАК МинимальноеКоличествоЗапаса,
ТоварныеОграничения.МаксимальноеКоличествоЗапаса КАК МаксимальноеКоличествоЗапаса,
ВЫБОР
КОГДА СвободныеОстаткиОстатки.ВНаличииОстаток ЕСТЬ NULL ТОГДА 0
ИНАЧЕ СвободныеОстаткиОстатки.ВНаличииОстаток
КОНЕЦ КАК ВНаличииОстаток
ИЗ
РегистрСведений.ТоварныеОграничения КАК ТоварныеОграничения
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки КАК СвободныеОстаткиОстатки
ПО ТоварныеОграничения.Номенклатура = СвободныеОстаткиОстатки.Номенклатура
ГДЕ
СвободныеОстаткиОстатки.ВНаличииОстаток <= ТоварныеОграничения.МинимальноеКоличествоЗапаса
Проанализируем этот запрос и выясним, почему позиции с нулевыми остатками не попадают в итоговую выборку. Здесь присутствуют сразу две грубые архитектурные ошибки, связанные с логикой работы реляционных баз данных.
Первое, на что мы должны обратить внимание, — это использование конструкции ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN). При внутреннем соединении СУБД сопоставляет записи из таблицы ограничений и таблицы остатков только тогда, когда для номенклатуры из первой таблицы существует строгое соответствие во второй таблице.
Однако особенности работы виртуальных таблиц остатков в 1С (как и в любых других СУБД) заключаются в том, что если по товару никогда не было движений на складе или если остаток товара стал равен нулю и все старые движения "закрылись", физически в базе данных в таблице остатков записи по этой номенклатуре не будет. СУБД не хранит миллионы строк с нулевыми остатками для экономии дискового пространства и повышения скорости чтения. Раз нет записи в таблице остатков — значит, строка с этой номенклатурой полностью отсекается на этапе выполнения ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Товар с нулевым остатком просто не доходит до дальнейшей обработки.
Для решения этой проблемы необходимо использовать ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN). В этом случае левая таблица (в нашем примере — РегистрСведений.ТоварныеОграничения) является ведущей. Все её записи гарантированно попадут в результат, а из правой таблицы (РегистрНакопления.СвободныеОстатки.Остатки) будут подтянуты данные там, где они есть. Если данных по остаткам нет, поля правой таблицы заполнятся значением NULL.
Допустим, мы заменили тип соединения на левое, но оставили условие фильтрации в секции ГДЕ в прежнем виде:
ГДЕ СвободныеОстаткиОстатки.ВНаличииОстаток <= ТоварныеОграничения.МинимальноеКоличествоЗапаса
Это классическая ловушка для начинающих разработчиков. Давайте пошагово разберем, как СУБД обрабатывает такой запрос:
ВНаличииОстаток заполняется значением NULL.ГДЕ. Она пытается сравнить значение поля ВНаличииОстаток с минимальным запасом.NULL <= МинимальноеКоличествоЗапаса.NULL (будь то больше, меньше, равно или не равно) возвращает специальное логическое значение Unknown (Ложь в контексте фильтрации строк).Таким образом, жесткое условие фильтрации по полям присоединяемой таблицы в секции ГДЕ полностью нивелирует работу левого соединения, превращая его обратно во внутреннее.
Для того чтобы решить проблему и вывести товары с нулевыми остатками, нам нужно правильно обработать значение NULL и сохранить логику левого соединения. Разберем два основных рабочих варианта.
Самый простой и понятный способ — обернуть проверяемое поле в функцию ЕСТЬNULL в условиях фильтрации. Эта функция проверяет первый параметр, и если он равен NULL, возвращает второй параметр (в нашем случае — числовой ноль).
Посмотрим на пример правильного запроса с использованием ЕСТЬNULL:
ВЫБРАТЬ
ТоварныеОграничения.Склад КАК Склад,
ТоварныеОграничения.Номенклатура.Родитель КАК НоменклатураРодитель,
ТоварныеОграничения.Номенклатура КАК Номенклатура,
ТоварныеОграничения.МинимальноеКоличествоЗапаса КАК МинимальноеКоличествоЗапаса,
ТоварныеОграничения.МаксимальноеКоличествоЗапаса КАК МаксимальноеКоличествоЗапаса,
ЕСТЬNULL(СвободныеОстаткиОстатки.ВНаличииОстаток, 0) КАК ВНаличииОстаток
ИЗ
РегистрСведений.ТоварныеОграничения КАК ТоварныеОграничения
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки КАК СвободныеОстаткиОстатки
ПО ТоварныеОграничения.Номенклатура = СвободныеОстаткиОстатки.Номенклатура
ГДЕ
ЕСТЬNULL(СвободныеОстаткиОстатки.ВНаличииОстаток, 0) <= ТоварныеОграничения.МинимальноеКоличествоЗапаса
Как это работает: Теперь для товаров с отсутствующими остатками выражение ЕСТЬNULL(СвободныеОстаткиОстатки.ВНаличииОстаток, 0) вернет числовой 0. СУБД выполнит корректное математическое сравнение: 0 <= МинимальноеКоличествоЗапаса. Так как минимальный запас всегда больше или равен нулю, условие выполнится как Истина, и данные товары успешно попадут в отчет.
Альтернативный, но очень мощный способ — перенести условие ограничения запасов непосредственно в условия связи таблиц (секция ПО). Давайте проанализируем этот вариант:
ВЫБРАТЬ
ТоварныеОграничения.Склад КАК Склад,
ТоварныеОграничения.Номенклатура.Родитель КАК НоменклатураРодитель,
ТоварныеОграничения.Номенклатура КАК Номенклатура,
ТоварныеОграничения.МинимальноеКоличествоЗапаса КАК МинимальноеКоличествоЗапаса,
ТоварныеОграничения.МаксимальноеКоличествоЗапаса КАК МаксимальноеКоличествоЗапаса,
ЕСТЬNULL(СвободныеОстаткиОстатки.ВНаличииОстаток, 0) КАК ВНаличииОстаток
ИЗ
РегистрСведений.ТоварныеОграничения КАК ТоварныеОграничения
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки КАК СвободныеОстаткиОстатки
ПО ТоварныеОграничения.Номенклатура = СвободныеОстаткиОстатки.Номенклатура
И СвободныеОстаткиОстатки.ВНаличииОстаток <= ТоварныеОграничения.МинимальноеКоличествоЗапаса
В чем разница: При такой схеме СУБД сначала пытается найти остатки для номенклатуры, которые удовлетворяют условию ВНаличииОстаток <= МинимальноеКоличествоЗапаса. Если остатки удовлетворяют этому условию, они подтягиваются в результирующее поле. Если остатков нет вовсе ИЛИ если остатки превышают минимальный предел, то для этой номенклатуры поле ВНаличииОстаток заполнится значением NULL.
Обратите внимание на важный нюанс: при таком подходе в итоговой выборке окажутся абсолютно все товары из регистра ограничений. Для товаров с избыточным остатком (например, на складе лежит 100 шт при минимуме в 10 шт) остаток выведется как 0 (благодаря ЕСТЬNULL в секции ВЫБРАТЬ). Поэтому для решения исходной задачи отбора "проблемных" товаров предпочтительнее использовать Вариант А, так как он физически отсекает лишние строки, по которым условия превышения лимитов не сработали.
Для написания безошибочных запросов крайне важно четко понимать разницу между отсутствием значения и другими типами пустых данных. Начинающие разработчики часто путают NULL, Неопределено, пустые ссылки и нули. Давайте структурируем эти понятия:
Неопределено хранится в виде специальных системных полей, но это не то же самое, что NULL.ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)) — это полноценное значение объектного типа, содержащее уникальный идентификатор, состоящий из одних нулей (GUID 00000000-0000-0000-0000-000000000000). Пустая ссылка — это конкретный элемент базы данных, просто он указывает на "ничто".Важно помнить, что сравнить поле со значением NULL через привычный знак равенства = в запросе невозможно. Запрос ГДЕ Поле = NULL синтаксически ошибочен и не вернет записей. Для проверки на пустоту СУБД предлагает специальный оператор ЕСТЬ NULL:
ГДЕ СвободныеОстаткиОстатки.ВНаличииОстаток ЕСТЬ NULL
Функция ЕСТЬNULL(<ПроверяемоеПоле>, <ЗначениеЗамены>) является системной функцией платформы 1С и на уровне СУБД транслируется в высокопроизводительные конструкции (например, COALESCE или ISNULL в MS SQL Server). Она работает значительно быстрее стандартного оператора ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ... КОНЕЦ, поэтому её использование всегда предпочтительнее для оптимизации производительности БД.
Однако при работе с этой функцией необходимо учитывать критически важное правило — приведение типов по первому параметру.
Когда СУБД вычисляет тип возвращаемого значения функции ЕСТЬNULL, она жестко ориентируется на описание типа первого (проверяемого) параметра. Если значением замены (второй параметр) выступает тип, не соответствующий первому параметру, или значение большей длины/точности, платформа выполнит неявное приведение типа, что может привести к искажению данных.
Пример со строками: Представим, что первое проверяемое поле имеет в базе данных тип Строка(5). В качестве значения замены при значении NULL мы передаем строку "Нет данных" (длина 10 символов):
ЕСТЬNULL(Таблица.КороткоеПоле, "Нет данных")
На выходе при встрече с NULL мы получим обрезанную строку "Нет д", длина которой будет ровно 5 символов, так как СУБД жестко ограничила возвращаемый тип по первому параметру.
Пример с числами: Если первое поле имеет числовой тип с точностью 1 знак после запятой, а в качестве замены передать число с точностью 3 знака, лишние знаки будут округлены или отброшены, что может критически повлиять на расчет себестоимости или распределение долей запасов.
Рассматривая наш пример, мы обязаны уделить внимание вопросам оптимизации запросов к виртуальным таблицам. Накладывать фильтры по измерениям регистров накопления (например, по складу) через секции ГДЕ или ПО — это крайне неоптимальная практика, приводящая к серьезным проблемам с производительностью базы данных.
Если в секции ГДЕ написать Остатки.Склад = &Склад, СУБД сначала прочитает из базы данных абсолютно все остатки по всем складам организации, построит временную таблицу в памяти, выполнит соединение с товарными ограничениями и только потом отсечет лишние склады. Это вызывает избыточную нагрузку на сервер БД и дисковую подсистему.
Правильный и профессиональный подход — передавать фильтры в параметры самой виртуальной таблицы. В таком случае СУБД получит отбор еще на этапе обращения к индексам таблиц и прочитает из базы данных только те строки, которые относятся к нужному складу.
Давайте проанализируем полностью оптимизированный и эталонный вариант запроса:
ВЫБРАТЬ
ТоварныеОграничения.Склад КАК Склад,
ТоварныеОграничения.Номенклатура.Родитель КАК НоменклатураРодитель,
ТоварныеОграничения.Номенклатура КАК Номенклатура,
ТоварныеОграничения.МинимальноеКоличествоЗапаса КАК МинимальноеКоличествоЗапаса,
ТоварныеОграничения.МаксимальноеКоличествоЗапаса КАК МаксимальноеКоличествоЗапаса,
ЕСТЬNULL(СвободныеОстаткиОстатки.ВНаличииОстаток, 0) КАК ВНаличииОстаток
ИЗ
РегистрСведений.ТоварныеОграничения КАК ТоварныеОграничения
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.SвободныеОстатки.Остатки(&Период, Склад = &Склад) КАК СвободныеОстаткиОстатки
ПО ТоварныеОграничения.Номенклатура = СвободныеОстаткиОстатки.Номенклатура
ГДЕ
ЕСТЬNULL(СвободныеОстаткиОстатки.ВНаличииОстаток, 0) <= ТоварныеОграничения.МинимальноеКоличествоЗапаса
Использование полей из правых (присоединяемых) таблиц без предварительной проверки на NULL с помощью функции ЕСТЬNULL считается грубейшим нарушением стандартов разработки фирмы "1С".
Современные инструменты автоматического контроля качества кода, такие как BSL Language Server, SonarQube со специализированными плагинами для 1С, всегда подсвечивают подобные конструкции как критические ошибки (Critical Issues). И на это есть веские причины:
NULL на уровне СУБД, эти значения попадут в итоговую выборку запроса, которая возвращается во встроенный язык 1С.Остаток + 10) или попытается передать его в процедуру форматирования, система вызовет критическую ошибку выполнения (Runtime Error): "Операции над значениями несовместимых типов".NULL на стороне сервера баз данных с помощью встроенных SQL-функций работает в десятки раз быстрее, чем аналогичные проверки и циклы обхода выборки на стороне встроенного языка 1С.Следуя этим правилам, вы всегда будете получать стабильные, быстродействующие и легко поддерживаемые программные решения, которые не подведут пользователей в процессе эксплуатации системы.