Tom Lane wrote:
Chris <dmagick@xxxxxxxxx> 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.
