unexpected error: ERROR 1111 (HY000): Invalid use of group function

[jackysp]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE student (id int(11) DEFAULT NULL,
student_name varchar (20) DEFAULT NULL,
test_score int (11) DEFAULT NULL
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

select (SELECT @ids:=GROUP_CONCAT(test_score) FROM student where find_in_set(student_name,@ids)) from student;

This case with a user var in it.

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

No error.

3. What did you see instead (Required)

error: ERROR 1111 (HY000): Invalid use of group function

The stack:

[planner:1111]Invalid use of group function
github.com/pingcap/errors.AddStack
    /Users/yusp/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20220729040631-518f63d66278/errors.go:174
github.com/pingcap/errors.Trace
    /Users/yusp/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20220729040631-518f63d66278/juju_adaptor.go:15
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteExprNode
    /Users/yusp/work/tidb/planner/core/expression_rewriter.go:201
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteWithPreprocess
    /Users/yusp/work/tidb/planner/core/expression_rewriter.go:145
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewrite
    /Users/yusp/work/tidb/planner/core/expression_rewriter.go:113
github.com/pingcap/tidb/planner/core.(*userVarTypeProcessor).Enter
    /Users/yusp/work/tidb/planner/core/logical_plan_builder.go:1251
github.com/pingcap/tidb/parser/ast.(*VariableExpr).Accept
    /Users/yusp/work/tidb/parser/ast/expressions.go:1287
github.com/pingcap/tidb/parser/ast.(*SelectField).Accept
    /Users/yusp/work/tidb/parser/ast/dml.go:726
github.com/pingcap/tidb/parser/ast.(*FieldList).Accept
    /Users/yusp/work/tidb/parser/ast/dml.go:763
github.com/pingcap/tidb/parser/ast.(*SelectStmt).Accept
    /Users/yusp/work/tidb/parser/ast/dml.go:1446
github.com/pingcap/tidb/parser/ast.(*SubqueryExpr).Accept
    /Users/yusp/work/tidb/parser/ast/expressions.go:396
github.com/pingcap/tidb/planner/core.(*PlanBuilder).preprocessUserVarTypes
    /Users/yusp/work/tidb/planner/core/logical_plan_builder.go:1271
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildProjection
    /Users/yusp/work/tidb/planner/core/logical_plan_builder.go:1301
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelect
    /Users/yusp/work/tidb/planner/core/logical_plan_builder.go:4052
github.com/pingcap/tidb/planner/core.(*PlanBuilder).Build
    /Users/yusp/work/tidb/planner/core/planbuilder.go:793
github.com/pingcap/tidb/planner.buildLogicalPlan
    /Users/yusp/work/tidb/planner/optimize.go:439
github.com/pingcap/tidb/planner.optimize
    /Users/yusp/work/tidb/planner/optimize.go:362
github.com/pingcap/tidb/planner.Optimize
    /Users/yusp/work/tidb/planner/optimize.go:245
github.com/pingcap/tidb/executor.(*Compiler).Compile
    /Users/yusp/work/tidb/executor/compiler.go:114
github.com/pingcap/tidb/session.(*session).ExecuteStmt
    /Users/yusp/work/tidb/session/session.go:2121
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt
    /Users/yusp/work/tidb/server/driver_tidb.go:231
github.com/pingcap/tidb/server.(*clientConn).handleStmt
    /Users/yusp/work/tidb/server/conn.go:2049
github.com/pingcap/tidb/server.(*clientConn).handleQuery
    /Users/yusp/work/tidb/server/conn.go:1904
github.com/pingcap/tidb/server.(*clientConn).dispatch
    /Users/yusp/work/tidb/server/conn.go:1359
github.com/pingcap/tidb/server.(*clientConn).Run
    /Users/yusp/work/tidb/server/conn.go:1104
github.com/pingcap/tidb/server.(*Server).onConn
    /Users/yusp/work/tidb/server/server.go:563
runtime.goexit
    /usr/local/go/src/runtime/asm_arm64.s:1172

4. What is your TiDB version? (Required)

It is happened in master 298ce91. It works in MySQL 8.0.

[winoros]

For general cases, the aggregate functions are calculated after the WHERE clause.

But this SQL uses the result of the aggregate functions to calculate the filter. It's against the general procedure.

e.g.

mysql> select count(*) as cnt from title where cnt > 1;
ERROR 1054 (42S22): Unknown column 'cnt' in 'where clause'

If we want to do filtering after the aggregation. The general way is using the HAVING clause.

select (SELECT GROUP_CONCAT(test_score) as grouped_set FROM student having find_in_set(student_name,grouped_set)) from student;

[winoros]

We label won't fix first.

[winoros]

For MySQL 8

mysql> select count(*) as cnt from a where cnt > 1;
ERROR 1054 (42S22): Unknown column 'cnt' in 'where clause'
mysql> select @cnt = count(*) from a where @cnt > 1;
+-----------------+
| @cnt = count(*) |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.00 sec)

This would increase our code complexity significantly.
So we reject this for now.