Does MySQL's `GROUP BY` Extension Conform to SQL Standards, or Is It a Deviation?

MySQL's Extension to GROUP BY: Adherence or Deviation from SQL Standard?

Introduction

Traditionally in SQL Server and other RDBMSs, selecting non-aggregate columns that are not part of the GROUP BY clause in an aggregate query is prohibited by the SQL standard. However, in MySQL, this behavior deviates from the standard, raising questions about MySQL's adherence.

MySQL's Extended GROUP BY

In SQL versions up to 1992, the standard indeed prohibits selecting non-aggregate fields not included in the GROUP BY clause. However, from SQL-2003 onward, the standard allows for selecting columns that are functionally dependent on the grouping columns.

MySQL, in its eagerness to implement this new standard, extended GROUP BY to allow the selection of all columns, not just those functionally dependent on the grouping columns. While this extension conforms to the latest SQL standards, its implementation deviates from the intended behavior.

Consequences of MySQL's Extension

Allowing all columns in the SELECT list, even those not functionally dependent, can lead to indeterminate results if non-group-by columns contain different values within a group. This deviation from the standard can cause confusion and unpredictable behavior for users.

To address this, MySQL introduced the ONLY_FULL_GROUP_BY sql_mode, which disables MySQL's extended GROUP BY behavior and reverts to the more restrictive SQL-92 standard.

MySQL's Rationale and Comparison to Other RDBMSs

MySQL's rationale for this extension is performance optimization. By allowing all columns in the SELECT list, MySQL can avoid unnecessary sorting and grouping, leading to performance improvements.

However, other RDBMSs such as PostgreSQL and later versions of MySQL (5.7) have implemented more standard-compliant approaches by recognizing functional dependencies and restricting non-aggregate columns in the SELECT list accordingly. This ensures both performance and adherence to the SQL standard.

Conclusion

MySQL's extended GROUP BY, while inspired by the SQL standard, deviates from its intended implementation. This extension can lead to indeterminate results and requires a more nuanced understanding of functional dependency. By setting the ONLY_FULL_GROUP_BY sql_mode, users can revert to the more restrictive SQL-92 behavior. Later versions of MySQL (5.7) have improved their handling of GROUP BY to better align with the standard.

The above is the detailed content of Does MySQL's `GROUP BY` Extension Conform to SQL Standards, or Is It a Deviation?. For more information, please follow other related articles on the PHP Chinese website!