Oracle 9.2.05 bug with INNER JOIN ANSI?

Hi,

I'm working on a medium size project and have recently deployed the code to our client.  When work began we agreed on Oracle 9.2.0.1 as the version and all my SQL statements work fine with this version.  Now I drop the code to an Oracle 9.2.0.5 flavor of the database and by queries start having lots of problems.

The DBA there told me that INNER JOIN and LEFT OUTER JOIN ANSI queries will fail in 9.2.0.5.

My question is:  Is this true?  If so, not being a big PL/SQL expert, what can I replace the INNER JOIN AND LEFT OUTER JOIN keywords with?  I need to fix this fast!


Thanks!

LVL 1
rdytmireAsked:
Who is Participating?
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
ANSI Joins bugs fixed in 9.2.0.6 (from Metalink) :

2504168 Wrong results from query with ANSI outer join syntax and constant predicate  
2618400 ORA-907 using ANSI JOIN with FROM clause subqueries  
2711993 ANSI join with a set query block may produce wrong results  
2846640 Ambiguous columns not signalled with ANSI join syntax (wrong results instead of error)  
3087290 OERI[qcscpqbc1] possible from ANSI join  
3089870 OERI[qcstxswrite1] with full outer join  
3264527 OERI[12310] on SELECT with LEFT OUTER JOIN  
3414440 Query rewrite may spin in qsmg_process_sqltxt for ANSI join in a view  
3545642 Wrong result when using LEFT OUTER joins and TABLE() operator  
3632885 In FULL OUTER JOIN a NESTED LOOPS ANTI is used when unique index exists  
3696879 Wrong results from ANSI query with subquery in the ON condition  
3786302 ANSI join with correlated TABLE() clause fails (ORA-904)  

Your client should really apply the latest patchset...
0
 
schwertnerCommented:
LEFT OUTER JOIN - A join between two tables which returns the result of the INNER join as well as
unmatched rows from the LEFT table is a LEFT OUTER join.
 
Example:
 
    This query returns rows from the departments table even if no employees are
    assigned to it.
 
    SELECT employee_id, department_name
    FROM departments d
    LEFT OUTER JOIN employees e
    ON (e.department_id= d.department_id);
 
This is equivalent to the following pre-Oracle9i outer join notation:
 
    SELECT employee_id, department_name
    FROM departments d, employees e
    WHERE e.department_id(+) = d.department_id;



Inner Join:
The "ordinary" join that joins rows from the two tables based on the relationship of the values in columns of the tables. If "inner" is not specified then it is implicit. Merely specifying "join" is equivalent to specifying "inner join".
Examples:
      SELECT * FROM T1 INNER JOIN T2 ON T1.C1 = T2.C3
or       
      SELECT * FROM T1 JOIN T2 ON T1.C1 = T2.C3
or       
      SELECT * FROM T1 WHERE T1.C1 = T2.C3


0
 
jrb1Commented:
I don't see any documentation saying that it's a bug in 9.2.0.5.  That's a big patchset for earlier version of 9.

Anyway, 2 ways to do an inner join:

select ....
from a inner join b
on a.id = b.id

can also be written as

select ....
from a
   , b
where a.id = b.id


Two ways to write left outer join:

select ....
from a
   , b
where a.id = b.id (+)

select ....
from a left outer join b
on a.id = b.id
0
 
rdytmireAuthor Commented:
Ok.. got it for innner joins but what about POST oracle 9i notation...how to I replace LEFT OUTER JOIN notation?  I tried the (+) symbol in a test query here and no luck, appears to work just like a INNER JOIN.
0
 
rdytmireAuthor Commented:
Thanks folks while the (+) syntax never did work correctly I finally was moved to accept ploubier's answer.  I will ask the data center to upgrade to 9.2.0.6 rather than try to modify 50K lines of source code. :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.