IT17851: AN SQL STATEMENT IN A PARTITIONED DATABASE ENV CONTAINING THE ROW_NUMBER() OVER() OPERATION MIGHT PRODUCE INCONSISTENT RESULTS

DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• An sql statement in a partitioned database environment
  containing the ROW_NUMBER() OVER() operation
  might produce inconsistent results because the ROW_NUMBER()
  operation is processed in parallel on the individual partitions
  on the broadcasted result set.
  
  A example statement :
  
  select T1.BIGINT_ID, count(*) FROM
  ( SELECT
      VARCHAR_KEY,
      ROW_NUMBER() OVER() AS BIGINT_ID
    FROM
      (
        SELECT DISTINCT
          VARCHAR_KEY
        FROM
          MYTABLE
      ) ) T1, MYTABLE T2
  where T1.VARCHAR_KEY = T2.VARCHAR_KEY
  group by T1.BIGINT_ID having count(*) > 1
  
  This will have an execution plan showing  :
  
  
                    11141
                   BTQ
                   (  11)
                     |
                   1856.83
                   MDTQ
                   (  12)
  
  Where the resultset is pushed out to all partitions via the BTQ
  operation and where the row_number() operator is applied.
  A simplified example data set :
  
  TQ11  receives all matching rows and it will be assigning the
  rank to all rows as it receives them.
  say TQ11 on partition 0 receives row a b c d,  it will assign
  these row_number() / rank value :
  
  a rownum 1
  b rownum 2
  c rownum 3
  d rownum 4
  
  but partition 1 might receive the rows in a different order : c
  b a d
  so on partition 1 the assigned rownumbers are different
  
  a rownum 3
  b rownum 2
  c rownum 1
  d rownum 4
  
  hence when the data is then rejoined with the base table, the
  result might be
  a 1
  b 2
  c 1
  d 4
  
  with incorrect duplicate values for the rownumber() column.
  

Local fix

• If there is a unique column in the stream that row_number() over
  () runs on, then add the unique column into row_number:
  row_number() over (order by <unique-columns>)
  
  Example:
  SELECT VARCHAR_KEY,
         ROW_NUMBER() OVER (ORDER BY VARCHAR_KEY) + 1000000000 AS
  BIGINT_ID
  

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:

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

  IT18100 IT18101 IT18102

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels