IBM Support

IT30466: DB2 MAY PRODUCE INCORRECT RESULTS WHEN EXECUTING QUERIES CONTAINING JOINS WITH DIFFERING FLOATING-POINT PRECISIONS

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

  • Db2 may produce incorrect results when executing a query
    containing the following properties:
    
    - The query contains an inner join.
    - The join columns for the inner join have different data types
    but are both floating-point data types.
    - The query contains a local predicate with either operand as
    one of the join columns.
    - The other operand of the local predicate has a larger
    precision than the join column that isn't referenced in the
    local predicate
    - The precision of the join column used in the local predicate
    is equal to or smaller than the precision of the other join
    column
    
    For example:
    
    CREATE TABLE T1 (DF16 DECFLOAT(16));
    CREATE TABLE T2 (DEC8 DECIMAL(8));
    
    INSERT INTO T1 VALUES 1, 2;
    INSERT INTO T2 VALUES 1, 2;
    
    SELECT T2.DEC8 FROM T1 INNER JOIN T2 ON T1.DF16 = T2.DEC8 WHERE
    T2.DEC8 = CAST(1 AS DECIMAL(20));
    
    Expected Results:
    
    DEC16
    ----------
            1.
    
      1 record(s) selected.
    
    Actual Results:
    
    DEC16
    ----------
            1.
            2.
    
      2 record(s) selected.
    

Local fix

  • If possible, rewrite the local predicate so that both operands
    use the same (or smaller) precision as the join column that
    isn't referenced in the local predicate. For example:
    
    SELECT T2.DEC8 FROM T1 INNER JOIN T2 ON T1.DF16 = T2.DEC8 WHERE
    T2.DEC8 = CAST(1 AS DECIMAL(16));
    
    In this example, the constant operand of the local equality
    predicate has been changed to a DECIMAL(16) to match the
    precision of column DF16 (which is a DECFLOAT(16)).
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2 may produce incorrect results when executing queries     *
    * containing joins with differing floating-point precisions    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to version v11.1m4fp5                                *
    ****************************************************************
    

Problem conclusion

  • This issue will be fixed in db2 V11.1m4fp5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT30466

  • 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

    2019-10-01

  • Closed date

    2020-03-01

  • Last modified date

    2020-03-01

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

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

    IT30467 IT30469

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

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

Document Information

Modified date:
01 March 2020