mysql error -1055 – Expression #1 of SELECT list is not in GROUP BY clause solution

Table of Contents

  • Business scene
  • problem found
    • Table Structure
    • table data
    • sql query
  • analyse problem
    • verify
  • Solve the problem
    • Option One
    • Option II
    • third solution
  • Precautions

Business scenario

When you encounter duplicate data in the database, you need to group the data and take one of them to display. At this time, you need to use the group by statement.
But when the mysql version is higher than 5.7, when executing group by, if the select field does not belong to the group by field, an error will be reported. The error message is as follows:

1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘database name. table name. field name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
>Time: 0.233s

Problem found

Table structure

CREATE TABLE `t_iov_help_feedback` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID',
  `USER_ID` INT(255) DEFAULT NULL COMMENT 'User ID',
  `problems` VARCHAR(255) DEFAULT NULL COMMENT 'problem description',
  `last_updated_date` DATETIME DEFAULT NULL COMMENT 'Last updated time',
  PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Table data

INSERT INTO `test`.`feedback` (`id`, `user_id`, `problems`, `last_updated_date`) VALUES (1, 1, 'Time is relatively small', '2023-06-15 16:53:25');
INSERT INTO `test`.`feedback` (`id`, `user_id`, `problems`, `last_updated_date`) VALUES (2, 2, 'Time is small', '2023-06-15 16:53: 57');
INSERT INTO `test`.`feedback` (`id`, `user_id`, `problems`, `last_updated_date`) VALUES (3, 3, 'wrong', '2023-06-15 16:54 :26');
INSERT INTO `test`.`feedback` (`id`, `user_id`, `problems`, `last_updated_date`) VALUES (4, 1, 'Unqualified', '2023-06-15 16:54: 52');
INSERT INTO `test`.`feedback` (`id`, `user_id`, `problems`, `last_updated_date`) VALUES (5, 2, 'qualified', '2023-06-15 16:55:22 ');
INSERT INTO `test`.`feedback` (`id`, `user_id`, `problems`, `last_updated_date`) VALUES (6, 4, 'very useful', '2023-06-15 16:56: 26');

sql query

1) Query the fields of GROUP BY (normal)

SELECT user_id FROM feedback GROUP BY user_id;


2) The query uses aggregate functions (normal)

SELECT MAX(id), user_id FROM feedback GROUP BY user_id;


3) Query non-group by fields (abnormal)

SELECT id,user_id FROM feedback GROUP BY user_id;


wrong description:

“Error Code: 1055, Expression #1 of the SELECT list is not in the GROUP BY clause and contains non-aggregate
Column ‘test.feedback.id’, which is not functionally dependent on columns in the GROUP BY clause; this is the same as sql_mode=
only_full_group_by is not compatible”

Analyze the problem

1) Principle level

This error will occur in mysql version 5.7 and above. The default sql configuration of mysql version 5.7 and above is: sql_mode=”ONLY_FULL_GROUP_BY”, this configuration strictly enforces the “SQL92 standard”.
When many programmers upgrade from 5.6 to 5.7, for the sake of syntax compatibility, most of them will choose to adjust sql_mode to keep it consistent with 5.6, in order to be as compatible as possible with the program.

2) SQL level

When sql is executed, the reason appears, in simple terms:
Since ONLY_FULL_GROUP_BY is enabled, if the select field is not in group by,
And if the select field does not use aggregate functions (SUM, AVG, MAX, MIN, etc.), then this sql query is considered illegal by mysql.

Authentication

1) Query the database version

SELECT VERSION();


Apparently, the database version 5.7.29 is greater than 5.7.

2) View sql_mode

select @@GLOBAL.sql_mode;


The value of sql_mode

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Obviously, sql_mode opens the ONLY_FULL_GROUP_BY attribute.

Fix the problem

Option 1

Use ANY_VALUE() to include non-group by fields

SELECT ANY_VALUE(id), user_id FROM feedback GROUP BY user_id;


It can be seen that the problem is easily solved!

ANY_VALUE() function description

MySQL has any_value(field) function, its main function is to suppress ONLY_FULL_GROUP_BY value from being rejected.
In this way, the sql statement can be executed normally no matter whether the ONLY_FULL_GROUP_BY mode is closed or in the open mode, and will not be rejected by mysql.
any_value() will select the specified column value of the first piece of data in the data classified into the same group as the returned data.
Official introduction, address: https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

Option 2

Temporarily modify sql_mode through sql statement, that is, remove the ONLY_FULL_GROUP_BY attribute

SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

The above is to change the global sql_mode, which is valid for the newly created database. For existing databases, you need to execute under the corresponding database:

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

There is a problem with this solution: after restarting the mysql database service, ONLY_FULL_GROUP_BY will still appear, so this is only temporary.

Option 3

Permanently modify sql_mode through the configuration file
1) Modify the configuration file under Linux
Edit the my.cnf file, the file address is generally: /etc/my.cnf, /etc/mysql/my.cnf
Find sql_mode and remove the ONLY_FULL_GROUP_BY attribute. If there is no sql_mode configuration, just add it under [mysqld].

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Be sure to restart mysql after saving

service mysql restart

2) Modify the configuration file under the window
Find the mysql installation directory, open the my.ini file directly with Notepad
Similarly, add sql_mode, as follows:

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Notes

After permanently modifying sql_mode, whether it is linux or windows, the mysql service must be restarted to take effect