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

DB2 Version 10.1 Fix Pack 6 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 10.1 Fix Pack 6                               *
  ****************************************************************
  

Problem conclusion

• First fixed in DB2 10.1 Fix Pack 6
  

Temporary fix

Comments

APAR Information

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

  IT17941

• 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