IBM Support

IT34774: For special queries DISTINCT is pulled up above the UNION level in the optimized statement, what may cause wrong results

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • Incorrect results may be observed for queries with following
    characteristic:
    - query with UNION
    - UNION is joined to SELECT DISTINCT with predicate
    - one leg of union is a join, rows in joined tables are not
    unique
    
    In optimized statement DISTINCT is pulled up above the UNION
    level, what may cause wrong results.
    
    Sample query:
    
    select *
    from
    (SELECT inf.T1C10
            FROM T1 inf
      UNION ALL
      ( SELECT A.T2C1
          FROM T2 a
        INNER JOIN T3 R
          ON A.T2C1 = r.T3C1
        WHERE ( a.T2C4 IS NOT NULL )
      )
    ) MAIN
    INNER JOIN (SELECT DISTINCT T4C2,T4C3 FROM t4 WHERE T4C1 IN(
    'A1', 'A2')) acc
    ON    (1=1)
    ;
    
    Optimized Statement:
    -------------------
    SELECT
      DISTINCT Q10.$C0 AS "T1C10",
      Q3.T4C2 AS "T4C2",
      Q3.T4C3 AS "T4C3"
    FROM
      REPRO.T4 AS Q3,
      (SELECT
         Q9.$C0
       FROM
         (SELECT
            Q5.T2C1
          FROM
            REPRO.T3 AS Q4,
            REPRO.T2 AS Q5
          WHERE
            (Q5.T2C1 = Q4.T3C1) AND
            Q5.T2C4 IS NOT NULL
          UNION ALL
          SELECT
            Q7.T1C10
          FROM
            REPRO.T1 AS Q7
         ) AS Q9
      ) AS Q10
    WHERE
      Q3.T4C1 IN ('A1', 'A2')
    
    As a workaround DISTINCT can be rewritten to GROUP BY.
    select *
    from
    (SELECT inf.T1C10
            FROM T1 inf
      UNION ALL
      ( SELECT A.T2C1
          FROM T2 a
        INNER JOIN T3 R
          ON A.T2C1 = r.T3C1
        WHERE ( a.T2C4 IS NOT NULL )
      )
    ) MAIN
    INNER JOIN (SELECT T4C2,T4C3 FROM t4 WHERE T4C1 IN( 'A1', 'A2')
    GROUP BY T4C2,T4C3 ) acc
    ON    (1=1)
    ;
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 11.1 Fix Pack m4fp6 or later          *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 Version 11.1 Fix Pack m4fp6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT34774

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2020-11-04

  • Closed date

    2021-03-15

  • Last modified date

    2021-03-29

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

  • 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

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1"}]

Document Information

Modified date:
31 March 2021