1. MySQL error: 1055 – Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘model.group_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. As shown in 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. Executed the 1st SQL clause without errors, and the query result was empty. As shown in 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. Executed the 2nd SQL clause without errors, and the query result was empty. As shown in 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. Compared the fields of the query results from the 2 SQL clauses respectively. Found that the results are 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. Adjusted the SQL statement to explicitly declare every field. That is, the query fields and the grouping fields must be exactly consistent. Then there will be no error. As shown in 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 . Modified the my.ini file. Because the cost of modifying the SQL statement is too high. No more errors. As shown in 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