Oracle – Unexpected CASE Evaluation Logic

oracle

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

Best Answer

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:

from payment
max over()
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.

Related Solutions

Global locking for multi-master Oracle GoldenGate replication

Keep in mind most of the work I do is with PostgreSQL so this may or may not be 100% on the money but I think this should be close enough to be helpful.

The basic issue is that in an environment like this you are going to have a great deal of trouble managing the locks. It seems you can either do some sort of conflict resolution or conflict prevention on the lock level. Conflict prevention seems like despite the performance difficulties, it would reduce the level of user frustration significantly.

My approach here would be indeed to do the locking on the central server in a pl/sql stored procedure, which would insert into the locking table if possible, returning a value indicating success, or if that is not possible, either returning a value indicating failure, or raising an exception (what I have done in the past, for example, is to return something identifying who has the lock if it is already locked).

What I would omit is actually checking with the local server. If you have a high read to write ratio and the chance of collisions is otherwise relatively small, you are going to have to check the central server most times anyway, so there isn't much to be gained by checking the local server also. Certainly you wouldn't want to write to both the local and remote servers for locking. Keep locking simple. It is likely to be a source of pain no matter what you do.

The second thing I would suggest here is that I highly recommend expiring locks like this, perhaps after 2 hours or something. There are two major reasons for doing so. The first is that bugs in the application-layer of the code can cause locks not to be released, and secondly if this is over a web interface, HTTP is stateless and therefore you have no real way of knowing that state has dropped. In this way you can give a lock which is valid for a certain period of time, can be renewed pre-emptively (in the background if needed), and times out if the individual closes the browser window and goes home for the day. Some sort of administrative utility to free locks is also something I would recommend.

I share your sense that 7 sec to acquire a lock is a significant performance cost, but in the end I don't see any better way to do this. Your options are limited significantly by the CAP theorem and a single central locking system is probably what's needed.

I suppose another option is that one could have the central server merely lock to branch location, and have the branch location release the lock as soon as no valid locks have been held for a certain period of time. This might have the advantage of allowing faster collaboration by a team, meaning that only the first editor on a team would have to incur that cost.

Oracle Table Changes – Capturing Table Changes Without Killing the Server

Just from a logical standpoint you're going to have to have 2 copies of the data, or a log of each change (including the deletes/inserts). You can use the tables/triggers like you said, use flashback query (and that will work on 11g by the way, assuming that all the archive logs for the day are still online-- and, in my experience, it's not that expensive of a task depending on the query)

You can also approach it a little differently, although this method is more complex, it might be faster to some extent, especially if there's some limit to which rows are "in scope"-- meaning, that if you can put some kind of code to ignore the vast majority of the rows in the table (like a rule that they cannot update hours that are more than a month old or something similar).

You create a second table that has all the rows that are "in scope" for the update-- it's just a plain old copy of the other table as it sits right now.

When your job runs, it first deletes records from the table that are "out of scope" and then runs a query that gets the edited/deleted rows..

select t1.a as a1, t2.a as a2, t1.b as b1, t2.b as b2 
from t1
left join t2 on t2.id=t1.id
...

Start building your file with this info Be sure to UPDATE the t2 with the new values (and perhaps delete records that were deleted)

Then you run another that gets the inserted rows Add this info to your file as well Likewise, insert those rows into table 2.

In the end, it's a coinflip on whether or not this is faster-- but at least it'll run when you can schedule it-- but the flashback query is basically the same thing and letting oracle handle it.

It's much more simple and perhaps less error prone to just use flashback query in my opinion.