Error with outer join

From: FatP <thatsang_at_gmail.com>
Date: Fri, 13 Jul 2007 01:06:34 -0700
Message-ID: <1184313994.423437.231120@i38g2000prf.googlegroups.com>

It seems Oracle is a bit over sensitive when something happen in the column used for outer join. See the following example

SQL> create table t1 (n1 number);

Table created.

SQL> create table t2 (n2 number);

Table created.

SQL> select * from t1, t2
  2 where t1.n1 (+) = t2.n2;

no rows selected

SQL> select * from t1, t2
  2 where (t1.n1+1) (+)= t2.n2;
where (t1.n1+1) (+)= t2.n2

                  *

ERROR at line 2:
ORA-00920: invalid relational operator

SQL> select * from t1, t2
  2 where (t1.n1) (+)= t2.n2;
where (t1.n1) (+)= t2.n2

                 *

ERROR at line 2:
ORA-00920: invalid relational operator

It seems to reject whatever containing the parenthesis in the outer join.

Another strange thing is that the order of operands in arithematic operations affects outer join

SQL> select * from t1, t2
  2* where 1+t1.n1 (+)= t2.n2
SQL> / no rows selected

SQL> select * from t1, t2
  2* where t1.n1+1 (+)= t2.n2
SQL> /
where t1.n1+1 (+)= t2.n2

               *
ERROR at line 2:
ORA-00920: invalid relational operator

I know that there are workarounds for this issue, e.g., using ANSI outer join or create an inline view. I just want to know whether this is by design, and whether there's any more reasonable workaround. It also surprised me that this is not noticed by many people(I tried 9i and 10g), as it seems quite common to do some 'pre-processing' before join.

System information:
Oracle Database 10g Release 10.2.0.1.0 - Production PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production

Linux: RHEL4 Received on Fri Jul 13 2007 - 03:06:34 CDT