Oracle 数据库 Bug：与子查询多层嵌套有关的错误：标识符无效 - I. 案例描述

• 在Mysql中常常有如下写法
• 用相关子查询 + order by desc + limit 1来完成需求

select code,
       date,
       (select value 
         from test t1 
        where t.code = t1.code 
          and t1.date between date_sub(t.date,interval 1 year) and t.date 
        order by t1.date desc 
        limit 1) as value
  from test t

因为oracle不支持limit,所以必须改写为row_number()或者rownum,如下所示

select code,
       date,
       (select value
          from (
		       select value,row_number()over(order by t1.date desc) as rn
		         from test t1 
		        where t.code = t1.code 
		          and t1.date between date_sub(t.date,interval 1 year) and t.date
		          ) 
		  where rn <= 1
         ) as value
  from test t

把这条Sql拿到Oracle去执行,是报错的,报date标识符无效,这明显不大合理,在我的印象中,oracle是支持这种写法的,而且一般Mysql支持的特性Oracle也支持,后面发现是oracle版本的问题
我把这条Sql语句拿到Oracle 19c执行是没有问题的,而报错的oracle版本是11.2.0.3.0