BUG: Outer Join with Another Table in FROM May Cause SQL

SYMPTOMS

A left outer join between two tables along with a third table in the FROM clause can cause SQL_ERROR to be returned. This happens only when the outer join escape syntax is used and the third table appears after the escape sequence in the FROM clause.

SQLExecDirect:

   SELECT t1.col1, t1.col2, t2.col1, t2.col2
   FROM {oj testoj1 t1 left outer join testoj2 t2 on t1.col1 = t2.col1},
        testoj3 t3,
   WHERE t3.col1 = t1.col1

This generates a syntax error:

SQLSTATE = 37000 NativeError = 102
ErrorMsg = [Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax near ','

CAUSE

The SQL Server driver incorrectly translates the above SELECT statement to:

   SELECT t1.col1, t1.col2, t2.col1, t2.col2
   FROM  testoj1 t1,  testoj2 t2
   WHERE  t1.col1 *=  t2.col1 ,  testoj3 t3
   WHERE t3.col1 = t1.col1

This is incorrect syntax; hence, it causes the error.

WORKAROUND

Rewrite the SELECT statement such that testoj3 appears before the outer join escape clause:

   SELECT t1.col1, t1.col2, t2.col1, t2.col2
   FROM   testoj3 t3,

{oj testoj1 t1 left outer join testoj2 t2 on t1.col1 = t2.col1}

   WHERE t3.col1 = t1.col1

STATUS