Почему перестает работать запрос 1С при сравнении результата РАЗНОСТЬДАТ с числовым параметром?

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

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

Анализ проблемы на практическом примере

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

ВЫБРАТЬ
    Компьютеры.Ссылка КАК СерийныйНомер,
    Компьютеры.Контрагент КАК Контрагент,
    КомпьютерыРабочаяСтанция.Номенклатура КАК Оборудование,
    КомпьютерыСвойства.ЗначениеСвойства КАК ДатаВвода,
    ДОБАВИТЬКДАТЕ(ВЫРАЗИТЬ(КомпьютерыСвойства.ЗначениеСвойства КАК ДАТА), МЕСЯЦ, 13) КАК ДатаОкончанияФН,
    Компьютеры.ВидРабочейСтанции.Ссылка КАК НомерФН
ИЗ
    Справочник.Компьютеры.РабочаяСтанция КАК КомпьютерыРабочаяСтанция
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Компьютеры КАК Компьютеры
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Компьютеры.Свойства КАК КомпьютерыСвойства
        ПО Компьютеры.Ссылка = КомпьютерыСвойства.Ссылка.Ссылка
    ПО КомпьютерыРабочаяСтанция.Ссылка.Ссылка = Компьютеры.Ссылка
ГДЕ
    Компьютеры.ВидРабочейСтанции = &ВидРабочейСтанции1
    И КомпьютерыСвойства.ВидСвойства В(&ВидСвойства)
    И РАЗНОСТЬДАТ(ВЫРАЗИТЬ(КомпьютерыСвойства.ЗначениеСвойства КАК ДАТА), &ТекущаяДата, МЕСЯЦ) <= 26
    И РАЗНОСТЬДАТ(ВЫРАЗИТЬ(КомпьютерыСвойства.ЗначениеСвойства КАК ДАТА), &ТекущаяДата, МЕСЯЦ) >= 1

Если в условии вместо числа 26 подставить параметр &ПериодВМесяцах, выборка перестает возвращать ожидаемые строки, несмотря на то, что в параметр передается обычное число.

Выясним причину этой проблемы:

1. Неявное несоответствие типов данных в СУБД. Встроенная функция РАЗНОСТЬДАТ на уровне СУБД преобразуется в целочисленное значение (например, DATEDIFF в MS SQL Server с типом INT). При этом числовые переменные встроенного языка 1С по умолчанию передаются в параметры СУБД как числа с фиксированной точностью (NUMERIC/DECIMAL). В зависимости от используемой СУБД и версии платформы неявное приведение типов в предикатах фильтрации может приводить к некорректному сопоставлению типов или построению неверного плана выполнения.
2. Особенности работы с полями составного типа. Реквизит ЗначениеСвойства обычно имеет составной тип. Если в строке хранится не дата, а строка или ссылка, конструкция ВЫРАЗИТЬ(КомпьютерыСвойства.ЗначениеСвойства КАК ДАТА) вернет значение NULL. Любая функция РАЗНОСТЬДАТ от NULL также возвращает NULL, а результат сравнения NULL <= &Параметр дает Ложь.

Способ 1. Явное приведение типа параметра через ВЫРАЗИТЬ

Рассмотрим быстрое решение, позволяющее устранить конфликт типов непосредственно в тексте запроса. Для этого необходимо явно указать тип передаваемого числового параметра с помощью оператора ВЫРАЗИТЬ.

Разберем, как изменить секцию ГДЕ:

ГДЕ
    Компьютеры.ВидРабочейСтанции = &ВидРабочейСтанции1
    И КомпьютерыСвойства.ВидСвойства В(&ВидСвойства)
    И РАЗНОСТЬДАТ(ВЫРАЗИТЬ(КомпьютерыСвойства.ЗначениеСвойства КАК ДАТА), &ТекущаяДата, МЕСЯЦ) <= ВЫРАЗИТЬ(&ПериодВМесяцах КАК ЧИСЛО(10, 0))
    И РАЗНОСТЬДАТ(ВЫРАЗИТЬ(КомпьютерыСвойства.ЗначениеСвойства КАК ДАТА), &ТекущаяДата, МЕСЯЦ) >= 1

Конструкция ВЫРАЗИТЬ(&ПериодВМесяцах КАК ЧИСЛО(10, 0)) жестко фиксирует длину и разрядность параметра на уровне запроса, что исключает ошибки сопоставления типов при трансляции в SQL.

Способ 2. Оптимальный подход: вычисление дат во встроенном языке (SARG)

Использование функций РАЗНОСТЬДАТ или ДОБАВИТЬКДАТЕ над полями базы данных в секции ГДЕ является неоптимальным с точки зрения производительности. СУБД не может применить индекс по полю даты, так как вынуждена вычислять функцию для каждой строки таблицы (происходит полное сканирование таблицы — Table Scan или Index Scan).

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

1. Рассчитаем граничные даты во встроенном языке до выполнения запроса с помощью функции ДобавитьМесяц.
2. Передадим полученные даты в параметры запроса.
3. Заменим функцию РАЗНОСТЬДАТ в секции ГДЕ на прямое сравнение поля даты с интервалом через оператор МЕЖДУ.

Посмотрим на пример кода во встроенном языке 1С:

// 1. Рассчитываем границы периода
ТекущаяДатаСеанса = ТекущаяДатаСеанса();
КоличествоМесяцев = 26;

// Сдвигаем даты на нужный интервал назад
ДатаНачала = ДобавитьМесяц(ТекущаяДатаСеанса, -КоличествоМесяцев);
ДатаОкончания = ДобавитьМесяц(ТекущаяДатаСеанса, -1);

// 2. Устанавливаем параметры и выполняем запрос
Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	Компьютеры.Ссылка КАК СерийныйНомер,
    |	Компьютеры.Контрагент КАК Контрагент,
    |	КомпьютерыРабочаяСтанция.Номенклатура КАК Оборудование,
    |	КомпьютерыСвойства.ЗначениеСвойства КАК ДатаВвода,
    |	ДОБАВИТЬКДАТЕ(ВЫРАЗИТЬ(КомпьютерыСвойства.ЗначениеСвойства КАК ДАТА), МЕСЯЦ, 13) КАК ДатаОкончанияФН,
    |	Компьютеры.ВидРабочейСтанции.Ссылка КАК НомерФН
    |ИЗ
    |	Справочник.Компьютеры.РабочаяСтанция КАК КомпьютерыРабочаяСтанция
    |	ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Компьютеры КАК Компьютеры
    |		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Компьютеры.Свойства КАК КомпьютерыСвойства
    |		ПО Компьютеры.Ссылка = КомпьютерыСвойства.Ссылка.Ссылка
    |	ПО КомпьютерыРабочаяСтанция.Ссылка.Ссылка = Компьютеры.Ссылка
    |ГДЕ
    |	Компьютеры.ВидРабочейСтанции = &ВидРабочейСтанции1
    |	И КомпьютерыСвойства.ВидСвойства В(&ВидСвойства)
    |	И ТИПЗНАЧЕНИЯ(КомпьютерыСвойства.ЗначениеСвойства) = ТИП(ДАТА)
    |	И ВЫРАЗИТЬ(КомпьютерыСвойства.ЗначениеСвойства КАК ДАТА) МЕЖДУ &ДатаНачала И &ДатаОкончания";

Запрос.УстановитьПараметр("ВидРабочейСтанции1", ВидРабочейСтанции);
Запрос.УстановитьПараметр("ВидСвойства", МассивВидовСвойств);
Запрос.УстановитьПараметр("ДатаНачала", ДатаНачала);
Запрос.УстановитьПараметр("ДатаОкончания", ДатаОкончания);

РезультатЗапроса = Запрос.Выполнить();

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

1. Полностью исключается проблема типизации параметров разности дат.
2. Добавлена явная проверка ТИПЗНАЧЕНИЯ(КомпьютерыСвойства.ЗначениеСвойства) = ТИП(ДАТА), что защищает от появления значений NULL при приведении типов.
3. Запрос выполняется значительно быстрее на больших объемах данных благодаря возможности использования индексов по дате.