DB2 FOR LUW / DB2FORLUW / A50 - DB2

Db2 may produce incorrect results when executing a query
containing the following properties:
 - Multiple table are involved
 - On the top of the query there is inner join between view and
regular table
 - The local join predicates for the top inner join are in form
   A = B and B = constant, A and B columns have different data
types CHAR(6) versus VARCHAR
 - Inside the view there is a cascade of Left Outer Joins with
UNION ALL in the middle on NULL providing side
 - The outer part of the view has inner join between regular
table and mentioned cascade and finally result is unioned all
with another regular table


For example:

create table T1 (c1 varchar(254) not null, c2 char(1 octets));
create table T2 (c1 char(6 octets) not null);
create table T3 (c1 char(6 octets) not null);
create table T4 (c1 char(3 octets) not null);
create table T5 (c1 char(3 octets) not null);
create table T6 (c1 char(6 octets) not null);

insert into T1 values ('201905','1'),('201907','0');
insert into T2 values ('201905'),('201907');
insert into T3 values ('201905');
insert into T4 values ('201');
insert into T5 values ('201');
insert into T6 values ('201908');


CREATE VIEW V1 AS
SELECT
     Q3.C1 AS C1
   FROM
     (SELECT
        T1.C1 AS C1,
        T1.C2 AS C2
      FROM
        T1
     ) AS Q2,
     (SELECT
        T3.C1 AS C1,
        Q1.C2 AS C2
      FROM
        T3
        LEFT OUTER JOIN
        (SELECT
           T4.C1 AS C1,
           '1' AS C2
         FROM
           T4
         UNION ALL
         SELECT
           T5.C1 AS C1,
           '0' AS C2
         FROM
           T5
        ) AS Q1
        ON T3.C1 = Q1.C1
        LEFT OUTER JOIN T6
        ON T3.C1=T6.C1
     ) AS Q3
   WHERE
     Q2.C2=Q3.C2
   UNION ALL
   SELECT
     T1.C1 AS C1
   FROM
     T1;

Problematic query:

SELECT V1.C1 From V1 INNER JOIN T2 ON T2.C1 = V1.C1 where
V1.C1='201905'

Note that, the problem can occur without using view, view is
used as above just to improve readability of problem
description.

****************************************************************
* USERS AFFECTED:                                              *
* See Error Description                                        *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 v11.1 or v11.5 with latest fixpack.           *
****************************************************************

If possible, exchange places of operands in local equality join
predicate between the regular table and the view:
e.g. change  T2.C1 = V1.C1 to V1.C1 = T2.C1
   SELECT V1.C1 From V1 INNER JOIN T2 ON V1.C1 = T2.C1 where
V1.C1='201905'

or cast the column returned from the view to match the type of
column from the regular table:
   SELECT V1.C1 From V1 INNER JOIN T2 ON T2.C1 = CAST( V1.C1 AS
CHAR(6)) where V1.C1='201905'

****************************************************************
* USERS AFFECTED:                                              *
* See Error Description                                        *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 v11.1 or v11.5 with latest fixpack.           *
****************************************************************

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

22.01.2020
22.01.2020
22.01.2020