# [Know Issue] 使用用户变量 SQL 执行报错 ERROR 1111 (HY000): Invalid use of group function

## Key Words

ERROR 1111 (HY000): Invalid use of group function, query, user variable, GROUP_CONCAT

## Description

TiDB 执行带用户变量的 SQL 时报错 Invalid use of group function，同样的 SQL 8.0 可以成功：

```sql
mysql> select (SELECT @ids:=GROUP_CONCAT(test_score) FROM student where find_in_set(student_name,@ids)) from student;
ERROR 1111 (HY000): Invalid use of group function
```

Stack 信息：

```log
[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

```

## Root Cause

对于一般 SQL 而言聚合函数的计算会发生在 where 条件过滤完数据后，而报错的 SQL 使用聚合函数的结果去带入到 where 子句中过滤数据。TiDB 暂时不支持，并且在未来一段时间也不会支持，具体可以通过这个 [Issue](https://github.com/pingcap/tidb/issues/38600) 来跟进。

## Diagnostic Steps

N/A

## Resolution

N/A

## Workaround

使用 Having 子句对 SQL 进行等价改写: 
select (SELECT GROUP_CONCAT(test_score) as ids FROM student having find_in_set(student_name,ids)) from student;

## Affected Versions

ALL