mysql error: 1055 – expression #2 of select list is not in group by clause and contains nonaggregated columnmodel.group_idwhich is not functionally depend on columns in group by clause;
1. Mysql error: 1055 – expression #2 of select list is not in group by clause and contains nonaggregated columnmodel.group_idwhich is not functionally depend on columns in group by cluse; as shown in Figure 1
Figure 1
SELECT * FROM ((SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')) UNION ALL ( SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_pre_pub_log` ON `cpa_task_group`.`id` = `cpa_pre_pub_log`.`task_group_id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_pre_pub_log`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c') )) `model` GROUP BY `id`
2. Execute the first SQL clause, no error is reported, and the query result is empty. as shown in Figure 2
Figure 2
SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')
3. Execute the 2nd SQL clause, no error is reported, and the query result is empty. as shown in Figure 3
Figure 3
SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')
4. Compare the fields of the query results of the 2 SQL clauses respectively. The results were found to be exactly the same.
id group_id uuid source source_article_id status pubed_at is_deleted created_at updated_at deleted_at
id group_id uuid source source_article_id status pubed_at is_deleted created_at updated_at deleted_at
5. Adjust the SQL statement to explicitly declare each field. That is, the query field and the grouped field should be exactly the same. There will be no errors. as shown in Figure 4
Figure 4
SELECT id,group_id FROM ((SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')) UNION ALL ( SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_pre_pub_log` ON `cpa_task_group`.`id` = `cpa_pre_pub_log`.`task_group_id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_pre_pub_log`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c') )) `model` GROUP BY `id`,group_id
6. Reference URL:https://stackoverflow.com/questions/40662899/sqlstate42000-syntax-error-or-access-violation-1055-expression-2. Modify the my.ini file. Because, the cost of modifying the SQL statement is too high. No more errors. as shown in Figure 5
Figure 5
# 当前的配置
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# 修改后的配置，添加：STRICT_ALL_TABLES
sql_mode=STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
