oracle execution order in case condition in sql core

Question

Why does the following code not work in SQL? In pl/sql, it works correctly (after minor code edits) . According to the correct one, it should stop at checking oopname = 'PAY_OPERDATE' and not proceed to converting to a number to_number(substr(paramvalue,4,2))<=12. Tested on the oracle up to 19c.

with data as(
select null oopname, 'fgdfhdfhdf' paramvalue from dual
)
select 
  case 
    when  oopname = 'PAY_OPERDATE' 
      and length(paramvalue)=10 
      and to_number(substr(paramvalue,4,2))<=12 
    then paramvalue 
  end
from data

In PostgreSQL code works correctly

select case when oopname = 'PAY_OPERDATE'  and length(paramvalue)=10 
and TO_NUMBER(substr(paramvalue,4,2),'99') <= 12
then paramvalue end aa
from (
select ''::text oopname, '02.hh.2022y'::text paramvalue 
) a

I want to get a technical explanation. The documentation does not say how CONDITION is executed. Very similar to a bug.

Answer 1

In short, this is a semi bug.

All because of opname = 'PAY_OPERDATE'. oopname in the example is NULL, The first comparison returns NULL, and the bug (feature) is that oracle does not stop the check, although it is already known that NULL is not equal to TRUE. But since NULL is not FALSE, it checks the second condition, etc. But in the end, after all the checks, if it does not meet FALSE, it will understand that NULL AND TRUE AND TRUE is not TRUE and will not execute the THEN block

with data as(
select null oopname, 'fgdfhdfhdf' paramvalue from dual
)
select 
  case 
    when  decode(oopname,'PAY_OPERDATE',1,0) = 1
      and length(paramvalue)=10 
      and to_number(substr(paramvalue,4,2))<=12 
    then paramvalue 
  end
from data