[Solved] MySQL: Syntax error or access violation: 1055 Expression #1 of SELECT list is not …

Reason for error.
MySQL 5.7.5 and above implements detection of functional dependencies. If only_full_group_by SQL mode is enabled (which it is by default), then MySQL rejects queries referenced by select lists, conditions, or sequential lists that will refer to unnamed non-aggregated columns in the group, rather than depend on them functionally. (Prior to 5.7.5, MySQL did not detect functional dependencies, and only_full_group_by was not enabled by default. For a description of the pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)
You can view the contents of sql_mode by executing the following command.

 

　　mysql> SHOW SESSION VARIABLES;

mysql> SHOW GLOBAL VARIABLES;

mysql> select @@sql_mode;

It can be seen that the value of sql_mode for both session and global is

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

only_full_group_by Description. 
only_full_group_by : use this is to use the same group rules as oracle, select the columns to be in the group, or itself is an aggregated column (SUM, AVG, MAX, MIN) only, in fact, this configuration is currently a personal feeling and distinct similar, so remove the good

Solution:
Execute the following two commands.

mysql> set global sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

mysql> set session sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

These two commands, remove the sql_mode of ONLY_FULL_GROUP_BY
However, many users say to directly modify the mysql configuration file by adding sql_mode= to my.ini

[mysqld]
sql_mode=

Huang has also tried it himself. It’s really OK!

Read More: