Weblog entry #88 for dkg
See all weblog entries made by dkg.
0 dkg@pip:~$ cat test.sql drop table if exists foo; create table foo (x int, y int); insert into foo VALUES (1,3); insert into foo VALUES (1,5); select y from foo group by x; 0 dkg@pip:~$ sqlite3 < test.sql 5 0 dkg@pip:~$ mysql -N dkg < test.sql 3 0 dkg@pip:~$ psql -qtA dkg < test.sql ERROR: column "foo.y" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select y from foo group by x; ^ 0 dkg@pip:~$
- Clear error reporting and
- an insistence on explicit disambiguation
Comments on this Entry
The sqlite version built into Android seems to check this. I am not able to find option to enable the check for upstream release though.
On the other hand, there is a case that I happen to hit rather often where the select is well defined even without aggregating the variable. It is like:
create table foo (x integer primary key, y integer);
create table bar (x integer references foo (x), z integer);
select y, min(z) from foo natural join bar group by x;Here y does not appear in the group by and is not aggregated and still the result is well defined, because there is only one y for each x. I don't have Postgresql around to check whether it accepts this.
[ Parent | Reply to this comment ]
Annoying, maybe, but I gather that determining uniqueness of column values can be quite resource-intensive in complex queries. If you really want to avoid many columns in your group-by clause, a nested select or inner join would be the way to go.
[ Parent | Reply to this comment ]
[ Parent | Reply to this comment ]
[ Parent | Reply to this comment ]