Bug #119761 there is something wrong about join+group by suition,ONLY_FULL_GROUP_BY don't effect

• Submitted: 23 Jan 9:47
• Modified: 23 Jan 10:25
• Reporter: tom tom
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0 5.7
• OS: Linux
• CPU Architecture: Any

[23 Jan 9:47] tom tom

Description:

select a.tablename,b.period,count(1) From manage_partitions a,manage_partitions b where a.tablename=b.tablename group by a.tablename;

mysql  Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL) : 可以执行
mysql  Ver 5.7.16 for Linux on x86_64 (MySQL Community Server - GPL) : 可以执行

but b.period is not in group by clause;

How to repeat:
CREATE TABLE `manage_partitions` (
  `tablename` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `period` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `keep_history` int DEFAULT NULL,
  `last_updated` date DEFAULT NULL,
  `comments` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

select a.tablename,b.period,count(1) From manage_partitions a,manage_partitions b where a.tablename=b.tablename group by a.tablename

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 

mysql  Ver 8.4.5 for Linux on x86_64 (MySQL Community Server - GPL)：不可执行 （也是正常期望结果）
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b.period' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql  Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL) : 可以执行
mysql  Ver 5.7.16 for Linux on x86_64 (MySQL Community Server - GPL) : 可以执行

[23 Jan 10:25] Roy Lyseng

In general, in a grouped query you cannot select a column that is not the GROUP BY list. Unless it is functionally dependent on the group list columns.
This is what the error message says.
Not a bug.