Re: GROUP BY

On May 19, 12:23 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> Vadim Tropashko wrote:
> > SELECT -1
> > FROM r
> > GROUP BY -1
>
> > should be defined to return one row even if r is empty (unfortunately
> > it doesn't).
>
> I disagree. Grouped aggregates are simply different from ungrouped
> aggregates. Grouping by zero columns should be empty if r is empty.

OK, this requires a little bit more thought.

Group by with aggregations is set equlity join, followed by projection. For example, given

A
x y
- -
1 a
1 b
2 a

Lets "manufacture" the following powerset relation P extended with some aggregate, say count:

P
y set count
- ----- ---

a {a,b} 2
b {a,b} 2
a {a}   1
b {b}   1

Now, set equality join A /= P is the relation

x set count
- ----- ---
1 {a,b} 2
2 {a} 1

which in SQL should be written as

select x, set(y), count(1)
from A group by x

So the counting "group by" query without the set column is the projection of A /= P.

Now lets' move on to extreme cases. Same input relation, but no aggregate function
A
x y
- -
1 a
1 b
2 a

P
y set
- -----

a {a,b}
b {a,b}
a {a}
b {b}

set equality join A /= P results into

x set
- -----
1 {a,b}
2 {a}

We still have to project away sets in order to get conventional result.

Grouping by the empty set of columns. The input

A
y
-
a
b

[set equality] joined with

P
y set
- -----

a {a,b}
b {a,b}
a {a}
b {b}

The empty relation A would naturally produce the output

set

---
{}

Hence, my suggestion to define group by empty set to always produce
one row.

It doesn't follow, hoever, that projection of the empty relation
should be nonempty. (If this were the case it would destroy a large
piece of relational lattice theory including the "fundamental"
decomposition identity
A = (A /\ 00) \/ (A /\ 11)