MySQL Bugs: #100395: No error reports on aggregates without group-by with ONLY_FULL_GROUP_BY sql

Bug #100395	No error reports on aggregates without group-by with ONLY_FULL_GROUP_BY sql_mode
Submitted:	31 Jul 2020 6:41	Modified:	1 Sep 2020 13:18
Reporter:	Hubery Lu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.13, 8.0.18, 8.0.21, 5.7.31	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	SQL_MODE; ONLY_FULL_GROUP_BY

Description:
With sql_mode=only_full_group_by, aggregates should come with group-bys, if not, error like the following one should be thrown:

In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'T_1.C2'; this is incompatible with sql_mode=only_full_group_by

However, if the columns come from const tables, mysql would not complain. In some other databases, e.g., ClickHouse, Postgresql, this kind of queries would be failed to execute.

Mysql:
======
SELECT MIN(T_1.C1) AS C1, T_1.C2 FROM (SELECT C1, C2 FROM T1 WHERE C1 = 1 AND C2 != 'BBB') T_1, (SELECT C21 FROM T2) T_2;
+------+------+
| C1   | C2   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

ClickHouse:
===========
Received exception from server (version 20.6.1):
Code: 215. DB::Exception: Received from localhost:9100. DB::Exception: Column `C2` is not under aggregate function and not in GROUP BY.

How to repeat:
CREATE TABLE T1 (C1 INT PRIMARY KEY, C2 VARCHAR(10));
INSERT INTO T1 VALUES (1, 'AAA');

CREATE TABLE T2 (C21 INT NOT NULL, C22 VARCHAR(10) NOT NULL);

SELECT MIN(T_1.C1) AS C1, T_1.C2 FROM (SELECT C1, C2 FROM T1 WHERE C1 = 1 AND C2 != 'BBB') T_1, (SELECT C21 FROM T2) T_2;
+------+------+
| C1   | C2   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT MIN(T_1.C1) AS C1, T_1.C2 FROM (SELECT C1, C2 FROM T1) T_1, (SELECT C21 FROM T2) T_2;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'T_1.C2'; this is incompatible with sql_mode=only_full_group_by

Hello Hubery Lu,

Thank you for the report and test case.

regards,
Umesh

Interestingly, MySQL is adding GROUP BY to the query, maybe because of functional dependency?  

insert into T2 values (1,'1'), (1,'1');
Query OK, 2 rows affected (0.01 sec)

mysql>  SELECT MIN(T_1.C1) AS C1, T_1.C2, count(*) FROM (SELECT C1, C2 FROM T1 WHERE C1 = 1 AND C2 != 'BBB') T_1, (SELECT C21 FROM T2) T_2;
+------+------+----------+
| C1   | C2   | count(*) |
+------+------+----------+
|    1 | AAA  |        2 |
+------+------+----------+
1 row in set (0.00 sec)

So it apparently doesn't trigger ONLY_FULL_GROUP_BY because the statement is using an implicit GROUP BY

If you remove the condition C1 = 1 from the where clause the functional dependency no longer exists, and the query returns an error:
SELECT MIN(T_1.C1) AS C1, T_1.C2, count(*) FROM (SELECT C1, C2 FROM T1 WHERE C2 != 'BBB') T_1, (SELECT C21 FROM T2) T_2;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'T_1.C2'; this is incompatible with sql_mode=only_full_group_by

Posted by developer:
 
I think MySQL is correct here. Query is:
SELECT MIN(T_1.C1) AS C1, T_1.C2 FROM (SELECT C1, C2 FROM T1 WHERE C1 = 1 AND
C2 != 'BBB') T_1, (SELECT C21 FROM T2) T_2;
It has MIN() so it's an aggregated query. It has no GROUP BY, so it will aggregate to one single row (it's like if there were an implicit "GROUP BY no_columns_listed").
The standard says that selected expressions should then be either aggregates, or functionally dependent on GROUP BY columns. If I say that column A is functionally dependent on column B, it means that if two rows have the same value in B they have the same value in A. Here the list of columns in GROUP BY is empty, so saying that column A is functionally dependent on "no columns" means that if two rows have the same value in "no columns" they have the same value in A. All rows have the same value in "no columns" (this value is: "nothing"). So, all rows must have the same value of A.
In the example query: C1 is a primary key, C1=1 identifies a unique row of T1, so there is only one value of C2, so all rows have the same value for it. It's fine.
I'm saying what Justin explained, in a different way.
It's not so surprising that other DBMSs throw an error. If I remember correctly, a few years ago MySQL was among the few ones with advanced functional dependency detection.