Re: Oracle purchases Sleepycat - is this the "other shoe"

Tom Lane wrote:
> Chris <dmagick@gmail.com> writes:
>
>>eg mysql doesn't force you to group by all columns being selected - I
>>can do:
>>select field1, field2, field3 from table group by field1;
>>and have it valid in mysql (but of course postgres will tell you it's
>>not valid and need to add grouping for field2 and field3).
>
>
> Actually, that *is* legal per SQL99 under certain specified conditions
> (eg if field1 is a primary key for table).  We haven't gotten around to
> implementing SQL99's relaxed rules for grouping --- we're still
> basically doing what SQL92 says.  Now the full SQL99 spec for this is
> pretty hairy, but I'd bet lunch that mysql supports only the easier
> cases such as group-by-primary-key.  We might be able to cover the same
> cases they do without too much sweat ... does anyone want to dig in and
> determine exactly which cases they cover?

Quick test:

create table a(a int primary key, b int, c varchar(200));
insert into a(a, b, c) values (1,1,'one');
insert into a(a, b, c) values (2,2,'two');
insert into a(a, b, c) values (3,1,'one');
insert into a(a, b, c) values (4,2,'two');

mysql> select a,b,c from a group by a;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 | one  |
| 2 |    2 | two  |
| 3 |    1 | one  |
| 4 |    2 | two  |
+---+------+------+
4 rows in set (0.00 sec)

mysql> select a,b,c from a group by b;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 | one  |
| 2 |    2 | two  |
+---+------+------+
2 rows in set (0.00 sec)

mysql> select a,b,c from a group by c;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 | one  |
| 2 |    2 | two  |
+---+------+------+
2 rows in set (0.00 sec)

As soon as I add an aggregate function like count into the mix it does
the right thing and tells me I need to add a group by:

mysql> select b, count(*) from a;
ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no
GROUP columns is illegal if there is no GROUP BY clause

but doesn't care when I use multiple columns:

mysql> select a, b, c, count(*) from a group by b;
+---+------+------+----------+
| a | b    | c    | count(*) |
+---+------+------+----------+
| 1 |    1 | one  |        2 |
| 2 |    2 | two  |        2 |
+---+------+------+----------+
2 rows in set (0.00 sec)


So it looks like they only check whether one 'group by' is applicable
for a query and that's it.