Почему запрос 1С связывает некорректные данные при соединении по СНИЛС или ФИО?

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

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

Анализ исходной проблемы и кода запроса

Рассмотрим ситуацию, с которой столкнулся разработчик. В запрос передается внешняя таблица значений ТаблицаРезультат, содержащая антропометрические сведения сотрудников. В конце этой таблицы присутствует техническая строка, где в колонке ФИО (Колонка1) записано значение "Конец таблицы", а колонка СНИЛС (Колонка10) осталась незаполненной (содержит пустую строку "").

Для сопоставления этих сведений со справочником ФизическиеЛица используется следующий запрос:

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

При выполнении этого запроса строка "Конец таблицы" неожиданно связывается с реальными физическими лицами из базы данных. Разработчик попытался заменить ЛЕВОЕ СОЕДИНЕНИЕ на ВНУТРЕННЕЕ СОЕДИНЕНИЕ, надеясь, что некорректная строка отсечется, однако это не решило проблему: запись "Конец таблицы" все равно попала в результат, размножившись по нескольким сотрудникам. Выясним, почему это происходит.

Выясняем причину: как СУБД обрабатывает пустые значения и оператор ИЛИ

Для того чтобы понять природу этой коллизии, нам необходимо спуститься на уровень работы СУБД (MS SQL Server или PostgreSQL) и посмотреть, как вычисляются условия соединения таблиц.

Проанализируем строку "Конец таблицы" из нашей временной таблицы:

1. Поле ФИОФизЛица содержит строку "Конец таблицы".
2. Поле СНИЛС не заполнено и содержит пустую строку "".

Теперь посмотрим на справочник ФизическиеЛица. В реальных базах данных у некоторых физических лиц (особенно у архивных или только что созданных сотрудников) реквизит СтраховойНомерПФР (СНИЛС) может быть не заполнен. В системе 1С незаполненная строка хранится именно как пустая строка "".

Когда СУБД начинает выполнять соединение по условию:

ПО (ФизическиеЛица.СтраховойНомерПФР = ВТ_АнтропометрическиеСведения.СНИЛС ИЛИ ВТ_АнтропометрическиеСведения.ФИОФизЛица = ФизическиеЛица.ФИО)

Для каждого физического лица с незаполненным СНИЛС первая часть условия принимает вид:

"" = "", что возвращает значение ИСТИНА (TRUE).

Поскольку в условии соединения используется логический оператор ИЛИ (OR), СУБД достаточно истинности хотя бы одной из частей выражения. Вторая часть условия (сопоставление по ФИО) даже не анализируется. В результате строка "Конец таблицы" связывается со всеми физическими лицами в базе данных, у которых не заполнен СНИЛС.

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

Критическая проблема производительности при использовании "ИЛИ" в соединениях

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

Когда оптимизатор СУБД видит оператор ИЛИ в условии связи, он в большинстве случаев не может использовать индексы таблиц (происходит отказ от эффективной операции Index Seek в пользу тяжелой операции Index Scan или даже Table Scan). СУБД вынуждена использовать неоптимальные алгоритмы соединения, такие как вложенные циклы (Nested Loops), перебирая всю таблицу физических лиц для каждой строки вашей временной таблицы. Если в справочнике физических лиц зарегистрировано несколько десятков тысяч записей, такой запрос может полностью заблокировать работу пользователей или завершиться по таймауту.

Рассмотрим, как правильно решить эту задачу в соответствии со стандартами разработки фирмы "1С".

Способ 1. Использование двойного левого соединения (Рекомендуемый)

Для того чтобы избежать использования оператора ИЛИ и гарантировать, что пустые значения не свяжутся друг с другом, мы должны разделить сопоставление по СНИЛС и сопоставление по ФИО на два независимых соединения. При этом мы обязаны явно указать СУБД, что пустые строки связывать нельзя.

Разберем по шагам правильную структуру запроса:

ВЫБРАТЬ
    ВТ_АнтропометрическиеСведения.ФИОФизЛица КАК ФИОФизЛица,
    ВТ_АнтропометрическиеСведения.СНИЛС КАК СНИЛС,
    
    // Если физлицо найдено по СНИЛС — берем его ссылку, иначе берем ссылку, найденную по ФИО
    ЕСТЬNULL(ФизЛицаПоСНИЛС.Ссылка, ФизЛицаПоФИО.Ссылка) КАК ФизическоеЛицоСсылка,
    
    // Аналогично для остальных полей физического лица
    ЕСТЬNULL(ФизЛицаПоСНИЛС.ФИО, ФизЛицаПоФИО.ФИО) КАК ФИО,
    ЕСТЬNULL(ФизЛицаПоСНИЛС.СтраховойНомерПФР, ФизЛицаПоФИО.СтраховойНомерПФР) КАК СтраховойНомерПФР,
    
    ВТ_АнтропометрическиеСведения.Рост КАК Рост,
    ВТ_АнтропометрическиеСведения.РазмерОдежды КАК РазмерОдежды
ИЗ
    ВТ_АнтропометрическиеСведения КАК ВТ_АнтропометрическиеСведения
    
    // Первое соединение: сопоставляем только по непустому СНИЛС
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизЛицаПоСНИЛС
        ПО (ВТ_АнтропометрическиеСведения.СНИЛС <> "")
        И (ФизЛицаПоСНИЛС.СтраховойНомерПФР = ВТ_АнтропометрическиеСведения.СНИЛС)
        
    // Второе соединение: сопоставляем по ФИО только при условии, что ФИО заполнено
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизЛицаПоФИО
        ПО (ВТ_АнтропометрическиеСведения.ФИОФизЛица <> "")
        И (ФизЛицаПоФИО.ФИО = ВТ_АнтропометрическиеСведения.ФИОФизЛица)

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

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

Способ 2. Разделение логики с помощью ОБЪЕДИНИТЬ ВСЕ

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

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

// Ветка 1: Точное сопоставление по СНИЛС (только для заполненных СНИЛС)
ВЫБРАТЬ
    ВТ_АнтропометрическиеСведения.ФИОФизЛица КАК ФИОФизЛица,
    ВТ_АнтропометрическиеСведения.СНИЛС КАК СНИЛС,
    ФизическиеЛица.Ссылка КАК ФизическоеЛицоСсылка,
    ФизическиеЛица.ФИО КАК ФИО,
    ВТ_АнтропометрическиеСведения.Рост КАК Рост,
    ВТ_АнтропометрическиеСведения.РазмерОдежды КАК РазмерОдежды
ИЗ
    ВТ_АнтропометрическиеСведения КАК ВТ_АнтропометрическиеСведения
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизическиеЛица
        ПО (ВТ_АнтропометрическиеСведения.СНИЛС <> "")
        И (ФизическиеЛица.СтраховойНомерПФР = ВТ_АнтропометрическиеСведения.СНИЛС)

ОБЪЕДИНИТЬ ВСЕ

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

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

Рекомендации по предварительной очистке данных

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

// Удаляем строку-маркер конца таблицы
СтрокаКонец = ТаблицаРезультат.Найти("Конец таблицы", "Колонка1");
Если СтрокаКонец <> Неопределено Тогда
    ТаблицаРезультат.Удалить(СтрокаКонец);
КонецЕсли;

// Удаляем строки с незаполненными ключевыми полями (где нет ни ФИО, ни СНИЛС)
Индекс = ТаблицаРезультат.Количество() - 1;
Пока Индекс >= 0 Цикл
    СтрокаТаблицы = ТаблицаРезультат[Индекс];
    Если СокрЛП(СтрокаТаблицы.Колонка1) = "" И СокрЛП(СтрокаТаблицы.Колонка10) = "" Тогда
        ТаблицаРезультат.Удалить(СтрокаТаблицы);
    КонецЕсли;
    Индекс = Индекс - 1;
КонецЦикла;

Такой превентивный анализ данных на стороне клиента/сервера 1С гарантирует, что в СУБД попадет только "чистая" информация, что снизит нагрузку на сервер и убережет вас от трудноуловимых логических ошибок.

Подводя итог, всегда помните золотое правило разработки запросов в 1С: никогда не соединяйте таблицы по условию "ИЛИ" и всегда явно обрабатывайте пустые значения связующих полей. Это сэкономит вам массу времени при отладке и обеспечит стабильно высокую скорость работы вашей конфигурации.