Агрегирование и group by по одинаковым полям

[Maximin]

Сегодня наступил на экзотические фигурные грабли.
AX2009 SP1 RU7

Вкратце:
Если сделать запрос по числовому полю вида select sum(<какое-то поле>) group by <какое-то поле>, то в результате, при наличии в таблице записей с одинаковым значением в <какое-то поле> получим отнюдь не одну запись с суммированным значением в <какое-то поле>, как может подумать знающий SQL (и не n - по числу строк с совпадающим значением, записей с одинаковым значением в <какое-то поле>, как может показаться неискушенному уму) . Но получим ОДНУ строку с нессуммированным значением в <какое-то поле> .

Усугубим.
Есть таблица, в которой есть индекс, в который входит <какое-то поле>. Указываем этот индекс в качестве используемого в запросе, и делаем агрегирование по <какое-то поле>. Получаем автоматом от Аксапты "подарок" в виде select агрег_ф-ция<какое-то поле>, <список полей из индекса> group by <список полей из индекса>
, что приводит к автоматическому срабатыванию вышеописанных граблей.
Поймал на РФОшном отчете, и существующем индексе на LedgerTrans с включением поля AmountMST (сильно ускоряет расчет фин. отчетности).

Причина: при таком запросе SQL сервер возвращает для поля <какое-то поле> два значения - собственно, результат агрегирующей ф-ции как БЕЗЫМЯННЫЙ СТОЛБЕЦ, вместо значения в самом поле, как ожидает Аксапта), а в самом поле, ессно, фигурирует то значение, по которому была сгруппированы записи.

Voila! Резная ручка стремительно приближается ко лбу.

[S.Kuskov]

Цитата:

Сообщение от Maximin

Получаем автоматом от Аксапты "подарок" в виде select агрег_ф-ция<какое-то поле>, <список полей из индекса> group by <список полей из индекса>

Хочу уточнить, вы говорите что аксапта сама устанавливает OrderMode в значение OrderMode::GroupBy? Можно пример запроса.

P.S.: Из-за таких особенностей возврата результата select'а в аксапте также нельзя применить одновременно две агрегирующие функции к одному и тому же полю. И когда бывает нужно определить одновременно и максимально и минимальное значение поля, приходится запрос разбивать на два.

[Maximin]

Тут следует уточнить, в чем цимес.
Запрос и так в первом случае был group by <какое-то поле>. Во втором же - OrderMode::GroupBy но не по полю <какое-то поле>, а по другим полям, и Аксапта САМА добавляет в список полей - <какое-то поле>, исходя из указанного индекса. Получаем ситуацию из первого случая.
Т.е. понять, что мы неявно получили из п.2. - п.1, можно далеко не сразу - вот в чем соль.

[trud]

т.е. вы делаете что то подобное
select sum(AmountMST) group by AmountMST
и удивляетесь, что получается фигня?
Думаю при таком запросе так и должно быть

[S.Kuskov]

Цитата:

Сообщение от Maximin

Аксапта САМА добавляет в список полей - <какое-то поле>, исходя из указанного индекса.

Что вы имеете в виду под указанием индекса? Не путаете ли вы директивы "index" и "index hint"? doens.be: Index vs Index hint

[S.Kuskov]

вот ещё ссылка в тему сортировка по индексу group by

[Maximin]

Цитата:

Сообщение от trud

т.е. вы делаете что то подобное
select sum(AmountMST) group by AmountMST
и удивляетесь, что получается фигня?
Думаю при таком запросе так и должно быть

Это не я делаю. Я делаю select sum(AmountMST) index <содержащий AmountMST> а в результате на SQL уходит то, что вы сказали. В любом случае, даже на запрос в таком виде я имею право рассчитывать получить корректный результат.

2 Kuskov Я не путаю index и index hint. В моем запросе был явно указан индекс, т.е. НЕ index hint (впрочем, надо будет попробовать указать index hint и полюбопытствовать). Я знаю о параметрах автоматического добавления GROUP BY и ORDER BY, и веду разговор о том, что при их работе появляется вот такой побочный эффект.

[S.Kuskov]

Цитата:

Сообщение от Maximin

Я знаю о параметрах автоматического добавления GROUP BY и ORDER BY, и веду разговор о том, что при их работе появляется вот такой побочный эффект.

Ну не знаю на сколько можно считать такой эфект побочным. Если составляя запрос программист осмысленно добавляет в него групировку по всем полям индекса (а именно это и происходит при использовании директивы index одновременно c group by), то вся ответственность за это лежит на программисте.

Для чего вообще может понадобиться использовать директиву index совместно с group by?
На самом деле в случае когда последовательность полей в сортировке критична для алгоритма, даже в случае с order by лучше напрямую перечислить нужные поля, пусть даже они и совпадают с текущим состоянием индекса, чем косвенно ссылаться на те же поля через индекс и в будущем огрести кучу проблем.

[Maximin]

Элементарно, Ватсон!

Приведу пару случаев. Первый паттерн использования (где поймал) я уже приводил в первом сообщении, добавлю к нему еще один.
1. Запрос внутри генератора РФО, при расчете суммы проводок использует как раз запрос с суммой по числовому полю. Попытка "подсказать" частенько выбирающему не тот индекс планировщику запросов как раз и привела к подобному финалу.
2. Да, собственно, почти любая фин. отчетность с попыткой оптимизации. Например, есть у нас "свой" генератор фин.отчетов, там запрос хранится в AOT с уже указанным индексом внутри - опять же, для скорости.

Что касается полезности индекса по числовому полю, заранее скажу - во всякого рода отчетности часто используют конструкции типа AmountMST > 0, AmountMST < 0. Более того, такой индекс как раз и был создан по рекомендациям SQL Profiler'а.

[S.Kuskov]

Цитата:

Сообщение от Maximin

Попытка "подсказать" частенько выбирающему не тот индекс планировщику запросов как раз и привела к подобному финалу.
2. Да, собственно, почти любая фин. отчетность с попыткой оптимизации.

Цитата:

Сообщение от Maximin

Я не путаю index и index hint. В моем запросе был явно указан индекс, т.е. НЕ index hint

Мы точно говорим про одно и тоже? Ещё раз.
Подсказка планировщику осущесвтляется при помощи "index hint". В этом случае сам запрос не модифицируется, хинт влияет только на выбор плана исполнения запроса.
"Index" - это принудителная сортировка/групировка по списку полей из индекса , т.е. изменение самого тела запроса.

[Maximin]

Поскольку пользователи хотят получить отчеты с "красивыми" данными, упорядоченными по контрагентам/аналитикам/счетам, сортировка/группировка при указании index, ессно, тоже нужна.