IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    LI72508

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-08-02

  • Closed date

    2008-07-14

  • Last modified date

    2008-07-14

  • 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

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 October 2021