Работа с динамическими списками в 1С:Предприятие — это мощный инструмент, который, однако, может преподнести неприятные сюрпризы при переходе на новые версии SQL Server или при усложнении структуры запроса. Одной из наиболее трудноуловимых проблем является внезапное «вылетание» системы с ошибкой СУБД при попытке пользователя раскрыть группировку или установить новый порядок строк. Сегодня мы подробно разберем ситуацию, когда платформа выдает сообщение: «Microsoft SQL Server Native Client 11.0: ORDER BY items must appear in the select list if SELECT DISTINCT is specified».
Рассмотрим ситуацию на практическом примере. Представим форму списка документов, где настроена группировка по году и объекту строительства. В запросе динамического списка используется левое соединение с табличной частью для получения суммовых показателей. На тестовой базе с SQL Server 2008 все работает корректно, но при переносе на рабочую среду с SQL Server 2014 система начинает аварийно завершать работу. Выясним причину такого поведения и найдем надежное решение.
Ошибка с кодом native=145 (SQLSTATE=42000) возникает из-за строгого соблюдения стандарта SQL в отношении оператора DISTINCT. Суть правила проста: если в запросе используется SELECT DISTINCT, то каждое поле, указанное в блоке ORDER BY, обязано присутствовать в списке полей SELECT. Если SQL Server встречает в сортировке выражение, которого нет в выборке, он блокирует выполнение запроса.
Почему же это проявляется именно в 1С и именно при группировках? Платформа 1С при формировании динамического списка часто добавляет ключевое слово DISTINCT автоматически, чтобы избежать дублирования строк, когда в запросе присутствуют соединения с табличными частями. При раскрытии группировки платформа трансформирует исходный запрос, добавляя свои условия фильтрации и сортировки. В версиях SQL Server 2014 и выше оптимизатор запросов (Cardinality Estimator) работает иначе, чем в старых версиях, и более жестко интерпретирует соответствие полей.
Одной из главных причин возникновения этой ошибки является использование «обращений через точку» в тексте запроса (так называемое разыменование ссылок). Рассмотрим пример проблемного запроса:
ВЫБРАТЬ
Документ.Ссылка КАК Ссылка,
Документ.Дата КАК Дата,
Документ.Стройка.Наименование КАК СтройкаНаименование,
СУММА(Документ.Товары.Сумма) КАК СуммаИтого
ИЗ
Документ.РеализацияТоваровУслуг КАК Документ
ГРУППИРОВАТЬ ПО
Документ.Ссылка,
Документ.Дата,
Документ.Стройка.Наименование
Когда мы пишем Документ.Стройка.Наименование, платформа 1С при генерации SQL-кода создает неявное соединение. Однако при определенных условиях (особенно в динамических списках с группировками) 1С может обернуть это поле в конструкцию ISNULL(CAST(...)) в блоке GROUP BY или ORDER BY, но забыть сделать то же самое в блоке SELECT. В результате SQL Server видит разницу между «чистым» полем в выборке и «обернутым» полем в сортировке, что и приводит к падению системы.
Это самый надежный и «правильный» с точки зрения разработки способ решения проблемы. Мы уходим от автоматического разыменования ссылок и прописываем соединения вручную. Это дает нам полный контроль над тем, какие поля и в каком виде попадают в итоговый SQL-запрос.
Разберем по шагам, как переписать запрос:
ЛЕВОЕ СОЕДИНЕНИЕ.Посмотрим на пример исправленного кода запроса:
ВЫБРАТЬ
Док.Ссылка КАК Ссылка,
Док.Дата КАК Дата,
СпрСтройки.Наименование КАК СтройкаНаименование,
СУММА(ДокТовары.Сумма) КАК СуммаИтого
ИЗ
Документ.РеализацияТоваровУслуг КАК Док
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Стройки КАК СпрСтройки
ПО Док.Стройка = СпрСтройки.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг.Товары КАК ДокТовары
ПО Док.Ссылка = ДокТовары.Ссылка
ГРУППИРОВАТЬ ПО
Док.Ссылка,
Док.Дата,
СпрСтройки.Наименование
Такой подход гарантирует, что 1С сформирует более «прозрачный» SQL-код, который будет корректно воспринят оптимизатором SQL Server 2014. Использование явных соединений позволяет избежать избыточных вложенных CAST и ISNULL, которые платформа генерирует при обращении через точку.
Если запросов очень много и переписывать каждый из них нет возможности, существует системное решение на уровне СУБД. Проблема часто кроется в том, что SQL Server 2014 использует уровень совместимости 120, который включает новый механизм оценки количества строк.
Рассмотрим, как изменить уровень совместимости:
SQL Server 2012 (110) или SQL Server 2008 (100).Важный момент: понижение уровня совместимости заставит SQL Server использовать старые алгоритмы разбора запросов, которые были более лояльны к конструкциям, генерируемым 1С. Это часто решает проблему мгновенно, но лишает вас некоторых преимуществ оптимизации новых версий SQL Server.
Проанализируем еще одну важную настройку динамического списка. В свойствах списка есть поле «Основная таблица». Если оно не заполнено, платформа 1С не может применить ряд оптимизаций и часто использует DISTINCT там, где это не требуется.
Выполним следующие действия:
ОсновнаяТаблица указан конкретный объект метаданных (например, Документ.РеализацияТоваровУслуг).ДинамическоеСчитываниеДанных.Когда основная таблица указана, платформа понимает структуру данных и может строить более эффективные запросы для получения порций данных (пагинации) и раскрытия группировок, минимизируя риск возникновения конфликтов в блоках SELECT и ORDER BY.
Иногда ошибка может быть связана с устаревшей версией драйвера на сервере приложений 1С. В тексте ошибки упоминается Microsoft SQL Server Native Client 11.0. Хотя он официально поддерживает SQL Server 2014, на практике замечено, что обновление компонентов доступа к данным или переход на использование ODBC Driver for SQL Server (более свежих версий) может стабилизировать работу при выполнении сложных запросов с группировками.
Подведем итоги. Если ваш динамический список в 1С «вылетает» при раскрытии групп на SQL Server 2014, наиболее эффективным алгоритмом действий будет:
ЛЕВЫЕ СОЕДИНЕНИЯ.Соблюдение этих правил не только решит текущую проблему с ошибкой SELECT DISTINCT, но и в целом повысит производительность и стабильность работы ваших динамических списков за счет формирования более предсказуемого и оптимизированного SQL-кода.