Not getting ORA-25156 "old style outer join (+) cannot be used with ANSI join" when I should be

Question

When converting some old-style joins to the SQL-92 style a (+) was accidentally left in the query. No error was raised when I expected the error ORA-25156: old style outer join (+) cannot be used with ANSI joins to be raised.

I have the following tables:

create table inner_join ( 
   id integer
 , some_data varchar2(32)
   );

insert into inner_join values (1, 'a');
insert into inner_join values (2, 'b');

create table outer_join ( 
   id integer
 , some_data varchar2(32)
   );

insert into outer_join values(2, 'c');
insert into outer_join values(3, 'd');

A (more complicated) query like the following was being converted

select *
  from outer_join oj
     , inner_join ij
 where ij.id = oj.id(+);

and by mistake the query was converted as follows

select *
  from outer_join oj
  join inner_join ij
    on ij.id = oj.id(+);

This produces the expected results, however, it's quite dangerous because:

• It's easy for someone to assume that an INNER JOIN as opposed to an OUTER JOIN was intended (guess what happened...) thereby causing unintended errors when the query is changed again
• The exhibited behaviour might change in the future, resulting in errors in a number of places

• It seems to contradict the documentation

  You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.

• A significant benefit of using the SQL-92 standard is that when there is an error in code (i.e. a missing join condition) there will be an error

I would always like ORA-25156 to be raised when it should be.

To demonstrate the issue: I do not get an error when I should if I run these queries

select *
  from outer_join oj
  join inner_join ij
    on ij.id = oj.id(+);

 ID SOME_DATA  ID SOME_DATA
--- --------- --- ---------
  2 c           2 b
                1 a


select *
  from inner_join ij
  join outer_join oj
    on ij.id = oj.id(+);

 ID SOME_DATA  ID SOME_DATA
--- --------- --- ---------
  2 b           2 c
  1 a

If I add a third table

create table middle_join ( 
   id integer
 , some_data varchar2(32)
   );

insert into middle_join values (1, 'e');
insert into middle_join values (2, 'f');
insert into middle_join values (3, 'g');

then when the old-style join is in the "middle" of the query there is no error

select *
  from inner_join ij
  join outer_join oj
    on ij.id = oj.id(+)
  join middle_join mj
    on ij.id = mj.id;

 ID SOME_DATA  ID SOME_DATA  ID SOME_DATA
--- --------- --- --------- --- ---------
  1 a                         1 e
  2 b           2 c           2 f

If the join is at the "end" of the query then the correct error(!) is raised.

select *
  from inner_join ij
  join middle_join mj
    on ij.id = mj.id
  join outer_join oj
    on ij.id = oj.id(+);
    on ij.id = oj.id(+)
               *
ERROR at line 6:
ORA-25156: old style outer join (+) cannot be used with ANSI joins

Why is this happening? How can I ensure that ORA-25156 is raised in all cases to avoid easy mistakes and future issues?

I've tested this on 12.1.0.1, but I'd be surprised if it's specific to this version...

Answer 1

I believe the error is raised when you try to use (+) in WHERE clause in ANSI Syntax query. There is no error when you try to do it in ON clause because in Oracle's old crappy syntax the ON clause was non-existent.

I guess it's messy that you can pack (+) into ON but the error is for WHERE clause.