CODEWITHSUNDEEP
mysql
LKW
LKW

Reputation: 180

Strange behavior of MySQL aggregate function

in mySQL, following works: Case 1:

Select X , MAX(Y) from table

but in MS SQL Server, you will get "it is not contained in either an aggregate function or the GROUP BY clause."

the proper way will be 

Select X, MAX(Y) from table group by X

more worse, In mySQL, you can: Case 2:

Select X, Y, MAX(Z) from table group by X

My question is, how MySQL determine the Y in above case? how about the X value in case 1? Why MySQL allows such strange behavior?

Upvotes: 1

Views: 126

Answers (1)

Shadow
Shadow

Reputation: 34285

Mysql's documentation on group by handling explains in great detail how and why mysql behaves under certain configuration settings when you use group by.

As @Mihai already pointed out in his comment, mysql has only full group by sql mode that governs whether to

permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

The reason for allowing such relaxation of syntax is that in many cases tables / views may contain fields that are functionally dependent on other fields. In simple words: one field's value determines the other fields value. With the relaxed syntax you only have to include the field(s) that determine the value of the other fields in the group by clause.

If you use the relaxed syntax, but the functional dependency does not exist, then

the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want.

In practice, mysql picks the 1st value for such fields that it encounters when scanning the data, so it is not completely random. However, relying on this feature is a bit suicidal, since mysql can change this behaviour anytime without any notice.

As I noted already in a comment, mysql is not unique with this approach. Sybase also allows this relaxed syntax:

Transact-SQL extensions to group by and having

Transact-SQL extensions to standard SQL make displaying data more flexible, by allowing references to columns and expressions that are not used for creating groups or summary calculations:

A select list that includes aggregates can include extended columns that are not arguments of aggregate functions and are not included in the group by clause. An extended column affects the display of final results, since additional rows are displayed.

Its behaviour is different from that of mysql's though.

Upvotes: 1

Related Questions