JR48511: PROCESS OR TASK INSTANCE QUERIES USING QUERY FILTER WITH OR EXPRESSION MAY RETURN DUPLICATE RESULT ROWS

Version 8.5 Refresh Pack 5 for the IBM Business Process Manager products
Version 8.5.0 Fix Pack 2 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• Query filter using OR and where both sides select the same
  object the evaluated result may contain duplicate rows.
  sample query which shows the issue
  http://<host>:<port>/rest/bpm/wle/v1/processes/query/IBM.DEFAULT
  ALLPROCESSLIST_75?selectedAttributes=PI_PIID,PI_NAME,PI_STATE
  &queryFilter=(PI_PIID IN (ID('<actual PI_PIID>')) OR
  PI_STATE=STATE_RUNNING)
  (where <actual PI_PIID> is in state running)
  Currently query filter using OR are mapped to a SQL 'union all'.
  It will be changed to SQL 'union' which ensure that the result
  does not include duplicate rows.
  The issue has been observed for DB2 and Oracle databases  MSSQL
  does not return duplicate result rows.
  

Local fix

• n/a
  

Problem summary

• When using the search functionallity in BPM using query filters
  with OR expression this can lead to duplicate rows in the
  result or even invalid SQL.
  
  PROBLEM DETAILED DESCRIPTION:
  When using OR expressions in query filters, the generated SQL is
  using UNION ALL, which allows duplicate rows.
  

Problem conclusion

• With this interim fix the generated SQL will be changed so that
  UNION instead of UNION ALL will be used so that the result will
  not return duplicate rows.
  
  FIX AVAILABILITY:
  iFix for 8.5.0.1 is/will be available on Fix Central; search for
  APAR JR48511 at http://www.ibm.com/support/fixcentral/
  
  When obtaining any of the above fixes, be sure to download the
  accompanying readme, for itself, and any prerequisite fixes, and
  review them thorougly.
  

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:

Fix information

• Fixed component name

  BPM STANDARD

• Fixed component ID

  5725C9500

Applicable component levels

• R850 PSY

     UP