dynamobi/dynamobi — Issue #588: [FRG-284] Empty grouping set applied to empty table gives 1 row, should give 0 rows
Author: dynamobi-build
Created: 2011-12-30 03:37:13+00:00
State: open
Labels: FRG, Sql2Rel

[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.