Hi
Following (very simplified) pieces of code get parsed (with some exceptions) on 19C and failing to parse on 11.2 .
Versions:
19c: 19.3.0.0.190416, Red Hat 7
11.2: 11.2.0.4.8, Red Hat 6
create table table1 (id number, sid varchar2(10));
create table table2 (id number, sid varchar2(10));
create table table3 (id number, sid varchar2(10));
-- First query
select table1.id
from table1
inner join table2 on table1.id = table2.id
inner join table3 on table1.sid = table3.sid
right outer join table2 on table2.sid = table3.sid
where table2.id is not null;
-- Second query
select j1.id
from table1 j1
inner join table2 j2 on j1.id = j2.id
inner join table3 j3 on j1.sid = j3.sid
right outer join table2 j4 on j4.sid = j3.sid
where j2.id is not null;
-- ddl based on above join
create view fj as select table1.id
from table1
inner join table2 on table1.id = table2.id
inner join table3 on table1.sid = table3.sid
right outer join table2 on table2.sid = table3.sid
where table2.id is not null;
19C:
1. no rows selected
2. no rows selected
3. error
where table2.id is not null
*
ERROR at line 6:
ORA-00918: column ambiguously defined
11.2:
1. error
where table2.id is not null
*
ERROR at line 6:
ORA-00918: column ambiguously defined
2. no rows selected
3. error
where table2.id is not null
*
ERROR at line 6:
ORA-00918: column ambiguously defined
Thoughts.
19C have some bug in parsing and probably need to be fixed. At least in my opinion only the second query is correct. The fact that DDL switch behavior seems to be suspicious.
11.2 Working correctly and not parsing incorrect queries.
When use ANSI SQL you must to be careful when writing query and test it on all versions where it should run.
Or am I absolutely wrong and both versions should run and the issue is with 11.2?
Any thoughts would be very welcome.
Thanks
Evgeni