При разработке отчетов или алгоритмов сравнения данных в 1С мы часто сталкиваемся с необходимостью соединить две таблицы. Однако, если в ключевых полях соединения присутствуют пустые значения, а именно NULL, результат может оказаться неожиданным: вместо корректного соединения мы получаем задвоение строк. Разберем эту ситуацию подробно, выясним причину такого поведения платформы и проанализируем несколько способов решения проблемы от простых до архитектурно правильных.
Для начала выясним причину, по которой классическое соединение таблиц дает сбой. В основе SQL и языка запросов 1С лежит концепция троичной логики. Логические выражения могут принимать три значения: Истина, Ложь и Неизвестно. Значение NULL означает «отсутствие значения» или то, что значение «неизвестно».
С точки зрения системы управления базами данных (СУБД), нельзя утверждать, что одно неизвестное значение равно другому неизвестному значению. Поэтому результат вычисления выражения NULL = NULL возвращает Unknown (Неизвестно). При оценке условий соединения (в секции ПО) СУБД трактует результат «Неизвестно» как «Ложь». Из-за этого строки с NULL не связываются между собой, и при полном или левом соединении происходит декартово произведение не связанных остатков — то самое задвоение строк.
Рассмотрим пример запроса, который приводит к проблеме:
ВЫБРАТЬ ПЕРВЫЕ 1
"Й" КАК Номенклатура,
NULL КАК ВидЦены
ПОМЕСТИТЬ Таб1
ИЗ
Справочник.Номенклатура КАК НН
;
ВЫБРАТЬ
ЕСТЬNULL(ЦН.Номенклатура, ЦС.Номенклатура) КАК Номенклатура,
ЕСТЬNULL(ЦН.ВидЦены, ЦС.ВидЦены) КАК ВидЦены
ИЗ
Таб1 КАК ЦН
ПОЛНОЕ СОЕДИНЕНИЕ Таб1 КАК ЦС
ПО ЦН.Номенклатура = ЦС.Номенклатура
И ЦН.ВидЦены = ЦС.ВидЦены
Результатом работы этого запроса будет таблица из двух строк, хотя мы соединяли одну строку саму с собой.
Часто разработчики пытаются решить проблему «в лоб», добавляя прямую проверку на NULL в условие соединения. Посмотрим на пример такой конструкции:
ПО ЦН.Номенклатура = ЦС.Номенклатура
И (ЦН.ВидЦены = ЦС.ВидЦены ИЛИ (ЦН.ВидЦены ЕСТЬ NULL И ЦС.ВидЦены ЕСТЬ NULL))
Важно: использовать такую конструкцию в реальных базах данных категорически не рекомендуется. Это классический антипаттерн в 1С. Из-за оператора ИЛИ (OR) в условии соединения СУБД не сможет использовать оптимальные алгоритмы соединения таблиц, такие как Hash Join. Вместо этого база данных будет вынуждена перебирать строки вложенными циклами (Nested Loops) или сканировать таблицы целиком. На реальных объемах данных (например, при соединении большого справочника номенклатуры и регистра с ценами) такой запрос может полностью парализовать работу системы.
Если соединение именно по таким полям архитектурно необходимо, оптимальнее всего будет привести NULL к пустому значению конкретного типа с обеих сторон равенства. Для этого мы используем функцию ЕСТЬNULL().
Чтобы избежать неявного приведения типов на стороне СУБД (которое также может замедлить запрос), важно приводить значение не просто к пустой строке или нулю, а к пустой ссылке того же типа, который ожидается в поле. Рассмотрим пример:
ПО ЕСТЬNULL(Таб1.ВидЦены, ЗНАЧЕНИЕ(Справочник.ВидыЦен.ПустаяСсылка)) = ЕСТЬNULL(Таб2.ВидЦены, ЗНАЧЕНИЕ(Справочник.ВидыЦен.ПустаяСсылка))
В этом случае обе стороны при отсутствии данных получат пустую ссылку, и равенство вернет «Истина».
В платформе 1С существует важное отличие NULL от Неопределено. В то время как NULL — это отсутствие значения в реляционной модели, Неопределено — это реальное платформенное значение (обозначающее пустоту для полей составного типа). Условие Неопределено = Неопределено всегда возвращает Истина.
Проанализируем, как можно преобразовать данные:
ВЫБРАТЬ
ЕСТЬNULL(Таб.ВидЦены, НЕОПРЕДЕЛЕНО) КАК ВидЦены
Такой подход избавляет от дополнительных сложных условий в секции ПО. Однако стоит учитывать, что это создает поле составного типа в таблице СУБД, что работает чуть медленнее, чем сравнение конкретных пустых ссылок одного типа.
Рассмотрим самую частую бизнес-задачу, которая приводит к подобным запросам: нам нужно сравнить цены на разные даты (например, получить срез цен на Дату 1 и Дату 2). Как мы выяснили, соединять такие виртуальные таблицы по измерениям, которых может не быть (из-за чего и вылезает NULL), чревато ошибками и падением производительности.
Архитектурно правильный способ обработки таких задач в 1С — использовать ОБЪЕДИНЕНИЕ, а не соединение. Разберем этот подход по шагам:
ОБЪЕДИНИТЬ ВСЕ.Цена1, а для второй даты создаем искусственную пустую колонку (например, 0 КАК Цена2).0 КАК Цена1, а во вторую колонку выбираем реальную сумму.СГРУППИРОВАТЬ ПО) по Номенклатуре и Виду Цены.СУММА(Цена1) и СУММА(Цена2), или через МАКСИМУМ().Посмотрим на пример реализации данного алгоритма:
ВЫБРАТЬ
ВложенныйЗапрос.Номенклатура КАК Номенклатура,
ВложенныйЗапрос.ВидЦены КАК ВидЦены,
МАКСИМУМ(ВложенныйЗапрос.Цена1) КАК Цена1,
МАКСИМУМ(ВложенныйЗапрос.Цена2) КАК Цена2
ИЗ
(ВЫБРАТЬ
Цены1.Номенклатура КАК Номенклатура,
Цены1.ВидЦены КАК ВидЦены,
Цены1.Цена КАК Цена1,
0 КАК Цена2
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата1, ) КАК Цены1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Цены2.Номенклатура КАК Номенклатура,
Цены2.ВидЦены КАК ВидЦены,
0 КАК Цена1,
Цены2.Цена КАК Цена2
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата2, ) КАК Цены2) КАК ВложенныйЗапрос
СГРУППИРОВАТЬ ПО
ВложенныйЗапрос.Номенклатура,
ВложенныйЗапрос.ВидЦены
Преимущества данного подхода:
При использовании группировки платформа сама идеально «схлопнет» нужные строки. Если цены на одну из дат не было, в итоговой таблице на этом месте будет стоять 0, и никакого NULL не возникнет. Этот алгоритм не только полностью решает логическую проблему задвоения строк, но и выполняется СУБД в разы быстрее, чем тяжелое ПОЛНОЕ СОЕДИНЕНИЕ виртуальных таблиц.