一个奇怪的case when

最新推荐文章于 2023-08-27 19:49:41 发布

原创最新推荐文章于 2023-08-27 19:49:41 发布 · 449 阅读

CC 4.0 BY-SA版权

文章标签：

昨天写一个查询，要求取到一些时间字段到现在的时间段，于是想到在where 后面写case when语句把时间分段，但是在实际实验中发现，当参数满足了第二条when表达式之后，还会继续满足后面的when表达式，甚至于满足所有的when表达式。于是把问题抽象一下，写成了下面的SQL语句，发现结果变得非常有趣：

/*Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
**Edit date: 2011-08-16
*/
with a as
 (select '2010.01' as c1, 'a' as c2, 4 as c3 from dual
 union all
  select '1999.10' as c1, 'b' as c2, 5 as c3 from dual
 union all
  select '1970.01' as c1, 'c' as c2, 6 as c3 from dual)

select case
    when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 20 then '10'
    when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) >= 20 and MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 400 then '20'
    when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) >= 400 and MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 600 then '30'
    else '0'
    end flag,
    a.* from a
  where 'AL' = 'ALL'  or  '20' = case
    when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 20  then '10'
    when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) >= 20 and MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 400 then '20'
    when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) >= 400 and MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 600 then '30'
    else '0'
   end

运行结果如下：

flag    c1          c2     c3
20	1999.10	     b	    5
30	1970.01	     c	    6

可以看到，当第二个when表达式的返回结果‘20’正好满足条件，但是运行之后却把第二和第三个when的结果全查询出来了。

经过反复修改，发现把前面并未执行的 ‘AL’ = 'ALL' or 注释之后，结果正确；或者把第一个when 的表达式改为

 MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 20  and MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) > 0

结果也会正确；把后面的else语句去掉或者改为 else null 返回空值结果也会正确。但是偏偏这个看上去并没有什么语法或者逻辑错误的SQL语句会执行不正确。

经过和同事的研究，还是不知所以然，因为这个问题好像在9i和11g的版本上执行时并不会出现，也许，这是一个蛋疼的BUG吧……