主页 / dba / 问题 / 136214
Accepted
BriteSponge
Asked: 2016-04-23 02:38:18 +0800 CST

意外的 CASE 评估逻辑

  • 772

我一直都明白，该CASE声明遵循“短路”原则，即如果先前步骤的评估结果为真，则不会对后续步骤进行评估。（这个答案Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?是相关的，但似乎没有涵盖这种情况并且与 SQL Server 相关）。

在下面的示例中，我希望MAX(amount)根据开始日期和支付日期之间的月份数来计算不同的月份范围。

（这显然是一个构造的示例，但逻辑在我看到问题的实际代码中具有有效的业务推理）。

如果开始日期和支付日期之间的时间小于 5 个月，则将使用表达式 1，否则将使用表达式 2。

这会导致错误“ORA-01428：参数‘-1’超出范围”，因为 1 条记录的数据条件无效，导致 ORDER BY 的 BETWEEN 子句的开头为负值。

查询 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

所以我进行了第二个查询，以首先消除可能发生这种情况的任何地方：

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

不幸的是，有一些意想不到的行为意味着表达式 1将使用的值得到验证，即使该语句不会被执行，因为否定条件现在被外部捕获CASE。

我可以通过使用ABSon the MONTHS_BETWEENin Expression 1来解决这个问题，但我觉得这应该是不必要的。

这种行为是否符合预期？如果是这样，“为什么”对我来说似乎不合逻辑，更像是一个错误？

这将创建一个表和测试数据。查询只是我检查是否采用了正确的路径CASE。

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
oracle

3 个回答

  1. Best Answer

    所以我很难从帖子中确定你的实际问题是什么，但我认为当你执行时：

    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

    您仍然得到ORA-01428: argument '-1' is out of range吗？

    我不认为这是一个错误。我认为这是一个操作顺序。Oracle 需要对结果集返回的所有行进行分析。然后它可以深入到转换输出的细节。

    解决此问题的其他几种方法是使用 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

    或者您可以在您的分析中嵌入一个案例，例如：

    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

    解释

    我希望我能找到一些文档来支持操作的顺序，但我还没有找到任何东西……还没有。

    CASE短路评估发生在解析函数评估之后。相关查询的操作顺序为：

    1. 从付款
    2. 最大超过（）
    3. 案子。

    因此，由于max over()发生在案例之前，因此查询失败。

    Oracle 的分析函数将被视为行源。如果您对查询执行解释计划，您应该看到一个“窗口排序”，它是分析生成的行，这些行由先前的行源 payment 表提供给它。case 语句是为行源中的每一行计算的表达式。因此（至少对我而言），案例发生在分析之后是有道理的。

    • 2

  2. SQL 定义了做什么，而不是如何做。虽然通常 Oracle 会短路案例评估，但这是一种优化，因此如果优化器认为不同的执行路径可提供卓越的性能，则会避免这种情况。当涉及到分析时，这种优化差异是意料之中的。

    优化差异不限于大小写。可以使用 coalesce 重现您的错误，这通常也会短路。

    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;

    似乎没有任何文档明确说明优化器可以忽略短路评估。我能找到的最接近的东西（虽然不够接近）是这样的：

    所有 SQL 语句都使用优化器，它是 Oracle 数据库的一部分，用于确定访问指定数据的最有效方式。

    这个问题表明即使没有分析也忽略了短路评估（尽管有分组）。

    Tom Kyte 在回答有关谓词评估顺序的问题时提到可以忽略短路。

    您应该使用 Oracle 打开 SR。我怀疑他们会接受它作为文档错误，并在下一个版本中增强文档以包含有关优化器的警告。

    • 1

  3. 看起来是什么让 Oracle 开始评估 CASE 中的所有表达式。看

    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;

    前两个查询运行正常。

    • -1

相关问题