TiDB crashes when trying to run SHOW ERRORS statement

[SteveLeungYL]

Bug Report

1. Minimal reproduce step (Required)

1. Compile the TiDB using the source repository: link
2. Check out with the latest master version: 1159bc8.
3. Compile the TiDB server using go1.21.0. Use make in the repo root directory.
4. Launch TiDB server using command:

./tidb-server -P 8000 -socket /tmp/mysql_0.sql -path $(pwd)/db_data

5. Connect to the server using mysql client:

mysql -h 127.0.0.1 -P 8000 -u root --socket /tmp/mysql_0.sql

6. Input the PoC Query to trigger the crash of the server.

drop database if exists test;
create database test;
use test;
SHOW ERRORS WHERE TRUE = ALL ( SELECT TRUE GROUP BY 1 LIMIT 1 ) IS NULL IS NOT NULL;

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

The TiDB Server should continue running.

3. What did you see instead (Required)

The TiDB-server crashes and then returns error:

ERROR 1105 (HY000): runtime error: index out of range [0] with length 0

Here is a more detailed stack trace from the latest master version (1159bc8):

github.com/pingcap/tidb/server.(*clientConn).Run.func1
\t/home/tidb/go_projects/src/github.com/tidb/tidb/server/conn.go:970
runtime.gopanic
\t/usr/local/go/src/runtime/panic.go:914
github.com/pingcap/tidb/executor.(*Compiler).Compile.func1
\t/home/tidb/go_projects/src/github.com/tidb/tidb/executor/compiler.go:54
runtime.gopanic
\t/usr/local/go/src/runtime/panic.go:914
runtime.goPanicIndex
\t/usr/local/go/src/runtime/panic.go:114
github.com/pingcap/tidb/planner/core.(*LogicalAggregation).IsPartialModeAgg
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/logical_plans.go:1136
github.com/pingcap/tidb/planner/core.(*LogicalAggregation).BuildKeyInfo
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:47
github.com/pingcap/tidb/planner/core.buildKeyInfo
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:42
github.com/pingcap/tidb/planner/core.buildKeyInfo
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:36
github.com/pingcap/tidb/planner/core.buildKeyInfo
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:36
github.com/pingcap/tidb/planner/core.buildKeyInfo
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:36
github.com/pingcap/tidb/planner/core.buildKeyInfo
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:36
github.com/pingcap/tidb/planner/core.buildKeyInfo
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:36
github.com/pingcap/tidb/planner/core.buildKeyInfo
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:36
github.com/pingcap/tidb/planner/core.buildKeyInfo
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:36
github.com/pingcap/tidb/planner/core.buildKeyInfo
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:36
github.com/pingcap/tidb/planner/core.buildKeyInfo
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:36
github.com/pingcap/tidb/planner/core.(*buildKeySolver).optimize
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/rule_build_key_info.go:29
github.com/pingcap/tidb/planner/core.logicalOptimize
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/optimizer.go:1141
github.com/pingcap/tidb/planner/core.DoOptimizeAndLogicAsRet
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/optimizer.go:305
github.com/pingcap/tidb/planner/core.DoOptimize
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/core/optimizer.go:342
github.com/pingcap/tidb/planner.optimize
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/optimize.go:525
github.com/pingcap/tidb/planner.Optimize
\t/home/tidb/go_projects/src/github.com/tidb/tidb/planner/optimize.go:348
github.com/pingcap/tidb/executor.(*Compiler).Compile
\t/home/tidb/go_projects/src/github.com/tidb/tidb/executor/compiler.go:98
github.com/pingcap/tidb/session.(*session).ExecuteStmt
\t/home/tidb/go_projects/src/github.com/tidb/tidb/session/session.go:2211
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt
\t/home/tidb/go_projects/src/github.com/tidb/tidb/server/driver_tidb.go:292
github.com/pingcap/tidb/server.(*clientConn).handleStmt
\t/home/tidb/go_projects/src/github.com/tidb/tidb/server/conn.go:2011
github.com/pingcap/tidb/server.(*clientConn).handleQuery
\t/home/tidb/go_projects/src/github.com/tidb/tidb/server/conn.go:1802
github.com/pingcap/tidb/server.(*clientConn).dispatch
\t/home/tidb/go_projects/src/github.com/tidb/tidb/server/conn.go:1289
github.com/pingcap/tidb/server.(*clientConn).Run
\t/home/tidb/go_projects/src/github.com/tidb/tidb/server/conn.go:1068
github.com/pingcap/tidb/server.(*Server).onConn
\t/home/tidb/go_projects/src/github.com/tidb/tidb/server/server.go:700

4. What is your TiDB version? (Required)

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.4.0-alpha-406-g1159bc8d88
Edition: Community
Git Commit Hash: 1159bc8
Git Branch: master
UTC Build Time: 2023-09-14 01:10:11
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[fixdb]

/assign @elsa0520

[qw4990]

Degrade this issue to Minor since the syntax is not correct.
Here is MySQL's result:

mysql> SHOW ERRORS WHERE TRUE = ALL ( SELECT TRUE GROUP BY 1 LIMIT 1 ) IS NULL IS NOT NULL;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE TRUE = ALL ( SELECT TRUE GROUP BY 1 LIMIT 1 ) IS NULL IS NOT NULL' at line 1

[qw4990]

Increase its severity since some customer is waiting for this.

[elsa0520]

Same problem with query:

explain select * from t1 WHERE TRUE = ALL ( SELECT TRUE GROUP BY 1 LIMIT 1 ) IS NULL IS NOT NULL;

[elsa0520]

The mysql@8.0 behavior of is null is not null .
If is null and is not null exist in the where condition, it will always be true.
But Mysql@8.0 doesn't support the subquery + limit.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> create table t1 (k1 int, k2 int);
ERROR 1046 (3D000): No database selected
mysql> use test;
Database changed
mysql> create table t1 (k1 int, k2 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values (1,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1 where k2 is null is not null;
+------+------+
| k1   | k2   |
+------+------+
|    1 |    1 |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t1 where k2 is null ;
+------+------+
| k1   | k2   |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> select * from t1 WHERE TRUE = ALL ( SELECT TRUE GROUP BY 1 LIMIT 1 ) IS NULL IS NOT NULL;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

[elsa0520]

It currently appears that the result of xxx is null is not null should be ** always true. **

So combined with the previous subquery, the result of

select * from t1 WHERE TRUE = ALL ( SELECT TRUE GROUP BY 1 LIMIT 1 ) IS NULL IS NOT NULL;

should be equivalent to

select * from t1 WHERE TRUE = ALL ( SELECT TRUE GROUP BY 1 LIMIT 1 )

I will modify this issue based on this conclusion

[elsa0520]

The panic reason

1. Panic on the rule_build_key_info.go which is used to compute the uniquekey of operator schema for Functional Dependency

2. The panic reason is that aggfunction is ** empty ** of group by 1

The code try to judge whether agg node is a partial node by agg function, but the agg function of group by 1 is empty.

3. Normally, even if it is a query group by 1 without any original aggregate function, its agg function will not be empty. This is because we will add a ** firstrow function ** to the group by column.
   Just like the picture below

| │       └─HashAgg_27           | 1.00    | root      |               | group by:1, funcs:firstrow(1)->Column#10

4. The main reason why this agg node does not have this firstrow function is because agg is in subquery, and subquery + is null is not null form a where clause.

where subquery is null is not null

5. is null is not null will cause expression rewriting during the build selection in the plan builder phase.
   This expression was evaluated as true, causing it to affect the rewriting of the previous subquery. Eventually, the agg function in the subquery was not added correctly.