[reporter="jhyde", created="Mon, 16 Jul 2007 18:21:10 -0500 (GMT-05:00)"]
Empty grouping set applied to empty table gives 1 row, should give 0 rows. For example,
CREATE TABLE t (INTEGER i NOT NULL);
SELECT COUNT() AS c, SUM(i) AS s FROM t GROUP BY ();
returns 1 row
c s
== ====
0 NULL
but should return 0 rows. The similar query
SELECT COUNT() AS c, SUM(i) AS s FROM t;
correctly returns the 1 row above.
See SQL:2003 spec [SQL:2003, part 2, page 325. Section 7.9 , General rules, paragraph 2]:
Case:
a) If there are no grouping columns, then the result of the is the grouped table consisting of T as its only group.
b) Otherwise, the result of the is a partitioning of the rows of T into the minimum number of groups such that, for each grouping column of each group, no two values of that grouping column are distinct.
I am surprised by this behavior - I had always assumed that the above two queries were equivalent - but Oracle 10.2 is consistent with my reading of the spec.
[reporter="jhyde", created="Mon, 16 Jul 2007 18:21:10 -0500 (GMT-05:00)"] Empty grouping set applied to empty table gives 1 row, should give 0 rows. For example,
, General rules, paragraph 2]:
is the grouped table consisting of T as its only group. is a partitioning of the rows of T into the minimum number of groups such that, for each grouping column of each group, no two values of that grouping column are distinct.
CREATE TABLE t (INTEGER i NOT NULL);
SELECT COUNT() AS c, SUM(i) AS s FROM t GROUP BY ();
returns 1 row
c s
== ====
0 NULL
but should return 0 rows. The similar query
SELECT COUNT() AS c, SUM(i) AS s FROM t;
correctly returns the 1 row above.
See SQL:2003 spec [SQL:2003, part 2, page 325. Section 7.9
Case:
a) If there are no grouping columns, then the result of the
b) Otherwise, the result of the
I am surprised by this behavior - I had always assumed that the above two queries were equivalent - but Oracle 10.2 is consistent with my reading of the spec.