ORDER BY contains aggregate function and applies to the result of a non-aggregated query

[Alkaagr81]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

 SET @@sql_mode='ONLY_FULL_GROUP_BY';
drop table if exists t1,t2;
CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
CREATE TABLE t2 (a INT, b INT, c INT DEFAULT 0);
INSERT INTO t2 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
 SET @@sql_mode='';
SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));

2. What did you expect to see? (Required)

mysql> SET @@sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t1,t2;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (a INT, b INT, c INT DEFAULT 0);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
+------+
| a    |
+------+
|    3 |
|    2 |
|    4 |
+------+
3 rows in set (0.01 sec)

mysql> SET @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
+------+
| a    |
+------+
|    3 |
|    2 |
|    4 |
+------+
3 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> SET @@sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t1,t2;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (a INT, b INT, c INT DEFAULT 0);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t2 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
ERROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query

mysql> SET @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
ERROR 1054 (42S22): Unknown column 't1.b' in 'order clause'

4. What is your TiDB version? (Required)

| Release Version: v5.2.2
Edition: Community
Git Commit Hash: da1c21fd45a4ea5900ac16d2f4a248143f378d18
Git Branch: heads/refs/tags/v5.2.2
UTC Build Time: 2021-10-20 06:03:45
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[winoros]

If we don't consider the ONLY FULL GROUP BY checking, this one is similar with #29084

[winoros]

We leave the only full group by checking part to issue #29766.
The duplicate part would be solved in #29084.
So we downgrade the severity of this issue.

[AilinKid]

kind of name resolver problem, the agg inside sub-query is actually correlated with the outside group, so does the col name inside the agg func. this two expr should be built/kept in the outer schema, otherwise, when we are building having clause, the sub-query couldn't find the reference anywhere.

[AilinKid]

winoros#11 in our latest feature branch, this has been fixed