Я столкнулся с очень необычным поведением функции LISTAGG в Oracle.
Я знаю, что LISTAGG не работает, если он имеет дело с более чем 4000 символов.
Поскольку я знаю это, у меня был оператор CASE для замены ячеек с количеством символов более 100 на сообщение «Слишком много для подсчета».
CREATE TABLE EMP (
ID VARCHAR2(401),
DEP VARCHAR2(10)
);
INSERT INTO EMP VALUES (DBMS_RANDOM.string('A', 401), 'FOO'); -- Run exactly 9 times
INSERT INTO EMP VALUES (DBMS_RANDOM.string('A', 5), 'BAR'); -- Run 3 times
Для простоты давайте проигнорируем мой особый случай для количества > 100 и просто скажем, что FOO следует исключить, а BAR следует включить.
SELECT DEP,
CASE
WHEN DEP = 'BAR' THEN
LISTAGG(ID, ',')
WITHIN GROUP (ORDER BY NULL)
OVER (PARTITION BY DEP)
ELSE
'Too many to count'
END AS ID_LIST
FROM EMP;
Это дает результаты, которые должны выглядеть так (но с другими случайными символами):
Однако, добавив всего одну дополнительную строку, доведя общее количество в отделе FOO до 10...
INSERT INTO EMP VALUES (DBMS_RANDOM.string('A', 401), 'FOO'); -- Same as before
Заставляет нас столкнуться с исключением при повторном запуске того же выбора:
ORA-01489: result of string concatenation is too long
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.
Как ни странно, это происходит, даже если условие в операторе case изменено на 1=2.
Я не уверен, что здесь происходит. Кажется, что SQL решает оценить оператор независимо от того, намерен ли он его использовать, поэтому терпит неудачу, когда встречается с LISTAGG из 4000+ символов.
У меня есть несколько решений моей проблемы, но я действительно хотел бы узнать больше о том, почему SQL решил (очевидно) запустить LISTAGG, даже если он никогда не должен быть достигнут.