CODEWITHSUNDEEP
mysqlsqlvalidationerror-handlingwarnings
ks1322
ks1322

Reputation: 35795

mysql GROUP BY clause validation

Suppose I have a table with the following content:

mysql> select * from test;
+----+------+
| id | val  |
+----+------+
|  1 |    1 |
|  2 |    1 |
|  3 |    1 |
|  4 |    1 |
|  5 |    2 |
|  6 |    2 |
|  7 |    2 |
|  8 |    2 |
+----+------+
8 rows in set (0.00 sec)

mysql>

Now I run erroneous SQL query with group by clause and without any aggregation on id column and get wrong results:

mysql> select id, val from test group by val;
+----+------+
| id | val  |
+----+------+
|  1 |    1 |
|  5 |    2 |
+----+------+
2 rows in set (0.00 sec)

mysql>

Can mysql client or probably some other tool validate this query and issue error or warning on using group by without aggregation?

Upvotes: 5

Views: 1920

Answers (2)

John Woo
John Woo

Reputation: 263813

By default, the SQL Mode ONLY_FULL_GROUP_BY is set to disable. And that is the reason why your query did run without throwing any exceptions. If you don't want that behavior enable the ONLY_FULL_GROUP_BY mode,

Setting the SQL Mode

  • You can set the default SQL mode by starting mysqld with the --sql-mode="modes" option, or by using sql-mode="modes" in my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by comma (“,”) characters.
  • You can change the SQL mode at runtime by using a SET [GLOBAL|SESSION] sql_mode='modes' statement to set the sql_mode system value. ex:
    SET sql_mode = 'ONLY_FULL_GROUP_BY'

Upvotes: 1

user1919238
user1919238

Reputation:

Yes, you can do this:

To disable the MySQL GROUP BY extension, enable the ONLY_FULL_GROUP_BY SQL mode.

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';

See the documentation here. Also, this section on server modules may help.

Upvotes: 8

Related Questions