Kbase P125278: SQL query with LEFT JOIN returns different results in OpenEdge 10
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
20/01/2010 |
|
Status: Unverified
SYMPTOM(s):
SQL query with LEFT JOIN returns unexpected results
Query returned 1 record in Progress 9
Query returned many records in OpenEdge 10
Progress 9 returns the desired result
LEFT JOIN syntax contains filtering information, for example:
pub.table1 t1
LEFT JOIN pub.table2 ts on t1.field1 = 1
AND t1.field2 = 2
AND t1.field3 = 3
AND t2.field1 = t1.field4
FACT(s) (Environment):
OpenEdge 10.x
All Supported Operating Systems
CHANGE:
Upgraded to OpenEdge 10
CAUSE:
The behaviour seen in OpenEdge 10 is correct and is compliant with the SQL standards that cover how an outer join should be executed. Whilst Progress 9 returned the desired result, the result set was actually incorrect. The Progress SQL-92 engine was not processing the outer join in a manner that was compliant with SQL standards.
To explain further, a query asks for an outer join, as per the following example:
pub.table1 t1
LEFT JOIN pub.table2 t2 on t1.field1 = 1
AND t1.field2 = 2
AND t1.field3 = 3
AND t2.field1 = t1.field4
According to the SQL standard, this is supposed to mean that SQL should join the data from table "table1" to the data from table "table2". If there is no matching row in "table2" (because any one or more of the ON clause predicates say False) then the row in "table1" is still selected, but it is "padded out" with null data from table "table2". This means that all the rows from "table1" are always selected, no matter what the ON predicates say. For example, the ON predicates say only rows with "field2" = 2 are wanted, but the standards requires that all the other rows also get selected. That is the rule for a left outer join.
Progress 9.x SQL used the more intuitive, but incorrect, method of rejecting the data rows from "table1" for which the ON predicates said False. This behaviour was wrong, and is now fixed in OpenEdge 10.
FIX:
Modify the syntax of the LEFT JOIN clause, moving certain predicates from the ON clause to the WHERE clause in the query. For example:
pub.table1 t1
LEFT OUTER JOIN PUB.table2 t2 ON ((t1.field1=t2.field1)
AND (t1.field2=t2.field2))
AND (t1.field3=t2.field3))
WHERE
t1.field1=1 AND
t1.field2=2 AND
t1.field3=3
The SQL standards state that the ON clause predicates are used to determine which rows match for left outer join, with the rule that all the rows in the left table are always chosen. In addition, the WHERE clause predicates are used to determine which rows are returns in the query's result set. Therefore WHERE clause predicates can reject data rows from the left table (such as "table1") in an outer join. This means that when a predicate in an ON clause refers only to columns from one of the tables (e.g., "t1.field1 = 0"), it is worth considering whether this predicate should be in the WHERE clause.