При сопровождении распределенных информационных баз 1С:Предприятия 7.7 нередко возникает ситуация, когда один узел работает под управлением MS SQL Server 2008 (даже в режиме совместимости 80), а другой — на MS SQL Server 2000. При выполнении прямых запросов через компоненту 1С++ разработчики могут столкнуться с тем, что один и тот же текст запроса успешно выполняется на более новой версии СУБД, но аварийно завершается на SQL Server 2000 с диагностическим сообщением:
State 42000, native 104, message [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in the select list if the statement contains a UNION operator.
Разберем детально, почему возникает эта ошибка, где скрывается неявная сортировка и какими способами можно адаптировать запрос для стабильной работы на обеих версиях СУБД.
Проанализируем текст запроса. В самом тексте запроса явная инструкция ORDER BY отсутствует, однако в секции SELECT используется встроенный макрос метапарсера 1С++:
$ПоследнееЗначение.Номенклатура.Автор(СпрТов.ID, :Кон)
Парсер 1С++ преобразует данный макрос в скалярный подзапрос к таблице периодических реквизитов _1SCONST. Для получения актуального значения на указанную дату движок подставляет внутреннюю конструкцию следующего вида:
(SELECT TOP 1 Const.VALUE
FROM _1SCONST AS Const (NOLOCK)
WHERE Const.OBJID = СпрТов.ID
AND Const.ID = :РеквизитАвтор
AND Const.DATE <= :Кон
ORDER BY Const.DATE DESC, Const.TIME DESC, Const.DOCID DESC)
Выясним причину конфликта в парсере MS SQL Server 2000. В этой версии синтаксический анализатор имеет баг/ограничение области видимости: при наличии оператора UNION или UNION ALL на внешнем уровне анализатор ошибочно связывает внутренний ORDER BY скалярного подзапроса с общим объединением и требует, чтобы поля сортировки присутствовали в основном списке SELECT.
В MS SQL Server 2008 механизм разбора запросов полностью обновлен. Даже при включенном уровне совместимости (Compatibility Level 80) новый оптимизатор изолирует вложенные выражения и корректно обрабатывает ORDER BY внутри скалярного подзапроса без конфликтов с UNION ALL.
Самое быстрое и прямое решение проблемы без переписывания логики — скрыть конструкцию с ORDER BY от синтаксического анализатора UNION ALL. Для этого обернем первую часть запроса, содержащую вызов макроса, в производную таблицу:
INSERT INTO #ОстаткиОбороты
SELECT * FROM (
SELECT
CASE WHEN СпрТовРод1.ID IS NOT NULL
THEN СпрТовРод1.ID
ELSE
CASE WHEN СпрТовРодПервый.ID IS NOT NULL
THEN СпрТовРодПервый.ID
ELSE СпрТовРод.ID
END
END AS Группа1
,CASE WHEN (СпрТовРод1.ID IS NOT NULL) AND (СпрТовРодПервый.ID IS NOT NULL)
THEN СпрТовРодПервый.ID
ELSE СпрТовРод.ID
END AS Группа2
,CASE WHEN (СпрТовРод1.ID IS NOT NULL) AND (СпрТовРодПервый.ID IS NOT NULL)
THEN СпрТовРод.ID
ELSE NULL
END AS Группа3
,CASE WHEN СпрТовРод1.ID IS NOT NULL
THEN СпрТовРод1.CODE
ELSE
CASE WHEN СпрТовРодПервый.ID IS NOT NULL
THEN СпрТовРодПервый.CODE
ELSE СпрТовРод.CODE
END
END AS КодГруппа1
,CASE WHEN (СпрТовРод1.ID IS NOT NULL) AND (СпрТовРодПервый.ID IS NOT NULL)
THEN СпрТовРодПервый.CODE
ELSE СпрТовРод.CODE
END AS КодГруппа2
,CASE WHEN (СпрТовРод1.ID IS NOT NULL) AND (СпрТовРодПервый.ID IS NOT NULL)
THEN СпрТовРод.CODE
ELSE NULL
END AS КодГруппа3
,$ПоследнееЗначение.Номенклатура.Автор(СпрТов.ID, :Кон) AS Группа4
,0 AS КодГруппа4
,Запрос.Поставщик AS СтранаПоставщик
,Запрос.Товар AS ТоварID
,Запрос.ОстатокТовара
,Запрос.СуммаСтоимость
,Запрос.СуммаСтоимостьМПР
,Запрос.СуммаСтоимостьСети
,Запрос.СуммаДоставка
,Запрос.СуммаПриход
,Запрос.СуммаОборот
,Запрос.СуммаОборотМПР
,Запрос.СуммаОборотСети
,Запрос.СуммаСписано
,Запрос.СуммаСписаноМПР
,Запрос.СуммаСписаноСети
,Запрос.ДоставкаСписано
,Запрос.СуммаОбъем
,Запрос.СуммаОбъемМПР
,Запрос.СуммаОбъемСети
,Запрос.ПродажиКлОборот
,Запрос.ПродажиКлСписано
FROM (
-- Секция сбора движений и остатков
SELECT ...
) AS Запрос
INNER JOIN $Справочник.Номенклатура СпрТов (NOLOCK) ON СпрТов.ID = Запрос.Товар
LEFT JOIN $Справочник.Номенклатура СпрТовРод (NOLOCK) ON СпрТов.PARENTID = СпрТовРод.ID
LEFT JOIN $Справочник.Номенклатура СпрТовРодПервый (NOLOCK) ON СпрТовРод.PARENTID = СпрТовРодПервый.ID
LEFT JOIN $Справочник.Номенклатура СпрТовРод1 (NOLOCK) ON СпрТовРодПервый.PARENTID = СпрТовРод1.ID
) AS ВеткаБазы
UNION ALL
SELECT
Группа1, Группа2, Группа3,
КодГруппа1, КодГруппа2, КодГруппа3,
Группа4, КодГруппа4, СтранаПоставщик,
ТоварID, ОстатокТовара, СуммаСтоимость,
СуммаСтоимостьМПР, СуммаСтоимостьСети,
СуммаДоставка, СуммаПриход, СуммаОборот,
СуммаОборотМПР, СуммаОборотСети, СуммаСписано,
СуммаСписаноМПР, СуммаСписаноСети, ДоставкаСписано,
СуммаОбъем, СуммаОбъемМПР, СуммаОбъемСети,
ПродажиКлОборот, ПродажиКлСписано
FROM #ОстаткиОборотыШоп
Оборачивание первой выборки в SELECT * FROM (...) AS ВеткаБазы разрывает прямую связь между скалярным выражением с ORDER BY и внешним оператором UNION ALL, благодаря чему запрос без ошибок отрабатывает в MS SQL 2000.
Рассмотрим альтернативный и более производительный вариант. При выполнении макроса $ПоследнееЗначение в секции SELECT скалярный подзапрос выполняется для каждой строки результирующей выборки, что может замедлять выполнение. Мы можем вставить данные с пустым значением реквизита, а затем обновить его одной командой:
$ПустойИД AS Группа4.INSERT INTO #ОстаткиОбороты выполняем обновление таблицы:
UPDATE #ОстаткиОбороты
SET Группа4 = $ПоследнееЗначение.Номенклатура.Автор(ТоварID, :Кон)
WHERE Группа4 = $ПустойИД
Если выборка содержит большой объем номенклатуры, оптимальным архитектурным решением будет предварительная подготовка среза последних значений:
#АвторыНоменклатуры (Товар char(9), Автор char(9)).:Кон только для позиций из списка #МФНоменклатура.LEFT JOIN #АвторыНоменклатуры по идентификатору товара.Такой подход полностью устраняет синтаксические несовместимости между версиями SQL Server и значительно ускоряет выполнение итогового отчета за счет отказа от построчных скалярных подзапросов.