IBM Support

IT16656: SQL0801 AND WRONG RESULTS FROM STDDEV_SAMP, VARIANCE_SAMP, COVARIANCE_SAMP WHEN USED IN AN OLAP SPECIFICATION

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • (1) SQL0801 (divide by zero) is possible from
    sysibm.stddev_samp, sysibm.variance_samp and covariance_samp
    routines when applied in an OLAP specificat
    ion.
    
    Repro instructions:
    
    create table SUPPLY ( SNO varchar(20), QTY integer )
    DB20000I  The SQL command completed successfully.
    
    insert into SUPPLY values ( 'S1', 100 )
    DB20000I  The SQL command completed successfully.
    
    insert into SUPPLY values ( 'S1', 200 )
    DB20000I  The SQL command completed successfully.
    
    insert into SUPPLY values ( 'S2', 300 )
    DB20000I  The SQL command completed successfully.
    
    insert into SUPPLY values ( 'S2', 400 )
    DB20000I  The SQL command completed successfully.
    
    select stddev_samp(QTY) over ( order by SNO ) from supply
    
    1
    ------------------------
      +7.07106781186548E+001
    
    SQL0801N  Division by zero was attempted.  SQLSTATE=22012
    
    SQL0801N can also be obtained when using sysibm.variance_samp or
    sysibm.covariance_samp in place of sysibm.stddev_samp.
    
    (2) Wrong results are possible from sysibm.stddev_samp,
    sysibm.variance_samp and covariance_samp routines when applied
    in an OLAP specification.
    
    Repro instructions:
    
    create table SUPPLY ( SNO varchar(20), QTY integer )
    DB20000I  The SQL command completed successfully.
    
    insert into SUPPLY values ( 'S1', 100 )
    DB20000I  The SQL command completed successfully.
    
    insert into SUPPLY values ( 'S1', 200 )
    DB20000I  The SQL command completed successfully.
    
    insert into SUPPLY values ( 'S1', 200 )
    DB20000I  The SQL command completed successfully.
    
    select QTY, stddev_samp(QTY) over (order by SNO) from supply
    where SNO='S1'
    
    QTY         2
    ----------- ------------------------
            100   +5.77350269189626E+001
            200   +8.16496580927726E+001
            200
    
      3 record(s) selected.
    
    Expected is +5.77350269189626E+001 for each row.
    
    Similar wrong results can also be obtained when using
    sysibm.variance_samp or sysibm.covariance_samp in place of
    sysibm.stddev_samp.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Users using SYSIBM.STTDEV_SAMP, SYSIBM.VARIANCE_SAMP or      *
    * SYSIBM.COVARIANCE_SAMP                                       *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply v11.1 Fixpack 1 to the DB2 server.                     *
    ****************************************************************
    

Problem conclusion

  • First fixed in v11.1 Fixpack 1
    

Temporary fix

  • See LOCAL FIX.
    

Comments

APAR Information

  • APAR number

    IT16656

  • 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

    2016-08-17

  • Closed date

    2017-05-18

  • Last modified date

    2017-05-18

  • 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

[{"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","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 June 2020