Как исправить ошибку «Недопустимое имя столбца» в прямом запросе 1С 7.7 и оптимизировать соединение таблиц по ID

← На главную

При написании прямых SQL-запросов к базе данных 1С:Предприятие 7.7 с использованием внешней компоненты 1С++ (1CPP) разработчики часто сталкиваются со специфическими ошибками. Две самые распространенные из них — это ошибка компиляции запроса СУБД «Недопустимое имя столбца» при обращении к реквизитам объектов и получение пустого результата при попытке связать таблицы по идентификаторам (ID). В этой статье мы подробно разберем причины возникновения этих проблем и рассмотрим эффективные методы их решения с точки зрения производительности СУБД.

Проблема №1: Ошибка «Недопустимое имя столбца» при обращении к реквизитам

Рассмотрим классическую ситуацию. Разработчик пытается выполнить простейший SQL-запрос к справочнику контрагентов, чтобы получить системный идентификатор и значение кастомного реквизита ВнутреннийКонтрагент:


SELECT
    spr.ID,
    spr.ВнутреннийКонтрагент
FROM
    $Справочник.Контрагенты as spr

При выполнении этого кода СУБД (например, MS SQL Server) возвращает ошибку: «Недопустимое имя столбца "ВнутреннийКонтрагент"». При этом разработчик абсолютно уверен, что данный реквизит существует в конфигураторе 1С.

Выясним причину такого поведения. Дело в том, что СУБД ничего не знает о метаданных конфигурации 1С. В физической таблице базы данных (например, в таблице справочника SC123) нет поля с русскоязычным названием ВнутреннийКонтрагент. Вместо этого платформа 1С хранит значения этого реквизита в колонке со специальным системным кодом, например, SP1234.

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

Символ доллара ($) является ключевым указанием для препроцессора 1С++. Проанализируем разницу в обращении к полям:

Поле spr.ID не требует символа доллара, так как ID — это реальное физическое имя текстовой колонки в таблице SQL, создаваемое платформой по умолчанию для любого справочника.
Реквизит ВнутреннийКонтрагент — это пользовательский реквизит. Чтобы препроцессор заменил его на реальное имя столбца (например, SP1234), перед ним обязательно нужно поставить символ доллара, обратившись через псевдоним таблицы: $spr.ВнутреннийКонтрагент.

Таким образом, правильный и работоспособный вариант простейшего запроса будет выглядеть следующим образом:


SELECT
    spr.ID,
    $spr.ВнутреннийКонтрагент
FROM
    $Справочник.Контрагенты as spr

В этом случае препроцессор превратит конструкцию $spr.ВнутреннийКонтрагент в условное spr.SP1234, и СУБД успешно выполнит выборку данных.

Проблема №2: Соединение таблиц по ID и пустой результат запроса

Теперь рассмотрим более сложный сценарий, с которым часто сталкиваются при выборке данных из регистров накопления. Допустим, нам необходимо получить обороты по реализации в разрезе контрагентов и отфильтровать данные по признаку ВнутреннийКонтрагент = 0. Разработчик пишет следующий запрос:


SELECT 
    сТовар.ParentID as [Товар $Справочник.Товар],
    Реал.Период as Период,
    сМестаХранения.ParentID as [Склад $Справочник.МестаХранения],
    SUM(Реал.СуммаОборот) as Сумма,
    SUM(Реал.СуммаУчетнаяОборот) as СуммаУчетная
FROM
    $РегистрОбороты.Реализация(:ВыбНачПериода,:ВыбКонПериода,Месяц, , ,(Товар,МестоХранения,Контрагент),(Сумма,СуммаУчетная)) AS Реал 
LEFT JOIN
    $Справочник.Товар as сТовар ON сТовар.ID = Реал.Товар
LEFT JOIN 
    $Справочник.МестаХранения as сМестаХранения ON сМестаХранения.ID = Реал.МестоХранения
LEFT JOIN 
    $Справочник.Контрагенты as cКонтрагенты ON cКонтрагенты.ID = Реал.Контрагент
WHERE 
    $cКонтрагенты.ВнутреннийКонтрагент = 0
GROUP BY
    сТовар.ParentID,
    Реал.Период,
    сМестаХранения.ParentID

При выполнении этого запроса выборка оказывается абсолютно пустой, хотя в базе данных гарантированно есть движения по реализации и заполненные контрагенты. Если убрать условие фильтрации или заменить связь, запрос начинает возвращать данные. Выясним, почему стандартное соединение по равенству идентификаторов cКонтрагенты.ID = Реал.Контрагент не работает.

Для этого нам необходимо детально изучить структуру хранения ссылочных типов данных в СУБД под управлением 1С:Предприятие 7.7. Платформа использует три основных формата хранения ссылок в зависимости от их типизации:

Строго типизированная ссылка (длина 9 символов): Если в метаданных реквизиту четко назначен один тип (например, строго Справочник.Контрагенты), в базе данных под него выделяется поле типа char(9). В нем хранится только уникальный 36-ричный идентификатор (ID) элемента справочника. В нашем примере поле cКонтрагенты.ID имеет длину именно 9 символов.
Нетипизированная (общая) ссылка (длина 13 символов): Если реквизит имеет составной тип, например, просто «Справочник» без конкретизации вида, или тип «Документ». В СУБД под такой реквизит выделяется поле типа char(13). В нем первые 4 символа отводятся под 36-ричный идентификатор самого вида объекта метаданных (какой именно это справочник или документ), а последующие 9 символов — это непосредственный ID элемента. В нашем регистре измерение Контрагент определено как нетипизированный справочник, поэтому поле Реал.Контрагент имеет длину 13 символов.
Абсолютно неопределенный тип (длина 23 символа): Используется для реквизитов с типом «Неопределенный» (например, виды субконто). Первые 2 символа хранят маркер типа данных (например, S для справочников), а далее следуют системные идентификаторы и ID.

Вернемся к нашему запросу. Мы пытаемся соединить таблицы по условию:

ON cКонтрагенты.ID = Реал.Контрагент

В этом выражении СУБД пытается сравнить строку длиной 9 символов (ID из справочника, например, " 1AB32D") со строкой длиной 13 символов (Вид + ID из регистра, например, "B123 1AB32D"). Естественно, эти строки никогда не будут равны. Результатом соединения LEFT JOIN для всех полей таблицы контрагентов становится значение NULL. Из-за этого последующая фильтрация WHERE $cКонтрагенты.ВнутреннийКонтрагент = 0 полностью отсекает все записи, возвращая пустой результат.

Решение проблемы соединения таблиц и оптимизация производительности

Рассмотрим два возможных способа сопоставления 9-символьного ID справочника и 13-символьной нетипизированной ссылки из таблицы регистра.

Способ №1: Использование функции SUBSTRING (не рекомендуемый)

Для обхода проблемы можно «отрезать» первые 4 символа вида метаданных из 13-символьного поля регистра, используя встроенную функцию СУБД SUBSTRING. Условие связи будет выглядеть так:

ON cКонтрагенты.ID = SUBSTRING(Реал.Контрагент, 5, 9)

Этот запрос будет работать корректно и вернет правильные данные. Однако с точки зрения производительности базы данных данный метод крайне неэффективен. Использование строковых функций СУБД в секциях соединения ON или условиях фильтрации WHERE делает выражение non-SARGable (не подлежащим оптимизации поиска).

SQL Server не сможет использовать существующие индексы по полю Реал.Контрагент. Ему придется выполнить полное сканирование таблицы (Table Scan или Index Scan) и применить функцию SUBSTRING к абсолютно каждой строке регистра, чтобы сравнить результат с ID контрагента. На больших базах данных это приведет к колоссальным задержкам и блокировкам таблиц.

Способ №2: Оптимальное соединение через конкатенацию (рекомендуемый)

Вместо того чтобы «обрезать» индексированное поле левой таблицы регистра, правильнее будет «достроить» известное нам значение правой таблицы справочника до длины 13 символов. Для этого мы можем использовать предопределенную функцию препроцессора 1С++: $ВидСправочника36.Контрагенты. Она возвращает фиксированный 4-символьный код вида справочника "Контрагенты".

Сконструируем быстрое и эффективное условие соединения:

ON Реал.Contragent = $ВидСправочника36.Контрагенты + cКонтрагенты.ID

При таком написании SQL Server сравнивает «чистое» индексированное поле Реал.Контрагент с выражением в правой части, значение которого вычисляется моментально один раз для каждой связываемой строки справочника. Это позволяет оптимизатору СУБД построить эффективный план выполнения запроса с использованием операции Index Seek (поиск по индексу), что ускоряет выполнение запроса в десятки и сотни раз на крупных информационных базах.

Итоговый пример оптимизированного запроса

С учетом всех разобранных правил, напишем финальный вариант запроса, который будет работать быстро и без ошибок:


SELECT 
    сТовар.ParentID as [Товар $Справочник.Товар],
    Реал.Период as Период,
    сМестаХранения.ParentID as [Склад $Справочник.МестаХранения],
    SUM(Реал.СуммаОборот) as Сумма,
    SUM(Реал.СуммаУчетнаяОборот) as СуммаУчетная
FROM
    $РегистрОбороты.Реализация(:ВыбНачПериода,:ВыбКонПериода,Месяц, , ,(Товар,МестоХранения,Контрагент),(Сумма,СуммаУчетная)) AS Реал 
LEFT JOIN
    $Справочник.Товар as сТовар 
    ON сТовар.ID = Реал.Товар
LEFT JOIN 
    $Справочник.МестаХранения as сМестаХранения 
    ON сМестаХранения.ID = Реал.МестоХранения
LEFT JOIN 
    $Справочник.Контрагенты as cКонтрагенты 
    ON Реал.Контрагент = $ВидСправочника36.Контрагенты + cКонтрагенты.ID
WHERE 
    $cКонтрагенты.ВнутреннийКонтрагент = 0
GROUP BY
    сТовар.ParentID,
    Реал.Период,
    сМестаХранения.ParentID

Разберем, что изменилось в этом запросе:

В секции связи справочника контрагентов применено SARGable-условие конкатенации 4-символьного кода вида справочника и 9-символьного идентификатора элемента: ON Реал.Контрагент = $ВидСправочника36.Контрагенты + cКонтрагенты.ID.
Для обращения к реквизиту фильтрации использован корректный синтаксис препроцессора 1С++ с префиксом доллара перед псевдонимом таблицы: $cКонтрагенты.ВнутреннийКонтрагент.
Запрос больше не производит лишних проверок в условиях CASE WHEN, что также снижает нагрузку на процессор сервера СУБД.

Для дальнейшего изучения тонкостей составления прямых запросов к базе данных 1С 7.7 рекомендуется обращаться к официальной документации на форумах разработчиков 1С++ (1cpp.ru), изучать файл структуры словаря данных вашей базы 1cv7.dds (или 1cv7.dd для файловых баз DBF), а также освоить базовый курс по оптимизации планов запросов в T-SQL.