IZ28879: INCORRECT USE OF WINDOW-AGGREGATION-GROUP-CLAUSE... NAMELY "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" IS ALLOWED.

APAR status

• Closed as fixed if next.

Error description

• In V9.1 the window-aggregation-group-clause ... namely "ROWS
  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" as shown in the
  example below is incorrectly allowed with ranking and number
  OLAP functions namely RANK, DENSE_RANK or ROWNUMBER. This
  specification is relevant to column functions like SUM, COUNT
  for example
  
  .....AND SUBSTR(ACME.WIDGET,1,20) IN ('ALPHA', 'BETA',
  'CHARLIE')) SELECT ACME.CTRL_ID, ACME.KEY_ID, ACME.PRODUCT_ID,
  DENSE_RANK() OVER (PARTITION BY CTRL_ID ORDER BY CTRL_ID,
  ACME.ROWRANK ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  ORDER_BY_CTRL_JOB,.....
  
  This is incorrect syntax however it does not produce a syntax
  error and is ignored by DB2 Version 9.1
  
  The following error should be returned when this syntax is used.
  
  SQL0104N  An unexpected token "ROWS" was found following "<OLAP
  window
  specification>".  Expected tokens may include:  ")".
  SQLSTATE=42601
  
  
  The correct syntax is discussed in the 'OLAP functions' section
  of the DB2 V9 Information center.
  OLAP specifications
  http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.i
  bm.db2.luw.sql.ref.doc/doc/r0023461.html
  
  The below example shows the corrected syntax with the "rows
  between unbounded preceding and current row" clause removed.
  
  .....AND SUBSTR(ACME.WIDGET,1,20) IN ('ALPHA', 'BETA',
  'CHARLIE')) SELECT ACME.CTRL_ID, ACME.KEY_ID, ACME.PRODUCT_ID,
  DENSE_RANK() OVER (PARTITION BY CTRL_ID ORDER BY CTRL_ID,
  ACME.ROWRANK) ORDER_BY_CTRL_JOB,......
  
  
  NOTE:
  This behavior is fixed in the next release (DB2 9.5) and the
  'ROWS' syntax will result in the SQL0104N error.  Any syntax
  that contains the rows clause incorrectly should be corrected
  prior to running the query on DB2 9.5 GA or higher.
  The results of the query with the 'ROWS' syntax removed yields
  the same as with the 'ROWS' syntax in-place.
  

Local fix

• Remove the 'ROWS' syntax.
  E.G.
  Remove "rows between unbounded preceding and current row"
  
  In 9.5 GA and higher the 'ROWS' syntax will result in an
  SQL0104N error.
  

Problem summary

• In V9.1 the window-aggregation-group-clause ... namely "ROWS
  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" as shown in the
  example below is incorrectly allowed with ranking and number
  OLAP functions namely RANK, DENSE_RANK or ROWNUMBER. This
  specification is relevant to column functions like SUM, COUNT
  for example
  
  .....AND SUBSTR(ACME.WIDGET,1,20) IN ('ALPHA', 'BETA',
  'CHARLIE')) SELECT ACME.CTRL_ID, ACME.KEY_ID, ACME.PRODUCT_ID,
  DENSE_RANK() OVER (PARTITION BY CTRL_ID ORDER BY CTRL_ID,
  ACME.ROWRANK ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  ORDER_BY_CTRL_JOB,.....
  
  This is incorrect syntax however it does not produce a syntax
  error and is ignored by DB2 Version 9.1
  
  The following error should be returned when this syntax is use
  
  SQL0104N  An unexpected token "ROWS" was found following "<OLA
  window
  specification>".  Expected tokens may include:  ")".
  SQLSTATE=42601
  
  
  The correct syntax is discussed in the 'OLAP functions' sectio
  of the DB2 V9 Information center.
  OLAP specifications
  http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com
  bm.db2.luw.sql.ref.doc/doc/r0023461.html
  
  The below example shows the corrected syntax with the "rows
  between unbounded preceding and current row" clause removed.
  
  .....AND SUBSTR(ACME.WIDGET,1,20) IN ('ALPHA', 'BETA',
  'CHARLIE')) SELECT ACME.CTRL_ID, ACME.KEY_ID, ACME.PRODUCT_ID,
  DENSE_RANK() OVER (PARTITION BY CTRL_ID ORDER BY CTRL_ID,
  ACME.ROWRANK) ORDER_BY_CTRL_JOB,......
  
  NOTE:
  This behavior is fixed in the next release (DB2 9.5) and the
  'ROWS' syntax will result in the SQL0104N error.  Any syntax
  that contains the rows clause incorrectly should be corrected
  prior to running the query on DB2 9.5 GA or higher.
  The results of the query with the 'ROWS' syntax removed yields
  the same as with the 'ROWS' syntax in-place.
  

Problem conclusion

Temporary fix

• Remove the 'ROWS' syntax.
  E.G.
  Remove "rows between unbounded preceding and current row"
  
  In 9.5 GA and higher the 'ROWS' syntax will result in an
  SQL0104N error.
  

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

Applicable component levels

• R910 PSY

     UP