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
------------------------------
Original Message:
Sent: Fri January 10, 2020 01:58 AM
From: SangGyu Jeong
Subject: Is this a bug? (GROUP BY CUBE)
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
------------------------------
#Db2