Understanding non standrad GROUP BY with ONLY_FULL_GROUP_BY sql mode set in MySQL

Question

I understand that ANSI SQL requires non-GROUP BY attributes to be SELECTed with aggregate function.

For example, below is invalid in ANSI SQL:

SELECT name, MAX(salary), dept 
FROM `employee_gb`
GROUP BY dept;

as name is not passed to any aggregate function.

However, MySQL allows this. If we want MySQL to behave like ANSI SQL, we need to set sql mode as explained here. But when I tried that, it still gave me no error:

SET GLOBAL sql_mode = 'ANSI';

I guess above query should give me error. What I am missing here?

Below are queries to populate data:

CREATE TABLE `employee_gb`
(name varchar(50), salary float, dept varchar(50));

INSERT INTO `employee_gb` VALUES('a',1,'dept3'); 
INSERT INTO `employee_gb` VALUES('b',2,'dept4'); 
INSERT INTO `employee_gb` VALUES('c',3,'dept1'); 
INSERT INTO `employee_gb` VALUES('d',4,'dept2'); 
INSERT INTO `employee_gb` VALUES('e',5,'dept2'); 
INSERT INTO `employee_gb` VALUES('g',5,'dept2'); 
INSERT INTO `employee_gb` VALUES('f',6,'dept1');

These are some screenshots for execution from CLI:

user_10676716 · Answer

ONLY_FULL_GROUP_BY was added to sql mode ANSI in MySQL 5.7, as it can be seen by comparing the 5.6 documentation to the 5.7 documentation.

My guess is that you are running MySQL 5.6 or lower. In this version, if you want that specific behavior, you need to set ONLY_FULL_GROUP_BY separately, like:

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';

Demo on DB Fiddle - once the proper sql mode is set, the query errors with:

'employee_gb.name' isn't in GROUP BY

Side note: it is usually not a good idea to reset the whole sql_mode, since it will discard existing modes. You can concatenate instead:

SET SESSION sql_mode = concat_ws(',', @@sql_mode, 'ONLY_FULL_GROUP_BY');

user_1052130 · Answer

SQL mode ANSI is a comnination of REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and (as of MySQL 5.7.5) ONLY_FULL_GROUP_BY.

So, check your MySQL version.

Bill_Karwin_20860 · Answer

Setting the global option does not change the current session's value. A MySQL session copies the values of global variables when the session starts, so a given session is not affected by subsequent changes to global variables (with a few exception cases, like read_only).

Demo:

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> set global sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> select @@global.sql_mode;
+--------------------------------------------------------------------------------+
| @@global.sql_mode                                                              |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.sql_mode;
+------------------------+
| @@session.sql_mode     |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

Try the following to do what you are trying to do:

mysql> SET SESSION sql_mode = 'ANSI';

This will change the current session's value.

An alternative solution is to reconnect after changing the global option, so you start a new session. That will re-read the global values.

mysql> connect;
Connection id:    9
Current database: test

mysql> select @@session.sql_mode;
+--------------------------------------------------------------------------------+
| @@session.sql_mode                                                             |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

user_5193536 · Answer

Only full group by, will not solve your problem, completly, full groupby only forcess you ti decide, when a column is not in the Group By clause , you have to cedice which number you wand. In the following query you see the

SELECT MAX(salary) maxsalary,dept FROM employee_gb GROUP BY dept

all columns are in the group by(dept) or have an aggregation function like MAX(salary). name isn't in this select because databases dont't wirk with rows, so you need alsp to define which name to choose, toget a full GROUP By query.

I removes the neceesaty for this, by using the query as basis to select the correct row, without GROUP BY which would not permit to select the right name.

As you don't specify, what should happen when two have the same salary, you can start from here

CREATE TABLE `employee_gb`
(name varchar(50), salary float, dept varchar(50));

INSERT INTO `employee_gb` VALUES('a',1,'dept3'); 
INSERT INTO `employee_gb` VALUES('b',2,'dept4'); 
INSERT INTO `employee_gb` VALUES('c',3,'dept1'); 
INSERT INTO `employee_gb` VALUES('d',4,'dept2'); 
INSERT INTO `employee_gb` VALUES('e',5,'dept2'); 
INSERT INTO `employee_gb` VALUES('g',5,'dept2'); 
INSERT INTO `employee_gb` VALUES('f',6,'dept1');

SELECT * FROM employee_gb WHERE (salary,dept) IN(SELECT MAX(salary),dept FROM employee_gb GROUP BY dept)

name | salary | dept 
:--- | -----: | :----
a    |      1 | dept3
b    |      2 | dept4
e    |      5 | dept2
g    |      5 | dept2
f    |      6 | dept1

SELECT t1.* FROM employee_gb t1
INNER JOIN (SELECT MAX(salary) maxsalary,dept FROM employee_gb GROUP BY dept) t2
ON t1.salary = t2.maxsalary AND t1.dept = t2.dept

name | salary | dept 
:--- | -----: | :----
a    |      1 | dept3
b    |      2 | dept4
e    |      5 | dept2
g    |      5 | dept2
f    |      6 | dept1

db<>fiddle here

Understanding non standrad GROUP BY with ONLY_FULL_GROUP_BY sql mode set in MySQL

4 Answers