(somewhat OT) Seeking a SQL counter-example

[dragonchild]

I've got a really big itch with DBIx::Class and I want to scratch it really bad, but I want to make sure I don't scratch a big hole by missing something blindingly obvious.

I want to add auto-groupby magic to queries that have a function in the SELECT or ORDER BY clauses that would require a group by. It pisses me off that I have to enumerate all my columns when DBIC has all the necessary information. Well, I think it does.

So, I'm looking for an example of where the ordering of the groupby can cause a different resultset to be returned. Assume that ordering is irrelevant.

[extremely]

ID	SalesID	CityID	Money
1	10	20	1000
2	10	22	2000
3	11	20	4000
4	11	22	8000
5	10	20	3000
6	10	22	5000
7	11	20	7000
8	11	22	9000

select from table SalesID,CityID,Sum(Money) Groupby SalesID,CityID
 -- or
select from table SalesID,CityID,Sum(Money) Groupby CityID, SalesID

Those should vary only in output order of the summed rows. The danger of automatic grouping is when/if someone picks "ID" in the select list... then it all goes to heck. The "common case" for Group By is to list all columns not aggregated in the select_column clause. Automatically forcing group-by seems to be almost assuredly doomed to run into keys and foreign-keys when they are incorrectly added to the columns list.

In your limited case, tho, I'm thinking you're safe.

[dragonchild]

Assuming that ordering is unimportant (because if ordering is important, you'll put an ORDER BY clause in), then does this mean that automagical grouping if there is a function that requires it (count, max, min, sum, etc) is bad or ok?

[terce]

A minor issue - it's valid SQL syntax to include columns in the GROUP BY clause which aren't listed in the SELECT clause (although it's not often used, and I don't know if DBIx::Class supports it).

Adding auto-groupby would break this unless the user is allowed to override the auto-grouping.

It might also become a maintenance trap for the unwary; the existing DBIx::Class syntax is clearly recognisable as a GROUP BY query to somebody familiar with SQL. An auto-grouped version might not - it depends on the implementation

Otherwise, I don't think column order in GROUP BY makes a difference