Kbase 20114: Different Result Set Running Query with Outer-Join (-ojmode)
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
04/05/2007 |
|
Status: Unverified
FACT(s) (Environment):
Progress 8.3C
Progress 9.0B
Progress 9.1A
Progress 9.1B
SYMPTOM(s):
OUTER-JOIN query displays different results on latest patch of 9.0B and 9.1A
The following code is an example of the results.
OPEN QUERY q1 PRESELECT EACH customer,
FIRST order OUTER-JOIN OF customer WHERE order.order-num < 50,
FIRST order-line OF order WHERE order-line.item-num < 15.
CHANGE:
Progress 8.3C and earlier:
During a mixture of INNER versus OUTER Joins, Progress treated the mixture in such a way, where the INNER-JOIN behavior predominated.
Progress 8.3C and after (9.0B & 9.1A):
During a mixture of INNER versus OUTER Joins, Progress treated the mixture in such a way, where the OUTER-JOIN behavior predominated, thus more records are included in the combination of outer/inner join than before.
Progress 9.1B:
During a mixture of INNER versus OUTER Joins, Progress backed-out the above behavior, so that it behaves like Pre-8.3C, thus the INNER-JOIN predominated.
CAUSE:
Progress implemented a new startup option to preserve the 8.3C behavior. The startup option was available on the latest patch on 9.0B and 9.1A:
-ojmode n
where n can either be 1 or 2, thus default = 1.
If n is 1, Progress 7.x - 8.2C behavior was present, where INNER-JOINS dominated.
If n is 2, Progress 8.3C behavior was present, where OUTER-JOIN dominated.
FIX:
The latest patches on 9.0B and 9.1A exhibited the same behavior as Progress 9.1B.
Use the startup option (-ojmode 2), this sets the original 8.3C, 9.0B and 9.1A behavior.
The minimum patch level needed to use this new startup parameter is as follows:
Version 9.0B
- 9.0B26 for Compaq Tru64 UNIX
- 9.0B33 for HP/UX 11
- 9.0B38 for IBM AIX
- 9.0B35 for SUN SOLARIS
- 9.0B19 for SUN SOLARIS INTEL
- 9.0B22 for UNIX V.4
- 9.0B38 for WIN NT 32 Intel
Version 9.1A
- 9.1A15 for IBM AIX
- 9.1A16 for WIN NT 32 Intel