Order by with aggregation function in sub query not working

[Alkaagr81]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set @@sql_mode = default;
drop table if exists t1,t2;
CREATE TABLE t1(a INTEGER);
INSERT INTO t1 VALUES (1), (2);
SELECT a FROM t1 ORDER BY (SELECT COUNT(t1.a) FROM t1 AS t2);
SELECT SUM(a) FROM t1 ORDER BY (SELECT COUNT(t1.a) FROM t1 AS t2);
SELECT SUM(a) FROM t1 ORDER BY (SELECT COUNT(t2.a) FROM t1 AS t2);

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

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

mysql> drop table if exists t1,t2;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1(a INTEGER);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a FROM t1 ORDER BY (SELECT COUNT(t1.a) FROM t1 AS t2);
ERROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query
mysql> SELECT SUM(a) FROM t1 ORDER BY (SELECT COUNT(t1.a) FROM t1 AS t2);
+--------+
| SUM(a) |
+--------+
|      3 |
+--------+

mysql> SELECT SUM(a) FROM t1 ORDER BY (SELECT COUNT(t2.a) FROM t1 AS t2);
+--------+
| SUM(a) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

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

mysql> drop table if exists t1,t2;
Query OK, 0 rows affected (0.51 sec)

mysql> CREATE TABLE t1(a INTEGER);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t1 VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a FROM t1 ORDER BY (SELECT COUNT(t1.a) FROM t1 AS t2);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> SELECT SUM(a) FROM t1 ORDER BY (SELECT COUNT(t1.a) FROM t1 AS t2);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> SELECT SUM(a) FROM t1 ORDER BY (SELECT COUNT(t2.a) FROM t1 AS t2);
+--------+
| SUM(a) |
+--------+
|      3 |
+--------+
1 row in set (0.01 sec)

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 |

[sylzd]

it can be optimized by logic plan, when sum encounter order by, we can prune the order by, make it a simple query. any suggestions or duplicated task? @winoros @XuHuaiyu

mysql > explain SELECT SUM(a) FROM t1 ORDER BY (SELECT COUNT(t2.a) FROM t1 AS t2);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Mon Nov 29 20:20:01 2021
mysql > show warnings;
+-------+------+-------------------------------------------------------------------------+
| Level | Code | Message                                                                 |
+-------+------+-------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select sum(`test`.`t1`.`a`) AS `SUM(a)` from `test`.`t1` |
+-------+------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

[AilinKid]

after order by FD

mysql> SELECT a FROM t1 ORDER BY (SELECT COUNT(t1.a) FROM t1 AS t2);
ERROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query
mysql> SELECT SUM(a) FROM t1 ORDER BY (SELECT COUNT(t1.a) FROM t1 AS t2);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> SELECT SUM(a) FROM t1 ORDER BY (SELECT COUNT(t2.a) FROM t1 AS t2);
+--------+
| SUM(a) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

[AilinKid]

for the second query, as sylzd said, COUNT(t1.a) is always the same since outer query is seen as a whole group. In this way, order by clause can be eliminated, leading unnecessary to checking multi-row of a subquery.