|
Problem IT16656
|
Status: Closed |
SQL0801 AND WRONG RESULTS FROM STDDEV_SAMP, VARIANCE_SAMP, COVARIANCE_SAMP WHEN USED IN AN OLAP SPECIFICATION |
| product: |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 |
| Problem 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. |
| 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. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 11.1 Mod1 Fix Pack1 iFix001 for Linux, UNIX, and Windows
DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
|
| Solution |
First fixed in v11.1 Fixpack 1 |
| Workaround |
See LOCAL FIX. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 17.08.2016
18.05.2017
18.05.2017 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 11.1.1.1
|
 |
