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:

In minimal environment ANSI sql mode forbids your query. See [fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0d424dd649b3f91f8578e915cc2f2ee0). I think that your client sends additional queries and resets SQL mode. Try to execute your code using CLI. Or see real statements sent to MySQL in General Log. — Akina, Jun 21 '20 at 20:34

GMB · Answer 1 · 2020-06-21T23:30:25.997

2

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');

edited Jun 21 '20 at 23:30

answered Jun 21 '20 at 20:44

GMB

216,147
25
84
135

Yes I am on version 5.5.36. But I tried setting `ONLY_FULL_GROUP_BY` separately. Can you check last screenshot in question. – MsA Jun 21 '20 at 21:12
@anir: you can see in the screenshot that the parameter is not reflected after your `set global`. As showned in the fiddle in my answer (and as explained by Bill Karwin) you need `set session` instead. – GMB Jun 21 '20 at 22:05
Yes but why `SET GLOBAL` does not get reflected? – MsA Jun 22 '20 at 01:26
SET GLOBAL changes the global value, but changing globals does not update the values in each current session. It only applies to new sessions created subsequently. The reason is that the first thing each new session does is make a _copy_ of the global variables, and that copy is its set of session variables. – Bill Karwin Jun 22 '20 at 01:50

score 1 · Answer 2 · answered Jun 21 '20 at 20:42

1

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.

answered Jun 21 '20 at 20:42

slaakso

8,331
2
16
27

score 1 · Answer 3 · answered Jun 21 '20 at 21:22

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)

nbk · Answer 4 · 2020-06-21T22:02:36.840

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 Answers4