Oracle DECODE statement failing on RHEL but works on HPUX

Oracle DECODE statement failing on RHEL but works on HPUX

I’ve isolated a very specific code piece that works on our HP-UX Oracle 11.2 environment, but fails on our RHEL 7.1 Oracle 11.2 environment. Any pointers as to why this would happen?
Everything else (except PSU level) is generally the same.
AND v_effective_date
TO_DATE (’01/01/0001′, ‘DD/MM/YYYY’))
TO_DATE (’01/01/0001′, ‘DD/MM/YYYY’))


Solution 1:

Most Entity-Attribute-Value models have a fatal flaw: stringly-typed data.

If all values are stored as strings it’s critical that attribute filtering occurs before those values are converted into a type. But Oracle’s query optimizations make it almost impossible to enforce a specific order of operations in SQL.

This question has a simple example of how bizarre this out-of-order execution can get. It’s a bit extreme, but will hopefully help you prove how unpredictable order of operations can be. You wouldn’t think this query could fail, but it does:

WITH data AS (SELECT 1 AS cond, 10 AS num, 0 AS div FROM DUAL)
  CASE WHEN cond = 2 THEN (CASE WHEN MAX(div) = 0 THEN 0 ELSE SUM(num / div) END)
       ELSE -1
  END AS result
FROM data
GROUP BY cond;

ORA-01476: divisor is equal to zero

We don’t know exactly how Oracle implements the order. Maybe it’s different between RHEL and HPUX, maybe it’s different on Thursdays. Unfortunately, even using a LEAST may not be bullet-proof. That function may logically operate in order, and it may normally use short-circuit evaluation, but it’s not guaranteed to always run in that order. You may have just switched between one 99.9% solution to another 99.9% solution.

There are only two fool-proof solutions to this, discussed in more detail in my answer here. Either change the table to use a different column for different types or add an inline view with a ROWNUM to every query. Neither of which is pleasant.