I found a problem in SQL Developer 22.2.1 (and likely older ones too). Looks like it's explaining plan for incorrectly trimmed select.
Setup:
drop table a purge;
drop table b purge;
create table a
(POM_CODE NUMBER(9,0),
NAME VARCHAR2(4 BYTE)
);
create table b
(POM_CODE NUMBER(9,0),
NAME VARCHAR2(4 BYTE),
date_from date,
date_to date
);
insert /*+ append */ into a
select level, mod(level, 20)
from dual
connect by level < 1000000;
insert /*+ append */ into b
select level, level, date'2010-01-01', null
from dual
connect by level < 20;
commit;
Now for this select try explain plan:
select *
from a
inner join b
on a.name = b.name
where (a.name <> '1')
and (b.date_from <= date'2023-01-31'
and nvl(b.date_to,date'2099-12-31') >= date'2023-01-01');
It's OK, but when you delete the last right bracket:
select *
from a
inner join b
on a.name = b.name
where (a.name <> '1') /*<<<< put cursor before this bracket*/
and (b.date_from <= date'2023-01-31' and nvl(b.date_to,date'2099-12-31') >= date'2023-01-01';
and put the cursor before the bracket on second row before the end, then it's explaining plan for select without the last row:
If you put cursor on the last row (or at least after the bracket on second row before the end), then the ORA-00907 error is returned.
So it's bug or wad?