Instance crashed when SELECT list referring to nonaggregated column combined with GROUP BY and ORDER BY

[anitajjx]

Can be reproduced ?

Yes

Steps:

CREATE TABLE t1 (spID int,userID int,score smallint);
INSERT INTO t1 VALUES (1,1,1);
INSERT INTO t1 VALUES (2,2,2);
INSERT INTO t1 VALUES (2,1,4);
INSERT INTO t1 VALUES (3,3,3);
INSERT INTO t1 VALUES (1,1,5);
INSERT INTO t1 VALUES (4,6,10);
INSERT INTO t1 VALUES (5,11,99);

select spID , userID from t1 group by userID order by userID;

Expected behavior:

An error occurs when running the above select statement.

mysql> select spID , userID from t1 group by userID order by userID;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'qadb1.t1.spID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Actual behavior:

The instance crashed immediately after running the select statement. Please see the server log below.
Another observation is that the table (t1) was truncated after restarting the MO server and querying the table.

Server log:
2021/10/15 15:19:55.735795 +0800 INFO frontend/mysql_cmd_executor.go:944 query:select spID , userID from t1 group by userID orde...
2021/10/15 15:19:55.743055 +0800 INFO frontend/mysql_cmd_executor.go:787 time of Exec.Build : 4.864667ms
+++++++++
:0 π(userID,spID) -> δ([userID]) -> =>
:2 δ([userID]) -> =>
:1 δ([userID]) -> π(userID) -> =>
:1 τ([userID ASC]) -> π(spID,userID) -> sql output
+++++++++
2021/10/15 15:19:55.745428 +0800 INFO [email protected]/application.go:267 session connected {"session-id": 2, "addr": "127.0.0.1:54723"}
2021/10/15 15:19:55.753965 +0800 INFO frontend/mysql_cmd_executor.go:85 goid 876

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x28 pc=0x5215f8c]

goroutine 876 [running]:
matrixone/pkg/container/vector.(*Vector).Length(...)
/Users/anixjj/Workspace/matrixone/pkg/container/vector/vector.go:164
matrixone/pkg/frontend.getDataFromPipeline({0x56c1ac0, 0xc005482bd0}, 0xc004e8eaf0)
/Users/anixjj/Workspace/matrixone/pkg/frontend/mysql_cmd_executor.go:105 +0x28c
matrixone/pkg/sql/colexec/output.Call(0xc0001bda40, {0x54a6480, 0xc004b23500})
/Users/anixjj/Workspace/matrixone/pkg/sql/colexec/output/output.go:38 +0xbc
matrixone/pkg/vm.Run({0xc0042a6960, 0x3, 0x5ac1298}, 0x5ac1200)
/Users/anixjj/Workspace/matrixone/pkg/vm/vm.go:50 +0x94
matrixone/pkg/vm/pipeline.(*Pipeline).RunMerge(0xc005587f08, 0xc0001bda40)
/Users/anixjj/Workspace/matrixone/pkg/vm/pipeline/pipeline.go:103 +0x2ba
matrixone/pkg/sql/compile.(*Scope).MergeRun(0xc004b44080, {0x5a95f80, 0xc000409b48})
/Users/anixjj/Workspace/matrixone/pkg/sql/compile/scope.go:116 +0x485
matrixone/pkg/sql/compile.(*Exec).Run.func2(0x0)
/Users/anixjj/Workspace/matrixone/pkg/sql/compile/compile.go:187 +0x33
created by matrixone/pkg/sql/compile.(*Exec).Run
/Users/anixjj/Workspace/matrixone/pkg/sql/compile/compile.go:186 +0x265

Environment:

• Version or commit-id (e.g. v0.1.0 or 8b23a93):commit 690d11b5384209ff6fa1bd5c50975785bba5912
• Hardware parameters:
• OS type:
• Others:

Configuration file:

Additional context:

• Error message from client:
• Server log:
• Other information:

[nnsgmsone]

For sql_mode = only_full_group_by, it is illegal for a projection to contain non-group attributes and aggregate attributes. This crash does need to be handled though.

[iamlinjunhong]

Now behavior:
mysql> select spID , userID from t1 group by userID order by userID;
+------+--------+
| spID | userID |
+------+--------+
| 2 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 6 |
| 5 | 11 |
+------+--------+
5 rows in set (0.01 sec)

[JinHai-CN]

Well, current behavior is ok for now. If mysql compatible behavior is requested, we can reopen the issues.