IT17941: POSSIBLE WRONG RESULTS WHEN THE INPUT PARAMETERS OF AN INLINED SQL SCALAR UDF CONTAINS AN OLAP SPECIFICATION

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
Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 4 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• When the input parameters of an inlined SQL scalar UDF contains
  an OLAP specification, and under any of the following
  situations, wrong results are possible.
  
  (1) When such UDF appears in select clause, and the select
  clause also has "distinct" keyword.
  (2) When such UDF appears in order by clause
  
  You can work around the issue by modifying the query. For
  example, if the query is:
  
  select C2
  from T1
  order by F1(min(C1) over (partition by C2))
  
  You can modify the query to:
  
  select C2
  from
  (
  select C2, F1(min(C1) over (partition by C2)) as CC
  from T1
  )
  order by CC
  
  If the query is
  
  select distinct F1(min(C1) over (partition by c2)) *
  F1(count(C1) over (partition by c2))
  from T1
  
  You can modify the query to:
  
  select distinct F1(CC1) * F1(CC2)
  from
  (
  select min(C1) over (partition by c2) as CC1, count(C1) over
  (partition by c2) as CC2
  from T1
  )
  

Local fix

• Modify the query; see "Error Description"
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 Version 11.1.1.1                              *
  ****************************************************************
  

Problem conclusion

• First fixed in DB2 Version 11.1.1.1
  

Temporary fix

Comments

APAR Information

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

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

  IT17949 IT17950 IT17951

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• R950 PSN

     UP

• R970 PSN

     UP

• RA10 PSN

     UP

• RA50 PSN

     UP