“This is the seventh day of my participation in the First Challenge 2022.

preface

Some time ago, I found a system exception in the project development process. When I opened the log to check, I found the following error: Search for relevant information, this is because the mysql database SQL_mode =only_full_group_by was enabled, because the product was urgent to go online, so the group leader said to contact the DBA first to turn off SQL_mode = only_full_group_BY, and then change later.

So, today the group leader gave me this task.

ONLY_FULL_GROUP_BY

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

Reject queries that select lists, HAVING conditions, or ORDER BY lists that reference non-aggregated columns that are neither named in the GROUP BY clause nor functionally dependent on a (uniquely determined) GROUP BY column. (Google Translate)

The solution

MySQL GROUP BY

1. Close the sql_mode = only_full_group_by

2. The official explanation: You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column. You can achieve the same effect by referring to non-aggregated columns with ANY_VALUE() without ONLY_FULL_GROUP_BY disabled.

Problem of repetition

Let’s do a small example to reproduce this error:

Data preparation

  1. Create a table
CREATE TABLE `emp`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL.`age` INT(3),
	`salary` DOUBLE(10.2),
	PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code
  1. Insert data
	INSERT INTO `emp` VALUE(null.'Joe'.21.10000);
	INSERT INTO `emp` VALUE(null.'bill'.22.15000);
	INSERT INTO `emp` VALUE(null.'Cathy'.23.20000);
	INSERT INTO `emp` VALUE(null.'Daisy'.24.25000);
	INSERT INTO `emp` VALUE(null.'money seven'.21.10000);
	INSERT INTO `emp` VALUE(null.'sun eight'.22.15000);
	INSERT INTO `emp` VALUE(null.'li nine'.23.20000);
	INSERT INTO `emp` VALUE(null.'von ten'.24.25000);
Copy the code

Check the sql_mode

Run the following SQL command to check whether the SQL mode contains ONLY_FULL_GROUP_BY

SELECT @@session.sql_mode;
Copy the code

ONLY_FULL_GROUP_BY is enabled in sql_mode.

If the function is disabled, run the following command to enable it:

SET SESSION sql_mode = sys.list_add(@@session.sql_mode, ‘ONLY_FULL_GROUP_BY’);

Close:

SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, ‘ONLY_FULL_GROUP_BY’);

My SQL mode ONLY_FULL_GROUP_BY is currently on, so let’s test GROUP_BY

The SQL instance

First let’s do a correct query:

SELECT age, Max(salary) FROM emp GROUP BY age;
Copy the code

There is no effect on aggregated columns. Only non-aggregated columns are checked for GROUP BY list.

Here is an error query:

SELECT id.`name` age, Max(salary) FROM emp GROUP BY age;
Copy the code

1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘sql_mode_test.emp.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Solutions:

  1. Change sql_mode to remove the ONLY_FULL_GROUP_BY configuration
SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');
Copy the code

Query again:

  1. Add ANY_VALUE to the non-aggregated column, non-group By column
SELECT ANY_VALUE(id) as id,ANY_VALUE(name) as name ,age, Max(salary) FROM emp GROUP BY age;
Copy the code

Obviously, both methods will keep the program from reporting errors, but the id and name values are not the only values that are determined. They are simply filled with one of the eligible groups.

  1. GROUP BY followed BY a non-aggregated column in the SELECT list resolves the error but changes the SQL query result, so it is not appropriate
SELECT id.`name` age, Max(salary) FROM emp GROUP BY age,id.name;
Copy the code

* Note that when ONLY_FULL_GROUP_BY mode is enabled, an error will be reported when the query does not have a group by statement and the select list has an aggregate function

SELECT `name`.MAX(age) FROM emp;
Copy the code

1140 – In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘sql_mode_test.emp.name’; this is incompatible with sql_mode=only_full_group_by

This can be done using ANY_VALUE() :

SELECT ANY_VALUE(`name`) as name , MAX(age) FROM emp;
Copy the code

Summary:

When ONLY_FULL_GROUP_BY is enabled in SQL mode, if GROUP BY exists, the non-aggregated column must be a column in the GROUP BY list. If it is not a column after GROUP BY, use ANY_VALUE(column) to convert the column to an aggregated column.

If there is no GROUP BY, SELECT aggregated columns and non-aggregated columns cannot exist at the same time, and can be converted to aggregated columns using ANY_VALUE(column) if both exist.

Back to the project

Looking at the mapper file, I looked at the query SQL containing GROUP BY and saw this SELECT, SELECT all fields.

ONLY_FULL_GROUP_BY cannot be turned off, only SQL can be changed, so I was wondering if there is a way to automatically change the value of ANY_VALUE(), and I came up with an interceptor. Mybits provides interceptors for SQL. So I started experimenting, and I’ll stop here today and update my solution later. If you have a better way, you can also leave your valuable opinion in the comments section.