Lógica de avaliação CASE inesperada

Portanto, foi difícil para mim determinar qual era sua pergunta real na postagem, mas presumo que quando você executa:

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

Você ainda obtém ORA-01428: o argumento '-1' está fora do intervalo ?

Eu não acho que isso é um bug. Eu acho que é uma coisa de ordem de operação. A Oracle precisa fazer análises em todas as linhas retornadas pelo conjunto de resultados. Em seguida, pode chegar ao âmago da questão de transformar a saída.

Algumas maneiras adicionais de contornar isso seriam excluir a linha com uma cláusula where:

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 

Ou você pode incorporar um caso em sua análise como:

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

Explicação

Eu gostaria de encontrar alguma documentação para fazer backup da ordem de operação, mas não consegui encontrar nada... ainda.

A CASEavaliação do curto-circuito ocorre após a avaliação da função analítica. A ordem das operações para a consulta em questão seria:

1. do pagamento
2. máximo acima()
3. caso.

Portanto, como isso max over()acontece antes do caso, a consulta falha.

As funções analíticas do Oracle seriam consideradas uma fonte de linha . Se você executar um plano de explicação em sua consulta, deverá ver uma "classificação de janela" que é a análise, gerando linhas, que são alimentadas pela fonte de linha anterior, a tabela de pagamento. Uma instrução case é uma expressão avaliada para cada linha na origem da linha. Então faz sentido (pelo menos para mim) que o caso aconteça depois da analítica.

SQL define o que fazer, não como fazer. Embora normalmente o Oracle interrompa a avaliação do caso, isso é uma otimização e, portanto, será evitada se o otimizador acreditar que um caminho de execução diferente oferece desempenho superior. Essa diferença de otimização seria esperada quando a análise estiver envolvida.

A diferença de otimização não se limita ao caso. Seu erro pode ser reproduzido usando coalescência, que normalmente também causaria um curto-circuito.

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;

Não parece haver nenhuma documentação dizendo explicitamente que a avaliação de curto-circuito pode ser ignorada pelo otimizador. A coisa mais próxima (embora não perto o suficiente) que posso encontrar é esta :

Todas as instruções SQL usam o otimizador, uma parte do Oracle Database que determina o meio mais eficiente de acessar os dados especificados.

Esta questão mostra a avaliação de curto-circuito sendo ignorada mesmo sem análise (embora haja agrupamento).

Tom Kyte menciona que o curto-circuito pode ser ignorado em sua resposta a uma pergunta sobre Ordem de avaliação de predicados .

Você deve abrir um SR com Oracle. Suspeito que eles aceitarão isso como um bug de documentação e aprimorarão a documentação na próxima versão para incluir uma ressalva sobre o otimizador.

Parece que é o janelamento que faz o Oracle começar a avaliar todas as expressões em CASE. Ver

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;

As duas primeiras consultas são executadas corretamente.