Oracle 11g递归子查询分解中日期比较的异常问题

表结构与数据 #

trans_info表的结构和数据如下：

第一段SQL及执行结果 #

执行以下SQL：

with m1 as (select ti.date_d,ti.weight,row_number() over(order by ti.date_d) rn from trans_info ti ),
m2(date_d,weight,rn,start_date) as (
    select date_d,weight,rn,date_d as start_date from m1 where rn=1 
    union all 
    select a.date_d,a.weight,a.rn, 
           case when b.start_date+4 <= a.date_d then a.date_d else b.start_date end 
    from m1 a,m2 b where b.rn+1=a.rn
) 
select date_d, weight, start_date from m2

得到结果：

第二段SQL及执行结果 #

修改SQL中的日期比较逻辑后：

with m1 as (select ti.date_d,ti.weight,row_number() over(order by ti.date_d) rn from trans_info ti ),
m2(date_d,weight,rn,start_date) as (
    select date_d,weight,rn,date_d as start_date from m1 where rn=1 
    union all 
    select a.date_d,a.weight,a.rn, 
           case when 4 <= a.date_d-b.start_date then a.date_d else b.start_date end 
    from m1 a,m2 b where b.rn+1=a.rn
) 
select date_d, weight, start_date from m2

得到结果：

原因分析 #

两段SQL的核心差异在于日期比较的运算逻辑，本质是字段类型与Oracle隐式转换规则导致的：

1. 字段类型的关键影响 #

你的date_d字段大概率是VARCHAR2字符串类型，而非DATE类型，这导致两种写法的运算逻辑完全不同：

• 写法1：b.start_date+4 <= a.date_d
  字符串类型使用+号时，Oracle会将其解析为字符串拼接，而非日期加法。例如'2016-01-01' + '4'会生成字符串'2016-01-014'，随后与a.date_d（'2016-01-02'）做字符串逐位对比：
  第9位字符'1'小于'2'，因此'2016-01-014' <= '2016-01-02'结果为TRUE，触发case分支取a.date_d作为新的start_date。

• 写法2：4 <= a.date_d - b.start_date
  字符串类型使用-号时，Oracle会触发隐式类型转换，将字符串转为DATE类型后计算天数差。例如'2016-01-02' - '2016-01-01'得到天数差1，4 <= 1结果为FALSE，因此case分支取b.start_date作为新的start_date。

2. 递归CTE的累积效应 #

递归过程中，start_date的取值会传递到下一次递归：

• 第一段SQL中，rn=2的start_date更新为2016-01-02，rn=3时b.start_date+4生成'2016-01-024'，与'2016-01-03'对比仍为TRUE，start_date继续更新；rn=4时'2016-01-034'与'2016-01-05'对比仍为TRUE，最终start_date更新为2016-01-05。
• 第二段SQL中，rn=2的start_date保持2016-01-01，rn=3时天数差为2，4<=2为FALSE，start_date不变；rn=4时天数差为4，4<=4为TRUE，start_date更新为2016-01-05。

验证与修正建议 #

1. 确认字段类型：

DESCRIBE trans_info;

2. 修正写法，确保日期运算的正确性（如果是字符串类型）：

-- 显式转换为DATE类型后再运算
case when TO_DATE(b.start_date, 'YYYY-MM-DD') +4 <= TO_DATE(a.date_d, 'YYYY-MM-DD') then a.date_d else b.start_date end

3. 最优方案：将date_d字段类型改为DATE，彻底避免隐式转换带来的逻辑混乱。

内容的提问来源于stack exchange，提问作者WflytoC