Почему запрос 1С с ЛЕВЫМ СОЕДИНЕНИЕМ не выбирает нулевые остатки и как это исправить?

← На главную

При работе с отчетами и выборками данных в 1С, особенно когда необходимо получить информацию об остатках товаров, часто возникают ситуации, когда стандартный запрос не выдает ожидаемых результатов. Распространенной проблемой является то, что при использовании ЛЕВОГО СОЕДИНЕНИЯ в запросе и попытке отфильтровать позиции с нулевыми остатками, в выборку попадают только позиции с отрицательными остатками, а товары с остатком “0” игнорируются. Давайте подробно разберем, почему это происходит и как правильно построить запрос для корректного отбора нулевых остатков.

Мы рассмотрим исходный запрос, который демонстрирует эту проблему, проанализируем его структуру, выявим причины некорректного поведения и предложим несколько эффективных решений, включая правильную обработку NULL-значений и преодоление распространенной ошибки "Нельзя сравнивать поля неограниченной длины".

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

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


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

При выполнении этого запроса выяснилось, что в выборку попадают только позиции, у которых КоличествоОстаток отрицательное. Позиции с нулевым остатком, которые также должны были бы соответствовать условию <= 0, отсутствовали.

Разбираемся в механизме ЛЕВОГО СОЕДИНЕНИЯ и секции ГДЕ

Для того чтобы понять, почему нулевые остатки не попадают в выборку, мы должны глубоко изучить, как работает ЛЕВОЕ СОЕДИНЕНИЕ, особенно когда условия фильтрации (секция ГДЕ) применяются к полям из правой таблицы соединения.

Как работает ЛЕВОЕ СОЕДИНЕНИЕ:
Когда мы используем ЛЕВОЕ СОЕДИНЕНИЕ, в результирующую выборку включаются все записи из левой таблицы (в нашем случае, ВТЗапасы). Из правой таблицы (РегистрНакопления.ЗапасыНаСкладах.Остатки) берутся только те записи, которые соответствуют условию соединения (ПО (ЗапасыНаСкладахОстатки.Номенклатура.Ссылка = ВТЗапасы.Номенклатура.Ссылка)). Если для какой-либо записи из левой таблицы не находится соответствующая запись в правой таблице (то есть, для номенклатуры нет остатков на складе), то поля правой таблицы в результирующей строке будут содержать значение NULL.
Влияние условия в секции ГДЕ на правую таблицу:
Именно здесь кроется ключевая проблема. В нашем запросе мы имеем условие ГДЕ ЗапасыНаСкладахОстатки.КоличествоОстаток <= 0. Для тех строк, где номенклатура из ВТЗапасы не имеет остатков в регистре (ЗапасыНаСкладах.Остатки), поле ЗапасыНаСкладахОстатки.КоличествоОстаток будет содержать NULL. Любое сравнение с NULL (например, NULL <= 0, NULL = 0, NULL > 0) всегда возвращает ЛОЖЬ, за исключением специальных проверок, таких как ЕСТЬ NULL или НЕ ЕСТЬ NULL.

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

Первое решение: Корректная обработка NULL-значений функцией ЕСТЬNULL

Для того чтобы правильно выбрать позиции, у которых нет остатка (то есть остаток равен 0), нам необходимо явно указать системе, как интерпретировать NULL-значения, возникающие из ЛЕВОГО СОЕДИНЕНИЯ. Для этого мы используем функцию ЕСТЬNULL(<Выражение>, <ЗначениеЗамены>).

Применение ЕСТЬNULL в секции ГДЕ:
Мы заменим условие ЗапасыНаСкладахОстатки.КоличествоОстаток <= 0 на ЕСТЬNULL(ЗапасыНаСкладахОстатки.КоличествоОстаток, 0) <= 0.

Что это дает:
- Если ЗапасыНаСкладахОстатки.КоличествоОстаток не NULL (то есть, для номенклатуры есть запись об остатке), то функция вернет само значение остатка, и оно будет сравниваться с 0.
- Если ЗапасыНаСкладахОстатки.КоличествоОстаток равно NULL (то есть, для номенклатуры нет записи об остатке), то функция ЕСТЬNULL подставит вместо NULL указанное нами значение 0. Таким образом, условие 0 <= 0 станет истинным, и позиции с нулевым остатком (фактически, отсутствующие в регистре) будут включены в выборку.
Это является критически важным шагом для получения всех необходимых данных.

Вот как будет выглядеть часть запроса с исправленным условием ГДЕ:


ВЫБРАТЬ
    // ... другие поля ...
    ЕСТЬNULL(ЗапасыНаСкладахОстатки.КоличествоОстаток, 0) КАК Остаток
ИЗ
    ВТЗапасы КАК ВТЗапасы
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ЗапасахНаСкладах.Остатки(&НаМоментВремени, ) КАК ЗапасыНаСкладахОстатки
        ПО (ЗапасыНаСкладахОстатки.Номенклатура.Ссылка = ВТЗапасы.Номенклатура.Ссылка)
ГДЕ
    <strong>ЕСТЬNULL(ЗапасыНаСкладахОстатки.КоличествоОстаток, 0) <= 0</strong>
    И ВТЗапасы.Ссылка = &Док;

Второе решение: Проблема "Нельзя сравнивать поля неограниченной длины"

После исправления условия ГДЕ мы можем столкнуться с другой распространенной ошибкой, особенно при работе с полями ссылочных типов, такими как Номенклатура. Если мы попытаемся убрать суффикс .Ссылка в условии соединения (как было предложено в одном из сообщений форума для оптимизации), мы можем получить ошибку "Нельзя сравнивать поля неограниченной длины и поля несовместимых типов".

Эта ошибка возникает потому, что поля типа "Ссылка" в 1С часто имеют составной тип (могут ссылаться на разные справочники или документы) или могут быть представлены как "Строка неограниченной длины" в определенных контекстах или в зависимости от внутреннего представления СУБД. Система запросов 1С требует, чтобы поля, участвующие в сравнении, соединении, группировке или отборе, имели явно определенный и совместимый тип.

Если поле Номенклатура в табличных частях документов или регистрах является составным типом или его тип неявно определен как "Строка неограниченной длины", то прямое сравнение РасходнаяНакладнаяЗапасы.Номенклатура = ЗапасыНаСкладахОстатки.Номенклатура приведет к ошибке.

Решение проблемы: Явное приведение типов с помощью функции ВЫРАЗИТЬ

Для решения проблемы сравнения полей неограниченной длины или составных типов мы используем функцию ВЫРАЗИТЬ(<Выражение> КАК <Тип>). Эта функция позволяет явно указать системе запросов, какой тип данных мы ожидаем от поля, приводя его к конкретному типу справочника.

Применение ВЫРАЗИТЬ в условии соединения ПО:
Мы изменим условие соединения ПО (ЗапасыНаСкладахОстатки.Номенклатура.Ссылка = ВТЗапасы.Номенклатура.Ссылка). Вместо использования свойства .Ссылка, которое иногда может быть избыточным или приводить к указанной ошибке, мы явно приведем поля Номенклатура к типу Справочник.Номенклатура.

Исправленное условие будет выглядеть так: ПО ВЫРАЗИТЬ(РасходнаяНакладнаяЗапасы.Номенклатура КАК Справочник.Номенклатура) = ВЫРАЗИТЬ(ЗапасыНаСкладахОстатки.Номенклатура КАК Справочник.Номенклатура).

Что это дает:
- Мы гарантируем, что обе части сравнения являются ссылками на элементы справочника Номенклатура, что делает сравнение корректным и избегает ошибки "Нельзя сравнивать поля неограниченной длины".
- Этот подход является более явным и устойчивым к возможным изменениям в метаданных или неявным преобразованиям типов.

Важно: убедитесь, что поле Номенклатура действительно содержит ссылки на элементы справочника Справочник.Номенклатура. Если там могут быть ссылки на другие справочники или объекты, этот подход может отфильтровать нежелательные данные. Однако для типичных учетных задач с товарами это обычно именно то, что нужно.

Итоговый исправленный запрос

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


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

Запрос.УстановитьПараметр("Док", Док);
Запрос.УстановитьПараметр("НаМоментВремени", Объект.Дата);
РезультатЗапроса = Запрос.Выполнить;

В этом запросе мы добились следующего:

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

Такой подход обеспечивает точность выборки и корректную работу запроса.

Временные таблицы (ПОМЕСТИТЬ) и их влияние на производительность

В исходном запросе была использована временная таблица (ВТЗапасы), созданная оператором ПОМЕСТИТЬ. Давайте разберем, когда временные таблицы полезны, а когда их использование может быть избыточным или даже вредным для производительности.

Преимущества временных таблиц:

Улучшение читаемости и структуры сложного запроса:
Для очень сложных запросов, включающих множество соединений, подзапросов и агрегаций, временные таблицы могут помочь разбить логику на более мелкие, управляемые части. Это значительно упрощает понимание и отладку кода.
Повышение производительности при повторном использовании данных:
Если одни и те же промежуточные данные необходимо использовать несколько раз в одном запросе (например, в разных соединениях или подзапросах), однократное помещение их во временную таблицу может быть эффективнее, чем повторное вычисление этих данных. Система управления базами данных (СУБД) может кэшировать временную таблицу и быстрее обращаться к ней.
Индексирование:
На временные таблицы можно накладывать индексы, что значительно ускоряет операции поиска и соединения, особенно если объем данных в этих таблицах велик.

Недостатки и когда их стоит избегать:

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

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

Альтернативные типы соединений

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

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

В нашем случае, нам были нужны все позиции из расходной накладной и информация об их остатках (включая отсутствие остатка, т.е. 0). Поэтому ЛЕВОЕ СОЕДИНЕНИЕ в сочетании с корректной обработкой NULL-значений является наиболее подходящим и эффективным решением.

Заключение

Мы выяснили, что причина отсутствия нулевых остатков в выборке запроса 1С с ЛЕВЫМ СОЕДИНЕНИЕМ кроется в некорректной обработке NULL-значений в секции ГДЕ, которая эффективно превращает ЛЕВОЕ СОЕДИНЕНИЕ во ВНУТРЕННЕЕ. Для решения этой проблемы необходимо использовать функцию ЕСТЬNULL(<Поле>, <ЗначениеЗамены>), чтобы явно указать, как интерпретировать отсутствие данных. Кроме того, для корректного соединения по полям ссылочных типов, которые могут быть "неограниченной длины", мы должны применять функцию ВЫРАЗИТЬ(<Поле> КАК <Тип>), чтобы избежать ошибок типизации. Оптимизация запроса также может включать отказ от избыточных временных таблиц. Применяя эти принципы, мы можем создавать надежные и эффективные запросы в 1С, обеспечивающие точную выборку данных.