Как правильно использовать оператор НЕ ПОДОБНО в запросах 1С и почему пропадают пустые строки?

← На главную

Разберем классическую, но довольно коварную проблему, с которой часто сталкиваются разработчики при написании запросов в 1С. Сценарий следующий: необходимо выбрать данные из табличной части документа, исключив при этом строки, в которых текстовое представление определенного реквизита (например, наименование способа отражения зарплаты в бухучете) начинается на заданные символы (скажем, на "91" или "71").

На первый взгляд задача кажется тривиальной и решается использованием оператора ПОДОБНО с отрицанием. Однако на практике разработчики часто сталкиваются с тем, что запрос возвращает пустой результат, либо из выборки бесследно «пропадают» строки, в которых проверяемый реквизит вообще не заполнен. Давайте подробно проанализируем причины этого поведения и рассмотрим правильные варианты решения.

Причина 1. Коварство логического «ИЛИ» при отрицании

Первая ошибка, которую часто совершают при попытке исключить несколько шаблонов, кроется в неверном использовании логических операторов. Рассмотрим условие, которое изначально пытался применить автор темы:


И (СпособОтражения.Наименование НЕ ПОДОБНО &Сч91 
    ИЛИ СпособОтражения.Наименование НЕ ПОДОБНО &Сч71)

С точки зрения формальной математической логики (законов де Моргана), связка НЕ А ИЛИ НЕ В всегда будет возвращать значение ИСТИНА для абсолютно любой строки (кроме значений NULL, о которых мы поговорим ниже). Проанализируем ситуацию:

Если наименование начинается на "91" (например, "91.2 Субсидия"), то первая часть условия (НЕ ПОДОБНО "91%") ложна. Но вторая часть условия (НЕ ПОДОБНО "71%") при этом истинна! Так как между ними стоит оператор ИЛИ, общее выражение вычисляется как ИСТИНА, и строка попадает в результат.
Если наименование начинается на "71" (например, "71.01 Подотчет"), то первая часть условия истинна, а вторая ложна. Общий результат снова ИСТИНА.

Для правильного исключения нескольких текстовых шаблонов условия необходимо связывать строго через логическое И:


И (НЕ СпособОтражения.Наименование ПОДОБНО &Сч91 
    И НЕ СпособОтражения.Наименование ПОДОБНО &Сч71)

Или, что эквивалентно, через отрицание всей группы условий, объединенных оператором ИЛИ:


И НЕ (СпособОтражения.Наименование ПОДОБНО &Сч91 
      ИЛИ СпособОтражения.Наименование ПОДОБНО &Сч71)

Причина 2. Поведение NULL в СУБД (почему пропадают незаполненные строки)

Вторая и самая неочевидная проблема — это полное исчезновение из результата строк, у которых способ отражения не выбран вовсе (пустая ссылка).

Когда в запросе происходит обращение через точку к реквизиту ссылочного поля (СпособОтраженияЗарплатыВБухучете.Наименование), платформа 1С на уровне СУБД автоматически строит неявное ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN) с таблицей этого справочника. Если в исходной строке документа ссылка пустая, результатом этого соединения в поле наименования будет значение NULL.

В трехуровневой логике SQL любое сравнение с NULL (будь то равенство, неравенство или оператор ПОДОБНО) всегда возвращает результат UNKNOWN (NULL), а не истину или ложь:

Выражение NULL ПОДОБНО "91%" возвращает NULL.
Выражение НЕ (NULL ПОДОБНО "91%") также возвращает NULL.

Поскольку СУБД оставляет в итоговой выборке только те строки, для которых условие в секции ГДЕ вернуло строго ИСТИНА, все строки с незаполненным реквизитом отсекаются. СУБД просто не может определить, «подобно» ли пустое значение вашему шаблону или нет.

Чтобы решить эту проблему, значение NULL необходимо обрабатывать явно с помощью функции ЕСТЬNULL или конструкции ЕСТЬ NULL:


И (НЕ ЕСТЬNULL(ОтражениеЗарплатыВБухучете.СпособОтраженияЗарплатыВБухучете.Наименование, "") ПОДОБНО &Сч91
    И НЕ ЕСТЬNULL(ОтражениеЗарплатыВБухучете.СпособОтраженияЗарплатыВБухучете.Наименование, "") ПОДОБНО &Сч71)

Или через прямую проверку на незаполненность реквизита-ссылки:


И (ОтражениеЗарплатыВБухучете.СпособОтраженияЗарплатыВБухучете ЕСТЬ NULL 
    ИЛИ (НЕ ОтражениеЗарплатыВБухучете.СпособОтраженияЗарплатыВБухучете.Наименование ПОДОБНО &Сч91 
         И НЕ ОтражениеЗарплатыВБухучете.СпособОтраженияЗарплатыВБухучете.Наименование ПОДОБНО &Сч71))

Причина 3. Ошибки при передаче параметров и спецсимволов

Попытки динамически склеивать строки непосредственно внутри текста запроса (например, ПОДОБНО &Сч91 + "%") или использовать сложные регулярные выражения в параметрах (типа [^9][^1]%) часто приводят к ошибкам компиляции или непредсказуемому поведению СУБД.

Стандарты разработки 1С предписывают готовить шаблон поиска на встроенном языке и передавать его в запрос как готовый параметр целиком. Символы отрицания в квадратных скобках [^...] не являются кроссплатформенными и поддерживаются далеко не всеми СУБД (например, они не работают в IBM DB2). Используйте только универсальные спецсимволы: % (любое количество произвольных символов) и _ (один произвольный символ).

Правильный пример подготовки параметров в коде 1С:


Запрос.УстановитьПараметр("Сч91", "91%");
Запрос.УстановитьПараметр("Сч71", "71%");

А в тексте запроса используйте лаконичную конструкцию:


И НЕ ОтражениеЗарплатыВБухучете.СпособОтраженияЗарплатыВБухучете.Наименование ПОДОБНО &Сч91

Оптимальный архитектурный подход (Решение через временные таблицы)

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

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

Рассмотрим пример реализации такого запроса по шагам:


ВЫБРАТЬ
    СпособыОтражения.Ссылка КАК СпособОтражения
ПОМЕСТИТЬ ВтИсключаемыеСпособы
ИЗ
    Справочник.СпособыОтраженияЗарплатыВБухучете КАК СпособыОтражения
ГДЕ
    СпособыОтражения.Наименование ПОДОБНО &Сч91
    ИЛИ СпособыОтражения.Наименование ПОДОБНО &Сч71
;

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

Преимущества данного подхода:

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