IBM Support

IC73581: NESTED-LOOP JOIN WITH EARLYOUT FOR GROUPBY CLAUSES, YIELDS INCORRECT RESULTS WHEN JOIN COLUMNS ARE OF DIFFERENT DATA TYPES

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When a query that meets all the following conditions is
    executed, fewer results may be returned:
    - Several tables are joined using columns with different data
    types
    - Nested-loop join is selected as the join method
    - The output from the nested-loop join method is included into
    the Group by clause and the Early Out flag is marked as GROUPBY
    
    The following scenarios indicate the problem:
    
    1. Joining two tables with different data types. To demonstrate
    this problem, create two tables for the test.  Create the tables
    such that the columns being joined (t1.i1 and t2.i1) have
    different data types.
    create table t1 (i1 bigint not null, c2 char(250))
    create index i1 on t1 (i1)
    create table t2 (i1 smallint not null, i2 bigint)
    create index i2 on t2 (i1,i2)
    2. Run the query with different data types (bigint and smallint)
    in predicate and the result goes directly into the Group by
    clause.
    select t2.i1, max(t2.i2) from t1, t2 where t1.i1=t2.i1 group by
    t2.i1
    3. When the Nested-loop join is selected as the join method.
    
    Access Plan:
    -----------
            Total Cost:             22.8806
            Query Degree:           1
                  Rows
                 RETURN
                 (   1)
                  Cost
                   I/O
                   |
                   99
                 GRPBY
                 (   2)
                 22.8709
                    3
                   |
                   99
                 ^NLJOIN
                 (   3)
                 22.8655
                    3
             /-----+------\
           99             12.1111
         IXSCAN           IXSCAN
         (   4)           (   5)
        0.0455361         7.57676
            0                1
           |                |
           99              1199
     INDEX: DB2INST1   INDEX: DB2INST1
           I1               I2
           Q2               Q1
            3) NLJOIN: (Nested Loop Join)
                    Arguments:
                    ---------
                    EARLYOUT: (Early Out flag)
                            GROUPBY
                    Predicates:
                    ----------
                    3) Predicate used in Join
                            Predicate Text:
                            --------------
                            (Q2."I1" = Q1."I1")
    

Local fix

  • Execute the following and then recycle the instance:
    db2set DB2_REDUCED_OPTIMIZATION=NO_NLJN_EO_FOR_GB
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users.                                                   *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * When a query that meets all the following conditions is      *
    * executed, fewer results may be returned:                     *
    * - Several tables are joined using columns with different     *
    * data types                                                   *
    * - Nested-loop join is selected as the join method            *
    * - The output from the nested-loop join method is included    *
    * into the Group by clause and the Early Out flag is marked as *
    * GROUPBY                                                      *
    *                                                              *
    * The following scenarios indicate the problem:                *
    *                                                              *
    * 1. Joining two tables with different data types. To          *
    * demonstrate this problem, create two tables for the test.    *
    * Create the tables such that the columns being joined (t1.i1  *
    * and t2.i1) have different data types.                        *
    * create table t1 (i1 bigint not null, c2 char(250))           *
    * create index i1 on t1 (i1)                                   *
    * create table t2 (i1 smallint not null, i2 bigint)            *
    * create index i2 on t2 (i1,i2)                                *
    * 2. Run the query with different data types (bigint and       *
    * smallint) in predicate and the result goes directly into the *
    * Group by clause.                                             *
    * select t2.i1, max(t2.i2) from t1, t2 where t1.i1=t2.i1 group *
    * by t2.i1                                                     *
    * 3. When the Nested-loop join is selected as the join method. *
    *                                                              *
    * Access Plan:                                                 *
    * -----------                                                  *
    * Total Cost:             22.8806                              *
    * Query Degree:           1                                    *
    * Rows                                                         *
    * RETURN                                                       *
    * (   1)                                                       *
    * Cost                                                         *
    * I/O                                                          *
    * |                                                            *
    * 99                                                           *
    * GRPBY                                                        *
    * (   2)                                                       *
    * 22.8709                                                      *
    * 3                                                            *
    * |                                                            *
    * 99                                                           *
    * ^NLJOIN                                                      *
    * (   3)                                                       *
    * 22.8655                                                      *
    * 3                                                            *
    * /-----+------\                                               *
    * 99             12.1111                                       *
    * IXSCAN           IXSCAN                                      *
    * (   4)           (   5)                                      *
    * 0.0455361         7.57676                                    *
    * 0                1                                           *
    * |                |                                           *
    * 99              1199                                         *
    * INDEX: DB2INST1   INDEX: DB2INST1                            *
    * I1               I2                                          *
    * Q2               Q1                                          *
    * 3) NLJOIN: (Nested Loop Join)                                *
    * Arguments:                                                   *
    * ---------                                                    *
    * EARLYOUT: (Early Out flag)                                   *
    * GROUPBY                                                      *
    * Predicates:                                                  *
    * ----------                                                   *
    * 3) Predicate used in Join                                    *
    * Predicate Text:                                              *
    * --------------                                               *
    * (Q2."I1" = Q1."I1")                                          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 9.7.0.4.                                      *
    ****************************************************************
    

Problem conclusion

  • The problem was fixed in DB2 9.7.0.4.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC73581

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-12-29

  • Closed date

    2011-04-20

  • Last modified date

    2011-04-20

  • APAR is sysrouted FROM one or more of the following:

    IC71982

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC73581

Modified date: 20 April 2011