DB2 FOR LUW / DB2FORLUW / B10 - DB2

When a query uses the outer join operator, the join order may
occasionally change between different query compilations. While
it does not affect the query results, it may occasionally result
in a change in query plan and performance. For example, consider
the following query

create table t1 (c1 int);
create table t2 (c1 int);
create table t3 (c1 int);

select * from T1, T2, T3
where T1.c1 = T2.c1(+)
and T1.c1 = T3.c1(+);

In the explain output for this query, the "Optimized Statement"
would normally look like this:

Optimized Statement:
-------------------
SELECT
Q3.$C1 AS "C1",
Q3.$C0 AS "C1",
Q4.C1 AS "C1"
FROM
(SELECT
Q2.C1,
Q1.C1
FROM
USER.T1 AS Q1
LEFT OUTER JOIN USER.T2 AS Q2
ON (Q1.C1 = Q2.C1)
) AS Q3
LEFT OUTER JOIN USER.T3 AS Q4
ON (Q3.$C1 = Q4.C1)

Occasionally, it may look like this:

Optimized Statement:
-------------------
SELECT
Q3.$C1 AS "C1",
Q4.C1 AS "C1",
Q3.$C0 AS "C1"
FROM
(SELECT
Q2.C1,
Q1.C1
FROM
USER.T1 AS Q1
LEFT OUTER JOIN USER.T3 AS Q2
ON (Q1.C1 = Q2.C1)
) AS Q3
LEFT OUTER JOIN USER.T2 AS Q4
ON (Q3.$C1 = Q4.C1)

Notice the join order has changed from T1, T2, T3 to T1, T3, T2

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 version 11.5.8.0                              *
****************************************************************

Change query to use standard outer join syntax, for the example
query above:

select * from T1 left outer join T2 on T1.C1=T2.C1
left outer join T3 on T1.C1=T3.C1;

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 version 11.5.8.0                              *
****************************************************************

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

13.07.2022
14.07.2022
08.09.2022