0
506
ViewsUnderstanding non standrad GROUP BY with ONLY_FULL_GROUP_BY sql mode set in MySQL
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:
3 answers
Answer question0
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');
0
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.
0
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)