Почему оценивается запрос с невозможным оператором CASE?
У меня такой запрос:
SELECT
CASE
WHEN test.formula_type = 2
THEN EXP(SUM(LN(calculated_value)))
ELSE 1
END AS result
FROM (
SELECT
0 AS calculated_value,
1 AS formula_type
GROUP BY formula_type
) AS test
GROUP BY formula_type
Когда я запускаю его, я получаю:
psql:__scribble.sql:16: ERROR: cannot take logarithm of zero
Что имело бы смысл, если бы 4-я строка была выполнена, поскольку для calculated_value установлено значение 0.
Однако для выполнения строки 4 formula_type должен быть равен 2, что, конечно, неверно.
Я предполагаю, что PostgreSQL пытается провести здесь некоторую оптимизацию и вычислить значение до того, как оно понадобится.
Но я не понимаю, почему вычисление этого значения, для которого требуется значение из подзапроса, было бы хорошей оптимизацией, поскольку ему придется делать это для каждой отдельной строки, даже если условие может быть истинным только в 1% случаев. .
Если бы это было жестко запрограммированное значение, такое как SUM (LN (0)), тогда имело бы смысл рассчитать значение заранее в качестве оптимизации. Но в этом случае не похоже, что это приведет к более быстрым вычислениям.
Итак, у меня вопрос, что именно здесь происходит? Почему PostgreSQL пытается вычислить случай, которого никогда не бывает?
Интересно, что если я удалю предложение GROUP BY подзапроса, запрос будет выполняться так, как я ожидал.
Примечание: для ясности, я не спрашиваю, почему я получаю это сообщение об ошибке при попытке получить натуральный логарифм 0. Я спрашиваю, почему он вообще пытается получить логарифм 0.
Ответы 2
Проблема в том, что взаимодействие агрегирования с вычислением выражений иногда требует, чтобы выражения вычислялись «раньше».
Просто используйте nullif ():
THEN EXP(SUM(LN(NULLIF(calculated_value, 0))))
Сумма вычисляется как часть группировки, и поскольку выражение CASE содержит агрегатную функцию, ее можно вычислить только после того, как группировка (и суммирование) будет выполнено. Документация предупреждает о таких возможностях с помощью
Note
As described in Section 4.2.14, there are various situations in which subexpressions of an expression are evaluated at different times, so that the principle that “CASE evaluates only necessary subexpressions” is not ironclad. For example a constant 1/0 subexpression will usually result in a division-by-zero failure at planning time, even if it's within a CASE arm that would never be entered at run time.
Связанная страница еще более ясна:
Another limitation of the same kind is that a
CASEcannot prevent evaluation of an aggregate expression contained within it, because aggregate expressions are computed before other expressions in aSELECTlist orHAVINGclause are considered. For example, the following query can cause a division-by-zero error despite seemingly having protected against it:SELECT CASE WHEN min(employees) > 0 THEN avg(expenses / employees) END FROM departments;The
min()andavg()aggregates are computed concurrently over all the input rows, so if any row has employees equal to zero, the division-by-zero error will occur before there is any opportunity to test the result ofmin(). Instead, use aWHEREorFILTERclause to prevent problematic input rows from reaching an aggregate function in the first place.
Интересно, связано ли с этим отсутствие квалификации в ваших расчетах? Возможно, поскольку вы квалифицируете test.formula_type, но не Calculated_value, он вычисляет значение Calculated в другом порядке, чем при явной квалификации? Оптимизаторы СУБД делают некоторые удивительные вещи под капотом, и PostgreSQL вдвойне.