Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
XuHuaiyu opened this issue Jun 8, 2020 · 13 comments
Closed

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

XuHuaiyu opened this issue Jun 8, 2020 · 13 comments
Labels
severity/moderate sig/planner SIG: Planner type/bug The issue is confirmed as a bug.

Comments

@XuHuaiyu
Copy link
Contributor

XuHuaiyu commented Jun 8, 2020

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL,
  `col0` int(11) DEFAULT NULL
);
select * from (select col0,(select a.id from t a where a.id = max(b.id)) AS c from t b group by col0) s;

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

mysql> select * from (select col0,(select a.id from t a where a.id = max(b.id)) AS c from t b group by col0) s;
Empty set (0.00 sec)

3. What did you see instead (Required)

tidb> select * from (select col0,(select a.id from t a where a.id = max(b.id)) AS c from t b group by col0) s;
ERROR 1111 (HY000): Invalid use of group function

4. Affected version (Required)

Release Version: v4.0.0-beta.2-517-gaf7bbbe24
Edition: Community
Git Commit Hash: af7bbbe2412f9a0174338526daa01fe270500806
Git Branch: master
UTC Build Time: 2020-06-08 07:32:48
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

5. Root Cause Analysis

error log:

[2020/06/08 16:58:10.257 +08:00] [ERROR] [conn.go:730] ["command dispatched failed"] [conn=1] [connInfo="id:1, addr:127.0.0.1:61539 status:10, collation:utf8_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="select * from (select col0,(select a.id from t a where a.id = max(b.id)) AS c from t b group by col0) s"] [txn_mode=OPTIMISTIC] [err="[planner:1111]Invalid use of group function
github.com/pingcap/errors.AddStack
	/Users/xuhuaiyu/Development/GOPATH/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20190809092503-95897b64e011/errors.go:174
github.com/pingcap/errors.Trace
	/Users/xuhuaiyu/Development/GOPATH/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20190809092503-95897b64e011/juju_adaptor.go:15
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteExprNode
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:172
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteWithPreprocess
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:119
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewrite
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:87
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelection
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:798
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelect
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:2504
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildResultSetNode
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:301
github.com/pingcap/tidb/planner/core.(*expressionRewriter).buildSubquery
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:299
github.com/pingcap/tidb/planner/core.(*expressionRewriter).handleScalarSubquery
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:851
github.com/pingcap/tidb/planner/core.(*expressionRewriter).Enter
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:353
github.com/pingcap/parser/ast.(*SubqueryExpr).Accept
	/Users/xuhuaiyu/Development/GOPATH/pkg/mod/github.com/pingcap/parser@v0.0.0-20200522094936-3b720a0512a6/ast/expressions.go:383
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteExprNode
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:170
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteWithPreprocess
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:119
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildProjection
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:996
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelect
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:2532
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildResultSetNode
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:264
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildJoin
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:572
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildResultSetNode
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:259
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelect
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:2457
github.com/pingcap/tidb/planner/core.(*PlanBuilder).Build
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/planbuilder.go:494
github.com/pingcap/tidb/planner.optimize
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/optimize.go:150
github.com/pingcap/tidb/planner.Optimize
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/optimize.go:63
github.com/pingcap/tidb/executor.(*Compiler).Compile
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/executor/compiler.go:61
github.com/pingcap/tidb/session.(*session).ExecuteStmt
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/session/session.go:1144
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/driver_tidb.go:248
github.com/pingcap/tidb/server.(*clientConn).handleStmt
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/conn.go:1294
github.com/pingcap/tidb/server.(*clientConn).handleQuery
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/conn.go:1282
github.com/pingcap/tidb/server.(*clientConn).dispatch
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/conn.go:901
github.com/pingcap/tidb/server.(*clientConn).Run
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/conn.go:715
github.com/pingcap/tidb/server.(*Server).onConn
	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/server.go:415
runtime.goexit
	/Users/xuhuaiyu/.gvm/gos/go1.13/src/runtime/asm_amd64.s:1357"]
@XuHuaiyu XuHuaiyu added type/bug The issue is confirmed as a bug. sig/planner SIG: Planner labels Jun 8, 2020
@XuHuaiyu
Copy link
Contributor Author

XuHuaiyu commented Jun 8, 2020 •
edited
Loading

minor case:

select (select a.id from t a where a.id = max(b.id)) AS c from t b;
[2020/06/08 17:04:31.622 +08:00] [ERROR] [conn.go:730] ["command dispatched failed"] [conn=1] [connInfo="id:1, addr:127.0.0.1:61539 status:10, collation:utf8_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="select (select a.id from t a where a.id = max(b.id)) AS c from t b"] [txn_mode=OPTIMISTIC] [err="[planner:1111]Invalid use of group function
github.com/pingcap/errors.AddStack

	/Users/xuhuaiyu/Development/GOPATH/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20190809092503-95897b64e011/errors.go:174
github.com/pingcap/errors.Trace

	/Users/xuhuaiyu/Development/GOPATH/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20190809092503-95897b64e011/juju_adaptor.go:15
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteExprNode

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:172
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteWithPreprocess

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:119
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewrite

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:87
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelection

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:798
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelect

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:2504
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildResultSetNode

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:301
github.com/pingcap/tidb/planner/core.(*expressionRewriter).buildSubquery

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:299
github.com/pingcap/tidb/planner/core.(*expressionRewriter).handleScalarSubquery

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:851
github.com/pingcap/tidb/planner/core.(*expressionRewriter).Enter

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:353
github.com/pingcap/parser/ast.(*SubqueryExpr).Accept

	/Users/xuhuaiyu/Development/GOPATH/pkg/mod/github.com/pingcap/parser@v0.0.0-20200522094936-3b720a0512a6/ast/expressions.go:383
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteExprNode

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:170
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteWithPreprocess

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/expression_rewriter.go:119
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildProjection

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:996
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelect

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:2532
github.com/pingcap/tidb/planner/core.(*PlanBuilder).Build

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/planbuilder.go:494
github.com/pingcap/tidb/planner.optimize

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/optimize.go:150
github.com/pingcap/tidb/planner.Optimize

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/optimize.go:63
github.com/pingcap/tidb/executor.(*Compiler).Compile

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/executor/compiler.go:61
github.com/pingcap/tidb/session.(*session).ExecuteStmt

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/session/session.go:1144
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/driver_tidb.go:248
github.com/pingcap/tidb/server.(*clientConn).handleStmt

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/conn.go:1294
github.com/pingcap/tidb/server.(*clientConn).handleQuery

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/conn.go:1282
github.com/pingcap/tidb/server.(*clientConn).dispatch

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/conn.go:901
github.com/pingcap/tidb/server.(*clientConn).Run

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/conn.go:715
github.com/pingcap/tidb/server.(*Server).onConn

	/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/server.go:415
runtime.goexit

	/Users/xuhuaiyu/.gvm/gos/go1.13/src/runtime/asm_amd64.s:1357"]

@zhangysh1995
Copy link

I doubt whether it is a bug in TiDB. If I understand correctly, this query violated full group by and it shouldn't be executed.

@XuHuaiyu
Copy link
Contributor Author

@zhangysh1995 No, it has nothing to do with full group by.

You can check it in MySQL.

@zhangysh1995
Copy link

I mean it could be a bug in MySQL. @XuHuaiyu

@zhangysh1995
Copy link

This is the doc for handling group by in MySQL. AFAIK, this case is not considered as special.

@bin272
Copy link

bin272 commented Jun 29, 2020

change the ”where“ to "having" it can solve this problem

@XuHuaiyu
Copy link
Contributor Author

XuHuaiyu commented Jul 6, 2020

related issues:
#18350
#18384

@XuHuaiyu XuHuaiyu mentioned this issue Jul 6, 2020
Merged
@qw4990
Copy link
Contributor

qw4990 commented Jul 20, 2020

Can this issue be closed now? @XuHuaiyu

@qw4990
Copy link
Contributor

qw4990 commented Jul 24, 2020

After testing, this problem still exists.

@qw4990 qw4990 self-assigned this Jul 24, 2020
@ichn-hu ichn-hu mentioned this issue Nov 3, 2020
Closed
@qw4990 qw4990 assigned XuHuaiyu and unassigned qw4990 Nov 9, 2020
@yudongusa
Copy link

@XuHuaiyu is there any update of this issue?

@XuHuaiyu XuHuaiyu removed their assignment Nov 30, 2021
@winoros
Copy link
Member

winoros commented Apr 20, 2022

Fixed in master. Not sure when is fixed

@winoros winoros closed this as completed Apr 20, 2022
@jackysp
Copy link
Member

jackysp commented Oct 21, 2022 •
edited
Loading

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

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.

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

@jackysp jackysp reopened this Oct 21, 2022
@jackysp
Copy link
Member

jackysp commented Oct 21, 2022

create a new issue #38600, so close this one

@jackysp jackysp closed this as completed Oct 21, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
severity/moderate sig/planner SIG: Planner type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
8 participants
@jackysp @zz-jason @qw4990 @winoros @zhangysh1995 @XuHuaiyu @bin272 @yudongusa