Unexpected CASE evaluation logic

I always understood that the `CASE` statement worked on a 'short-circuit' principle in that evaluation of subsequent steps does not take place if a prior step is evaluated to true. (This answer Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition? is related but doesn't appear to cover this situation and relates to SQL Server).

In the following example, I wish to calculate the `MAX(amount)` between a range of months that differs based on how many months are between the start and paid dates.

(This is obviously a constructed example but the logic has valid business reasoning in the actual code where I see the issue).

If there are < 5 months between the start and paid dates then Expression 1 will be used otherwise Expression 2 will be used.

This results in the error "ORA-01428: argument '-1' is out of range" because 1 record has an invalid data condition that results in a negative value for the start of the BETWEEN clause of the ORDER BY.

Query 1

``````SELECT ref_no,
CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
-- Expression 1
MAX(amount)
OVER (PARTITION BY ref_no ORDER BY paid_date ASC
ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
AND CURRENT ROW)
ELSE
-- Expression 2
MAX(amount)
OVER (PARTITION BY ref_no ORDER BY paid_date ASC
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
END
END
FROM payment
``````

So I went for this second query to first eliminate anywhere this can occur:

``````SELECT ref_no,
CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN 0
ELSE
CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
MAX(amount)
OVER (PARTITION BY ref_no ORDER BY paid_date ASC
ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
AND CURRENT ROW)
ELSE
MAX(amount)
OVER (PARTITION BY ref_no ORDER BY paid_date ASC
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
END
END
FROM payment
``````

Unfortunately, there is some unexpected behaviour that means that the values Expression 1 WOULD use are validated, even though the statement will not be executed because the negative condition is now trapped by the outer `CASE`.

I can get around the issue by using `ABS` on the `MONTHS_BETWEEN` in Expression 1, but I feel like this should be unnecessary.

Is this behaviour as expected ? If so 'why' as it seems illogical to me and more like a bug ?

This will create a table and test data. The query is simply me checking that the correct path in the `CASE` is being taken.

``````CREATE TABLE payment
(ref_no NUMBER,
start_date DATE,
paid_date  DATE,
amount  NUMBER)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('01-01-2016','DD-MM-YYYY'),3000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('12-12-2015','DD-MM-YYYY'),5000)

INSERT INTO payment
VALUES (1001,TO_DATE('10-03-2016','DD-MM-YYYY'),TO_DATE('10-02-2016','DD-MM-YYYY'),2000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('03-03-2016','DD-MM-YYYY'),6000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('28-11-2015','DD-MM-YYYY'),10000)

SELECT ref_no,
CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN '<0'
ELSE
CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
'<5'
--    MAX(amount)
--       OVER (PARTITION BY ref_no ORDER BY paid_date ASC ROWS
--       BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
--       AND CURRENT ROW)
ELSE
'>=5'
--    MAX(amount)
--       OVER (PARTITION BY ref_no ORDER BY paid_date ASC ROWS
--       BETWEEN 5 PRECEDING AND CURRENT ROW)
END
END
FROM payment
``````
• FWIW SQL Server also has its quirks in this area where things don't quite work as advertised dba.stackexchange.com/a/12945/3690 – Martin Smith Apr 22 '16 at 12:39
• In SQL Server, placing an aggregate inside a CASE expression can force parts of the expression to be evaluated before you expect. I wonder if something similar is happening here? – Aaron Bertrand Apr 22 '16 at 12:40
• That sounds pretty close to this situation. Makes me wonder what it is about the logic for implementing CASE in two different RDBMS that leads to the same sort of effect. Interesting. – BriteSponge Apr 22 '16 at 13:33
• I wonder if this is allowed (and whether it shows the same ill behaviour): `MAX(amount) OVER (PARTITION BY ref_no ORDER BY paid_date ASC ROWS BETWEEN GREATEST(0, LEAST(5, MONTHS_BETWEEN(paid_date, start_date))) PRECEDING AND CURRENT ROW)` – ypercubeᵀᴹ Apr 26 '16 at 8:05
• @ypercubeᵀᴹ : The aggregation you suggest does not give the error. Maybe there's a limit to how 'deep' the evaluation will look. Speculation. – BriteSponge Apr 26 '16 at 8:30

So it was hard for me to determine what your actual question was from the post, but I assume it is that when you execute:

``````SELECT ref_no,
CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN 0
ELSE
CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
MAX(amount)
OVER (PARTITION BY ref_no ORDER BY paid_date ASC
ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
AND CURRENT ROW)
ELSE
MAX(amount)
OVER (PARTITION BY ref_no ORDER BY paid_date ASC
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
END
END
FROM payment
``````

You still get ORA-01428: argument '-1' is out of range?

I don't think this is a bug. I think it is an order of operation thing. Oracle needs to do the analytics on all of the rows returned by the resultset. Then it can get down to the nitty gritty of transforming the output.

A couple of additional ways around this would be to exclude the row with a where clause:

``````SELECT ref_no,
CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
-- Expression 1
MAX(amount)
OVER (PARTITION BY ref_no ORDER BY paid_date ASC
ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
AND CURRENT ROW)
ELSE
-- Expression 2
MAX(amount)
OVER (PARTITION BY ref_no ORDER BY paid_date ASC
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
END
END
FROM payment
-- this excludes the row from being processed
where MONTHS_BETWEEN(paid_date, start_date) > 0
``````

Or you could embed a case in your analytic like:

``````SELECT ref_no,
CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
-- Expression 1
MAX(amount)
OVER (PARTITION BY ref_no ORDER BY paid_date ASC
ROWS BETWEEN
-- This case will be evaluated when the analytic is evaluated
CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0
THEN 0
ELSE MONTHS_BETWEEN(paid_date, start_date)
END
PRECEDING
AND CURRENT ROW)
ELSE
-- Expression 2
MAX(amount)
OVER (PARTITION BY ref_no ORDER BY paid_date ASC
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
END
END
FROM payment
``````

Explanation

I wish I could find some documentation to back up the order of operation thing, but I haven't been able to find anything...yet.

The `CASE` short-circuit evaluation happens after the analytic function is evaluated. The order of operations for the query in question would be:

1. from payment
2. max over()
3. case.

So since the `max over()` happens before the case, the query fails.

Oracle's analytic functions would be considered a row source. If you execute an explain plan on your query, you should see a "window sort" which is the analytic, generating rows, which are fed to it by the previous row source, the payment table. A case statement is an expression that is evaluated for each row in the row source. So it makes sense (to me at least), that the case happens after the analytic.

• I appreciate the potential work arounds - its always interesting to see how others do things. However, I have and easy way to get around this; the ABS function works in my situation. Also, it is possible this isn't actually abug but if not then Oracle do need to state that the wide convention regarding 'short-circuit' logic does not apply in the case of analytic functions. – BriteSponge Apr 27 '16 at 7:59
• This answer has work arounds and a logical explanation. I don't think things will get any more definitive and so I'll mark this as the answer. Thanks – BriteSponge May 9 '16 at 7:49

SQL defines what to do, not how to do it. While normally Oracle will short-circuit case evaluation, this is an optimization and therefore will be avoided if the optimizer believes a different execution path provides superior performance. Such an optimization difference would be expected when analytics are involved.

The optimization difference is not limited to case. Your error can be reproduced using coalesce, which would normally also short circuit.

``````select coalesce(1
, max(1) OVER (partition by ref_no order by paid_date asc
rows between months_between(paid_date,start_date) preceding and current row))
from payment;
``````

There doesn't seem to be any documentation explicitly saying that short-circut evaluation can be ignored by the optimizer. The closest thing (though not close enough) I can find is this:

All SQL statements use the optimizer, a part of Oracle Database that determines the most efficient means of accessing the specified data.

This question shows short circuit evaluation being ignored even without analytics (though there is grouping).

Tom Kyte mentions that short-circuiting can be ignored in his response to a question on Order of predicate evaluation.

You should open a SR with Oracle. I suspect they will accept it as a documentation bug, and enhance the documentation in the next version to include a caveat about the optimizer.

• I was going to open an SR but it looks like that I wont be able to do that in my organisation unfortunately. – BriteSponge Apr 27 '16 at 7:56

Looks like it's windowing what makes Oracle to start evaluate all expressions in CASE. See

``````create table t (val int);
insert into t select 0  from dual;
insert into t select 1  from dual;
insert into t select -1  from dual;

select * from t;

select case when val = -1 then 999 else 2/(val + 1) end as res from t;

select case when val = -1 then 999 else 2/(val + 1 + sum(val) over())  end as res from t;

select case when val = -1 then 999 else sum(1) over(ORDER BY 1 ROWS BETWEEN val PRECEDING AND CURRENT ROW) end as res from t;

drop table t;
``````

First two queries run OK.