Kbase P130252: SQL: Query returns wrong result set if a sub query is embedded in the ON clause of a LEFT OUTER JOIN
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/27/2008 |
|
Status: Unverified
SYMPTOM(s):
SQL: Query returns wrong result set if a sub query is embedded in the ON clause of a LEFT OUTER JOIN
If a sub query is embedded in the ON clause of a LEFT OUTER JOIN, the query will return an incorrect result set.
If the sub query which is embedded in the IN predicate is replaced with a constant set, the correct result set is returned.
If the sub query which is embedded in the IN predicate is replaced with a WHERE clause, the correct result set is returned.
The whole AND expression containing the IN predicate is being ignored because of the sub query embedded in the IN Predicate.
Query is similar to the following:
SELECT COUNT(*)
FROM (PUB.ORDERHEADER_1 ORDERHEADER_1
LEFT OUTER JOIN PUB.ORDERDETAIL_1 ORDERDETAIL_1
ON ORDERHEADER_1.ORDERCODE = ORDERDETAIL_1.ORDERCODE
AND ORDERDETAIL_1.ORDERDETAILCODE IN (SELECT DISTINCT RETURNEDITEM_1.ORDERDETAILCODE AS C
FROM PUB.RETURNEDITEM_1 RETURNEDITEM_1))
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.1B
OpenEdge 10.1C
CAUSE:
Bug# OE00166662
FIX:
Upgrade to OpenEdge 10.1C02 or later