Is MySQL breaking the standard by allowing selecting columns that are not part of the group by clause?

by

Standard SQL would reject your query because you can not SELECT non-aggregate fields that are not part of the GROUP BY clause in an aggregate query

This is correct, up to 1992.

But it is plainly wrong, from 2003 and beyond.

From SQL-2003 standard, 6IWD6-02-Foundation-2011-01.pdf, from http://www.wiscorp.com/, paragraph-7.12 (query specification), page 398:

  1. If T is a grouped table, then let G be the set of grouping columns of T. In each ((value expression)) contained
    in ((select list)) , each column reference that references a column of T shall reference some column C that
    is functionally dependent on G or shall be contained in an aggregated argument of a ((set function specification))
    whose aggregation query is QS

Now MYSQL, has implemented this feature by allowing not only columns that are functionally dependent on the grouping columns but allowing all columns. This is causing some problems with users that do not understand how grouping works and get indeterminate results where they don’t expect.

But you are right to say that MySQL has added a feature that conflicts with SQL-standards (although you seem to think that for the wrong reason). It’s not entirely accurate as they have added a SQL-standard feature but not in the best way (more like the easy way) but it does conflict with the latest standards.

To answer your question, the reason for this MySQL feature (extension) is I suppose to be accordance with latest SQL-standards (2003+). Why they chose to implement it this way (not fully compliant), we can only speculate.

As @Quassnoi and @Johan answered with examples, it’s mainly a performance and maintainability issue. But one can’t easily change the RDBMS to be clever enough (Skynet excluded) to recognize functionally dependent columns, so MySQL developers made a choice:

We (MySQL) give you (MySQL users) this feature which is in SQL-2003 standards. It improves speed in certain GROUP BY queries but there’s a catch. You have to be careful (and not the SQL engine) so columns in the SELECT and HAVING lists are functionally dependent on the GROUP BY columns. If not, you may get indeterminate results.

If you want to disable it, you can set sql_mode to ONLY_FULL_GROUP_BY.

It’s all in the MySQL docs: Extensions to GROUP BY (5.5) – although not in the above wording but as in your quote (they even forgot to mention that it’s a deviation from standard SQL-2003 while not standard SQL-92). This kind of choices is common I think in all software, other RDBMS included. They are made for performance, backward compatibility and a lot of other reasons. Oracle has the famous '' is the same as NULL for example and SQL-Server has probably some, too.

There is also this blog post by Peter Bouman, where MySQL developers’ choice is defended: Debunking GROUP BY myths.

In 2011, as @Mark Byers informed us in a comment (in a related question at DBA.SE), PostgreSQL 9.1 added a new feature (release date: September 2011) designed for this purpose. It is more restrictive than MySQL’s implementation and closer to the standard.

Later, in 2015 MySQL announced that in 5.7 version, the behaviour is improved to conform with the standard and actually recognize functional dependencies, (even better than the Postgres implementation). The documentation: MySQL Handling of GROUP BY (5.7) and another blog post by Peter Bouman: MySQL 5.7.5: GROUP BY respects functional dependencies!

More Related Contents:

  1. How to use IN & NOT IN same query [closed]
  2. Out of memory Microsoft VBScript runtime error 800a0007
  3. Left Outer Join doesn’t return all rows from my left table?
  4. Are table names in MySQL case sensitive?
  5. MySQL: Large VARCHAR vs. TEXT?
  6. MySQL: is a SELECT statement case sensitive?
  7. Check overlap of date ranges in MySQL
  8. 1114 (HY000): The table is full
  9. Get table names using SELECT statement in MySQL
  10. UPDATE multiple tables in MySQL using LEFT JOIN
  11. Sorting varchar field numerically in MySQL
  12. setting global sql_mode in mysql
  13. Compare dates in MySQL
  14. MySQL root password change
  15. MySQL join query using like?
  16. MySQL: When is Flush Privileges in MySQL really needed?
  17. MySQL too long varchar truncation/error setting
  18. MySQL: update a field only if condition is met
  19. MySql Query: Select top 3 rows from table for each category
  20. how to use DISTINCT ON with mysql using ActiveRecord
  21. MySQL: How to select records for this week?
  22. Split a string and loop through values in MySQL stored procedure
  23. MySQL Select Query – Get only first 10 characters of a value
  24. Create an index on a huge MySQL production table without table locking
  25. MySQL concurrency, how does it work and do I need to handle it in my application
  26. mysql select id and name from other table and join query
  27. How to import large sql file in phpmyadmin
  28. Equivalent of MSSQL IDENTITY Column in MySQL
  29. #1060 – Duplicate column name ‘id’
  30. Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user ‘root’@’localhost'(using password:YES)

Leave a Comment