При переходе на новые релизы платформы 1С:Предприятие (начиная с версии 8.3.15.1489 и выше) разработчики часто сталкиваются с неожиданной ошибкой при выполнении запросов, которые ранее работали абсолютно корректно. Ошибка сопровождается текстом: «Scale argument is not valid. Valid expression for data type datetime2 scale argument are integer constants and integer constant expressions». В этой статье мы подробно разберем, почему возникает эта проблема, как она связана с особенностями MS SQL Server и какими способами ее можно устранить.
Для начала проанализируем ситуацию. Ошибка возникает в момент выполнения запроса, в котором функция НАЧАЛОПЕРИОДА используется непосредственно в условии соединения (ПО) или в сложных конструкциях ГДЕ. Выясним причину: начиная с версии 8.3.15, платформа 1С изменила механизмы генерации SQL-кода для работы с датами, чтобы задействовать преимущества типа данных datetime2 в MS SQL Server.
Когда мы пишем в запросе 1С НАЧАЛОПЕРИОДА(Дата, МЕСЯЦ), транслятор платформы пытается превратить это в SQL-функцию DATETIME2FROMPARTS. У этой системной функции SQL Server есть аргумент точности (scale), который должен быть строго константой. Из-за внутренней ошибки в генераторе запросов 1С (баг №10214695), при использовании ЛЕВОЕ СОЕДИНЕНИЕ, когда в правой части может возникнуть значение NULL, этот параметр передается некорректно. SQL-сервер не может интерпретировать его как константу, что и приводит к ошибке HRESULT=80040E14.
Интересно, что при использовании периодичности МИНУТА ошибка может пропадать, так как генерируется другой SQL-код, но при использовании параметров МЕСЯЦ или ДЕНЬ проблема проявляется в полной мере.
Самый правильный архитектурный подход, который советуют опытные разработчики, — это отказ от вычислений внутри условий соединения. Рассмотрим подробнее, почему это важно. Когда мы выполняем НАЧАЛОПЕРИОДА прямо в JOIN, мы заставляем оптимизатор SQL Server выполнять вычисления для каждой строки в процессе сопоставления таблиц, что само по себе снижает производительность.
Разберем, как переписать проблемный запрос. Вместо прямого соединения:
ВЫБРАТЬ
Док1.Ссылка,
Док2.Ссылка КАК Ссылка1
ИЗ
Документ.ЗарплатаКВыплатеОрганизаций КАК Док1
ЛЕВОЕ СОЕДИНЕНИЕ
Документ.ЗарплатаКВыплате КАК Док2
ПО
НАЧАЛОПЕРИОДА(Док1.Дата, МЕСЯЦ) = НАЧАЛОПЕРИОДА(Док2.Дата, МЕСЯЦ)
Мы предлагаем сначала подготовить данные во временных таблицах. Посмотрим на пример:
// Сначала подготавливаем данные первой таблицы
ВЫБРАТЬ
Док1.Ссылка,
НАЧАЛОПЕРИОДА(Док1.Дата, МЕСЯЦ) КАК ПериодДляСвязи
ПОМЕСТИТЬ ВТ_Данные1
ИЗ
Документ.ЗарплатаКВыплатеОрганизаций КАК Док1;
// Затем подготавливаем данные второй таблицы
ВЫБРАТЬ
Док2.Ссылка,
НАЧАЛОПЕРИОДА(Док2.Дата, МЕСЯЦ) КАК ПериодДляСвязи
ПОМЕСТИТЬ ВТ_Данные2
ИЗ
Документ.ЗарплатаКВыплате КАК Док2;
// Выполняем соединение уже по готовым полям
ВЫБРАТЬ
Т1.Ссылка,
Т2.Ссылка КАК Ссылка1
ИЗ
ВТ_Данные1 КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ
ВТ_Данные2 КАК Т2
ПО
Т1.ПериодДляСвязи = Т2.ПериодДляСвязи;
В данном случае функция НАЧАЛОПЕРИОДА выполняется в простом блоке ВЫБРАТЬ, где трансляция в SQL проходит без ошибок, а само соединение происходит по уже вычисленным значениям.
Если по каким-то причинам вы не можете использовать временные таблицы (например, вы правите сложный типовой запрос в динамическом списке или в отчете на СКД), существует проверенный "лайфхак". Мы можем слегка изменить выражение, чтобы заставить платформу использовать другой механизм генерации SQL.
Проанализируем ситуацию: если обернуть дату в функцию ДОБАВИТЬКДАТЕ с нулевым смещением, платформа перестает использовать проблемную функцию DATETIME2FROMPARTS. Рассмотрим пример такого выражения:
ПО (НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(Док1.Дата, ДЕНЬ, 0), МЕСЯЦ) = НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(Док2.Дата, ДЕНЬ, 0), МЕСЯЦ))
Важный момент: использование ДОБАВИТЬКДАТЕ(Дата, ДЕНЬ, 0) фактически не меняет саму дату, но меняет способ ее обработки транслятором. Это позволяет избежать ошибки «Scale argument is not valid» без кардинальной переработки структуры запроса.
Поскольку данная проблема признана официальным багом платформы, самым надежным долгосрочным решением будет обновление до релизов, в которых эта ошибка исправлена. Проблема была устранена в версиях:
Если обновление платформы в данный момент невозможно, системные администраторы могут временно применить настройку на стороне MS SQL Server. Ошибка часто проявляется при использовании высокого уровня совместимости базы данных (Compatibility Level 130 и выше). Понижение уровня совместимости до 110 (SQL Server 2012) может устранить ошибку, так как это изменит логику работы оптимизатора с типами datetime2, однако мы рекомендуем использовать этот метод с осторожностью, так как он может повлиять на общую производительность других запросов.
Подведем итоги нашего анализа. Ошибка «Scale argument is not valid» — это следствие несовершенства транслятора запросов 1С при работе с современными типами данных SQL Server. Чтобы ее решить, мы рекомендуем:
ДОБАВИТЬКДАТЕ, если структура запроса жестко задана.