Oracle三层嵌套查询的bug

Oracle三层嵌套查询的bug

如下的SQL语句查询不到任何记录，尽管数据库中有符合条件的记录。这是Oracle三层查询的bug。

select cb.* from mw_app.t_cb cb where cb.obj_id in

(select cba.channel_id from mw_app.t_current_business_alarm cba where cba.business_id in

(select b.business_id from mw_app.t_rt_business b where b.alarm_state <> '0' and b.business_code = '1') )

要想查询到结果，就需要更改sql语句，可以改成

select cb.* from mw_app.t_cb cb Left Join (select alarm.channel_id from mw_app.t_current_business_alarm alarm

where alarm.business_id in ( select business_id from mw_app.t_rt_business where alarm_state <> '0' and business_code = '1' )) b

On cb.obj_id=b.channel_id

或者

select cb.* from mw_app.t_cb cb Left Join (

select alarm.channel_id from mw_app.t_current_business_alarm alarm left join mw_app.t_rt_business biz

on alarm.business_id = biz.business_id

where (biz.alarm_state <> '0' and biz.business_code = '1') ) b

On cb.obj_id=b.channel_id