LI72508: AN EXPRESSION IN THE ORDER BY CLAUSE STARTING WITH ROLLUP IN THE GROUP BY CLAUSE IS NOT VALID

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• A query includes Rollup/Cube in order by query in group by
  clause is not valid
  
  The expected behaviour for the query is a systax error:
  SQL0214N  An expression in the ORDER BY clause in the following
  position, or
  starting with "ROLLUP ..." in the "GROUP BY" clause is not
  valid.  Reason code
  = "4".  SQLSTATE=42822
  
  SQL0214N An expression in the ORDER BY clause in the following
            position, or starting with
            "<expression-start-or-order-by-position>" in the
            "<clause-type>" clause is not valid.  Reason code =
            "<reason-code>".
  
  Explanation:
  
   4 Expression in a GROUP BY clause cannot include a
  scalar-fullselect.  This reason code occurs only when
  "<clause-type>" is GROUP BY.
  
  The semantically equivalent query receives the expected error in
  V8, V9 and V10:
  db2 => select (select 1 from t2 where c11 = c21)
  from t1
  group by rollup ((select 1 from t2));
  

Local fix

• Need to rewrite the query with removing the rollup/cube function
  in place of group by clause.
  

Problem summary

• A query includes Rollup/Cube in order by query in group by
  clause is not valid
  
  The expected behaviour for the query is a systax error:
  SQL0214N  An expression in the ORDER BY clause in the following
  position, or
  starting with "ROLLUP ..." in the "GROUP BY" clause is not
  valid.  Reason code
  = "4".  SQLSTATE=42822
  
  SQL0214N An expression in the ORDER BY clause in the following
            position, or starting with
            "<expression-start-or-order-by-position>" in the
            "<clause-type>" clause is not valid.  Reason code =
            "<reason-code>".
  
  Explanation:
  
   4 Expression in a GROUP BY clause cannot include a
  scalar-fullselect.  This reason code occurs only when
  "<clause-type>" is GROUP BY.
  
  The semantically equivalent query receives the expected error in
  V8, V9 and V10:
  db2 => select (select 1 from t2 where c11 = c21)
  from t1
  group by rollup ((select 1 from t2));
  
  LOCAL FIX:
  Need to rewrite the query with removing the rollup/cube function
  in place of group by clause.
  

Problem conclusion

• The problem will be fixed in db2 v9 fp5
  

Temporary fix

• Need to rewrite the query with removing the rollup/cube function
  in place of group by clause.
  

Comments

APAR Information

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

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

  LI72509 LI72669

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R910 PSY

     UP