Db2 (On Premises and Cloud)

Expand all | Collapse all

Is this a bug? (GROUP BY CUBE)

  • 1.  Is this a bug? (GROUP BY CUBE)

    Posted Fri January 10, 2020 01:58 AM
    Hi all, I did the below SQL statement and the result is strange.
    I think the result of using the WITH statement is correct.
    Is the result of using a subquery in the FROM clause a bug?

    $ db2 "SELECT A, B FROM (SELECT '1' AS A, '2' AS B FROM DUAL) TE WHERE A=2 GROUP BY CUBE (A,B)"
    
    A B
    - -
    1 2
    
      1 record(s) selected.
    
    $ db2 "WITH TE AS ( SELECT '1' AS A, '2' AS B FROM DUAL) SELECT A, B FROM TE WHERE A=2 GROUP BY CUBE (A,B)"
    
    A B
    - -
    - -
    
      1 record(s) selected.
    
    $ db2set -all
    [i] DB2_COMPATIBILITY_VECTOR=ORA
    [i] DB2_DJ_INI=/work3/db2/V11.5.dc_inshome/sqllib/cfg/db2dj.ini
    [i] DB2COMM=TCPIP​


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------


  • 2.  RE: Is this a bug? (GROUP BY CUBE)

    Posted Mon January 20, 2020 09:02 AM
    There's definitely something weird about that.
    Running th query against a Db2 10.5 gives me the same output for both queries as expected:
    [db2inst1]$ db2 "SELECT A, B FROM (SELECT '1' AS A, '2' AS B FROM sysibm.sysdummy1) TE WHERE A=2 GROUP BY CUBE (A,B)"
    
    A B
    - -
    - -
    
      1 record(s) selected.
    
    [db2inst1]$ db2 "WITH TE AS ( SELECT '1' AS A, '2' AS B FROM sysibm.sysdummy1) SELECT A, B FROM TE WHERE A=2 GROUP BY CUBE (A,B)"
    
    A B
    - -
    - -
    
      1 record(s) selected.
    
    [db2inst1]$ db2set
    DB2COMM=TCPIP
    [db2inst1]$ db2level
    DB21085I  This instance or install (instance name, where applicable:
    "db2inst1") uses "64" bits and DB2 code release "SQL10058" with level
    identifier "0609010E".
    Informational tokens are "DB2 v10.5.0.8", "special_36360", "IP23994_36360", and
    Fix Pack "8".
    Product is installed at "/opt/db2/10.5".

    Doing the same against a BLU environment on 11.1 has the same effect as your Oracle compatible system.
    [db2inst1]$ db2 "SELECT A, B FROM (SELECT '1' AS A, '2' AS B FROM sysibm.sysdummy1) TE WHERE A=2 GROUP BY CUBE (A,B)"
    
    A B
    - -
    1 2
    
      1 record(s) selected.
    
    [db2inst1]$ db2 "WITH TE AS ( SELECT '1' AS A, '2' AS B FROM sysibm.sysdummy1) SELECT A, B FROM TE WHERE A=2 GROUP BY CUBE (A,B)"
    
    A B
    - -
    - -
    
      1 record(s) selected.
    
    [db2inst1]$ db2set
    DB2_WORKLOAD=ANALYTICS
    DB2_USE_ALTERNATE_PAGE_CLEANING=ON [DB2_WORKLOAD]
    DB2_ANTIJOIN=EXTEND [DB2_WORKLOAD]
    DB2COMM=TCPIP
    [db2inst1]$ db2level
    DB21085I  This instance or install (instance name, where applicable:
    "db2inst1") uses "64" bits and DB2 code release "SQL11014" with level
    identifier "0205010F".
    Informational tokens are "DB2 v11.1.4.4", "s1811091400", "DYN1811091400PPCLE",
    and Fix Pack "4".
    Product is installed at "/opt/ibm/db2/V11.1_FP4".​


    ------------------------------
    Sebastian Zok
    ------------------------------



  • 3.  RE: Is this a bug? (GROUP BY CUBE)

    Posted Tue January 21, 2020 04:52 AM
    Just a quick search (without looking for your specific usecase) with 'INCORRECT RESULTS' shows these APAR's :
     ( https://www.ibm.com/support/home/search-results/P849948M05373L86/DB2_for_Linux,_UNIX_and_Windows?sortby=-dcdate&filter=DC.Type_avl:CT748&prod=G0&noredir=true

    IT29981: DB2 MAY RETURN INCORRECT RESULTS WHEN EXECUTING IUD STATEMENTS CONTAINING A SUBQUERY WITH CORRELATION AND AGGREGATION
    https://www-01.ibm.com/support/docview.wss?uid=swg1IT29981

    IT30249: WRONG RESULTS ARE POSSIBLE FOR FEDERATED OR COLUMNAR QUERY WITH AGGREGATION OVER DISTINCT OUTER JOIN
    https://www-01.ibm.com/support/docview.wss?uid=swg1IT30249


    IT28638: DB2 MAY PRODUCE INCORRECT RESULTS FOR HSJN WITH PREDICATE THAT HAS CASE(COALESCE) AND NLJN OR ZZJOIN ON THE OUTER
    https://www-01.ibm.com/support/docview.wss?uid=swg1IT28638



    IT30005: DB2 MAY PRODUCE INCORRECT RESULTS FOR HSJN WITH PREDICATE THAT HAS CASE(COALESCE) AND NLJN OR ZZJOIN ON THE OUTER
    https://www-01.ibm.com/support/entdocview.wss?uid=swg1IT30005


    IT30251: WRONG RESULTS ARE POSSIBLE FOR FEDERATED OR COLUMNAR QUERY WITH AGGREGATION OVER DISTINCT OUTER JOIN
    https://www-01.ibm.com/support/entdocview.wss?uid=swg1IT30251


    IT31342: DB2 MAY RETURN INCORRECT RESULT WHEN EXECUTING QUERY WITH PREDICATE WITH CONSTANT AND LEFT OUTER JOIN AND UNION ALL
    https://www-01.ibm.com/support/entdocview.wss?uid=swg1IT31342


    IT31207: INCORRECT RESULT WHEN USING TWICE JOIN WITH THE SAME TABLE WITH GROUP BY
    https://www-01.ibm.com/support/entdocview.wss?uid=swg1IT31207

    IT28739: WRONG RESULTS MIGHT BE OBSERVED WHEN RUNNING QUERIES WITH OUTER JOINS AND GROUP BY OPERATIONS
    https://www-01.ibm.com/support/docview.wss?uid=swg1IT28739

    IT29982: DB2 MAY RETURN INCORRECT RESULTS WHEN EXECUTING IUD STATEMENTS CONTAINING A SUBQUERY WITH CORRELATION AND AGGREGATION
    https://www-01.ibm.com/support/entdocview.wss?uid=swg1IT29982


    ------------------------------
    Erwin Hattingh
    Systems Engineer / Db2 DBA
    Triodos Bank
    ------------------------------



  • 4.  RE: Is this a bug? (GROUP BY CUBE)

    Posted Tue January 21, 2020 09:00 AM
    Hi Erwin, Thank you for sharing multiple APARs.
    I found some APARs related to using the 'group by cube' clause through google.

    IC81009: ROLLUP OR CUBE QUERY MAY RETURN INCORRECT RESULT WHEN IT REFERENCES SAME GROUPING(EXPRESSION) MULTIPLE TIMES
    https://www-01.ibm.com/support/docview.wss?uid=swg1IC81009

    IC84309: ROLLUP OR CUBE QUERY MAY RETURN INCORRECT RESULT WHEN IT REFERENCES SAME GROUPING(EXPRESSION) MULTIPLE TIMES
    http://www-01.ibm.com/support/docview.wss?uid=swg1IC84309

    Interestingly, this is a defect that was fixed in versions prior to 11.1.
    It's hard to tell if it's the same as the one described in the APAR, but it's obviously a bug.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------