Does MySQL's Non-Standard GROUP BY Behavior Violate SQL Standards?

Is MySQL Breaking the Standard with Non-Standard GROUP BY Behavior?

Introduction

MySQL's extension to the GROUP BY clause, allowing the selection of columns not included in the grouping, has raised questions regarding adherence to SQL standards. This article explores the historical context and implications of this deviation.

The Standard

Prior to SQL-2003, it was standard practice to prohibit the selection of non-aggregated columns that were not part of the GROUP BY clause. This restriction ensured consistent results by eliminating ambiguous groupings.

MySQL's Extension

MySQL introduced an extension that allows non-aggregated columns to be selected even when they are not part of the GROUP BY. According to MySQL documentation, this is done for performance optimization by avoiding unnecessary sorting and grouping. However, it comes with a caveat: the non-aggregated values may be indeterminate, meaning the server can choose any value from each group.

Standard SQL Interpretation

The SQL-2003 standard allows non-aggregated column references in the select list if they are functionally dependent on the grouping columns or are contained in an aggregated argument. Functional dependency ensures that for each value of the grouping columns, there is a single unique value for the non-aggregated columns.

MySQL's Implementation

MySQL's extension deviates from the standard by allowing all columns to be selected, even those not functionally dependent on the grouping columns. This can lead to unexpected results when the non-aggregated values vary within groups.

Consequences of MySQL's Extension

MySQL's implementation has two primary implications:

• Performance Optimization: The extension can enhance performance in certain GROUP BY queries by avoiding unnecessary sorting and grouping.
• Indeterminate Results: The drawback of this extension is that non-aggregated columns may return indeterminate values, leading to inconsistent results.

Addressing MySQL's Deviation

MySQL users can disable the non-standard behavior by setting the sql_mode to ONLY_FULL_GROUP_BY. Additionally, improvements were introduced in MySQL 5.7 to better comply with the standard. PostgreSQL 9.1 also offers a more restrictive implementation that closely adheres to functional dependency requirements.

The above is the detailed content of Does MySQL's Non-Standard GROUP BY Behavior Violate SQL Standards?. For more information, please follow other related articles on the PHP Chinese website!