"Lost connection to MySQL server during query" after UPDATE statement

[JZuming]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Setup the environment:

tiup playground --db.binpath /path/to/latest/tidb-server &
mysql -h "127.0.0.1" -u root -P 4000 -D testdb < mysql_bk.sql

mysql_bk.sql:
mysql_bk.txt

Testcase

mysql -h "127.0.0.1" -u root -P 4000 -D testdb

mysql> update t__ti1_d set
          wkey = 37
        where (case when 0 <> 0 then abs(
                  case when t__ti1_d.wkey > (
                        select
                            t__ti1_d.c_azzk8c as c0
                          from
                            t_yexe_d as ref_0
                          where 10 >= (select count(c_vqpj9c) from t_yexe_d)
                          window w_80pxn as ( partition by t__ti1_d.pkey order by ref_0.c_px23g desc)
                          order by c0 desc
                  ) then 1 else 20 end
               ) else 1 end * 53) > 1; 

mysql> select * from t__ti1_d;

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

The connection will not be lost.

3. What did you see instead (Required)

UPDATE statement

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

SELECT statement

ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    136645
Current database: testdb

+------+--------+---------+----------+--------------------+----------+----------+----------+----------+
| wkey | pkey   | c_l3pcj | c_ksp1hc | c_5vhjk            | c_azzk8c | c_g0jc6d | c_jqg9yd | c__qdjic |
+------+--------+---------+----------+--------------------+----------+----------+----------+----------+
|  107 | 130000 |    NULL | _dry8b   | 19.798874920631782 |     NULL |     NULL |     NULL |     NULL |
+------+--------+---------+----------+--------------------+----------+----------+----------+----------+
1 row in set (0.01 sec)

4. What is your TiDB version? (Required)

Release Version: v6.1.0-alpha-173-g32b9c1477
Edition: Community
Git Commit Hash: 32b9c14779c2a7dd73003667d81bb42f67a33385
Git Branch: master
UTC Build Time: 2022-04-11 17:53:15
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[bb7133]

The stack is as followings:

  [running]:
  github.com/pingcap/tidb/server.(*clientConn).Run.func1()
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/server/conn.go:1050 +0x8f
  panic({0x369ef20, 0xc010738888})
      /home/bb7133/Softwares/go/src/runtime/panic.go:838 +0x207
  github.com/pingcap/tidb/executor.(*ExecStmt).Exec.func1()
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/adapter.go:371 +0x3b0
  panic({0x369ef20, 0xc010738888})
      /home/bb7133/Softwares/go/src/runtime/panic.go:838 +0x207
  github.com/pingcap/tidb/util/chunk.(*Chunk).Column(...)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/util/chunk/chunk.go:589
  github.com/pingcap/tidb/expression.evalOneVec({0x3e644b8, 0xc000ef2480}, {0x3e63f48, 0xc0108d8c60}, 0xc010a01c20, 0xc010a01360, 0x0)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/expression/chunk_executor.go:119 +0x14d8
  github.com/pingcap/tidb/expression.(*defaultEvaluator).run(0xc010727ec0, {0x3e644b8, 0xc000ef2480}, 0xc010a01c20, 0x0?)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/expression/evaluator.go:52 +0x1cf
  github.com/pingcap/tidb/expression.(*EvaluatorSuite).Run(0xc01009b010, {0x3e644b8, 0xc000ef2480}, 0xc01073ca80?, 0xc010a01c20?)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/expression/evaluator.go:124 +0x4c
  github.com/pingcap/tidb/executor.(*ProjectionExec).unParallelExecute(0xc010a03680, {0x3e102e0?, 0xc01074b170?}, 0xc010a01360)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/projection.go:201 +0x148
  github.com/pingcap/tidb/executor.(*ProjectionExec).Next(0xc010a03680, {0x3e102e0, 0xc01074b170}, 0xc010a08a80?)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/projection.go:179 +0x5a
  github.com/pingcap/tidb/executor.Next({0x3e102e0, 0xc01074b170}, {0x3e12b48, 0xc010a03680}, 0xc010a01360)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/executor.go:306 +0x4e8
  github.com/pingcap/tidb/executor.(*MaxOneRowExec).Next(0xc010745810, {0x3e102e0, 0xc01074b170}, 0xc010a01360)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/executor.go:1522 +0xaa
  github.com/pingcap/tidb/executor.Next({0x3e102e0, 0xc01074b170}, {0x3e128c8, 0xc010745810}, 0xc010a01360)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/executor.go:306 +0x4e8
  github.com/pingcap/tidb/executor.(*NestedLoopApplyExec).fetchAllInners(0xc0058e08c0, {0x3e102e0, 0xc01074b170})
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/join.go:980 +0x32f
  github.com/pingcap/tidb/executor.(*NestedLoopApplyExec).Next(0xc0058e08c0, {0x3e102e0, 0xc01074b170}, 0xc010a01720)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/join.go:1033 +0x5ae
  github.com/pingcap/tidb/executor.Next({0x3e102e0, 0xc01074b170}, {0x3e12988, 0xc0058e08c0}, 0xc010a01720)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/executor.go:306 +0x4e8
  github.com/pingcap/tidb/executor.(*UpdateExec).updateRows(0xc01073cc00, {0x3e102e0, 0xc01074b170})
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/update.go:257 +0x255
  github.com/pingcap/tidb/executor.(*UpdateExec).Next(0xc01073cc00, {0x3e102e0, 0xc01074ade0}, 0x7fbfdb0e3108?)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/update.go:225 +0xcf
  github.com/pingcap/tidb/executor.Next({0x3e102e0, 0xc01074ade0}, {0x3e13348, 0xc01073cc00}, 0xc010a01630)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/executor.go:306 +0x4e8
  github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor(0xc0100f1380, {0x3e102e0, 0xc01074ade0}, {0x3e13348?, 0xc01073cc00})
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/adapter.go:661 +0x59f
  github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay(0xc0100f1380, {0x3e102e0, 0xc01074ade0}, {0x3e13348?, 0xc01073cc00?}, 0x0)
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/adapter.go:516 +0x1f9
  github.com/pingcap/tidb/executor.(*ExecStmt).Exec(0xc0100f1380, {0x3e102e0, 0xc01074ade0})
      /home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/"]

[solotzg]

Analyze Doc

• The direct cause of panic is that tidb generate wrong execution plan and execute MaxOneRow for empty output. The Projection task is from data of ref_0(t_yexe_d) to t__ti1_d.c_azzk8c.

  tidb/expression/chunk_executor.go

  Lines 117 to 119 in 1bf64c6

  	func evalOneVec(ctx sessionctx.Context, expr Expression, input *chunk.Chunk, output *chunk.Chunk, colIdx int) error {
  	ft := expr.GetType()
  	result := output.Column(colIdx)

  the output Chunk is empty.

explain update t__ti1_d set
          wkey = 37
        where (case when 0 <> 0 then abs(
                  case when t__ti1_d.wkey > (
                        select
                            t__ti1_d.c_azzk8c as c0
                          from
                            t_yexe_d as ref_0
                          where 10 >= (select count(c_vqpj9c) from t_yexe_d)
                          window w_80pxn as ( partition by t__ti1_d.pkey order by ref_0.c_px23g desc)
                          order by c0 desc
                  ) then 1 else 20 end
               ) else 1 end * 53) > 1;

+--------------------------------+---------+-----------+----------------+--------------------------------+
| id                             | estRows | task      | access object  | operator info                  |
+--------------------------------+---------+-----------+----------------+--------------------------------+
| Update_33                      | N/A     | root      |                | N/A                            |
| └─Apply_36                     | 1.00    | root      |                | CARTESIAN left outer join      |
|   ├─TableReader_38(Build)      | 1.00    | root      |                | data:TableFullScan_37          |
|   │ └─TableFullScan_37         | 1.00    | cop[tikv] | table:t__ti1_d | keep order:false, stats:pseudo |
|   └─MaxOneRow_39(Probe)        | 1.00    | root      |                |                                |
|     └─Projection_46            | 2.00    | root      |                | test.t__ti1_d.c_azzk8c         |
|       └─TableReader_48         | 2.00    | root      |                | data:TableFullScan_47          |
|         └─TableFullScan_47     | 2.00    | cop[tikv] | table:ref_0    | keep order:false, stats:pseudo |
+--------------------------------+---------+-----------+----------------+--------------------------------+

• mysql rewriet the sql to

`update `test`.`t__ti1_d` set `test`.`t__ti1_d`.`wkey` = 37 where (((case when (0 <> 0) then abs((case when (`test`.`t__ti1_d`.`wkey` > (/* select#2 */ select `test`.`t__ti1_d`.`c_azzk8c` AS `c0` from `test`.`t_yexe_d` `ref_0` where true)) then 1 else 20 end)) else 1 end) * 53) > 1)`

For mysql, the executor logic is case when ... then .... If the condition is false, the result of then ... will be ignored.

mysql> explain update t__ti1_d set
    ->           wkey = 37
    ->         where (case when 0 <> 0 then abs(
    ->                   case when t__ti1_d.wkey > (
    ->                         select
    ->                             t__ti1_d.c_azzk8c as c0
    ->                           from
    ->                             t_yexe_d as ref_0
    ->                           where 10 >= (select count(c_vqpj9c) from t_yexe_d)
    ->                           window w_80pxn as ( partition by t__ti1_d.pkey order by ref_0.c_px23g desc)
    ->                           order by c0 desc
    ->                   ) then 1 else 20 end
    ->                ) else 1 end * 53) > 1; 
+----+--------------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE             | t__ti1_d | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | ref_0    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    5 |   100.00 | NULL        |
|  3 | SUBQUERY           | t_yexe_d | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    5 |   100.00 | NULL        |
+----+--------------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
3 rows in set, 3 warnings (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                    |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1276 | Field or reference 'test.t__ti1_d.c_azzk8c' of SELECT #2 was resolved in SELECT #1                                                                                                                                                                                                         |
| Note  | 1276 | Field or reference 'test.t__ti1_d.pkey' of SELECT #2 was resolved in SELECT #1                                                                                                                                                                                                             |
| Note  | 1003 | update `test`.`t__ti1_d` set `test`.`t__ti1_d`.`wkey` = 37 where (((case when (0 <> 0) then abs((case when (`test`.`t__ti1_d`.`wkey` > (/* select#2 */ select `test`.`t__ti1_d`.`c_azzk8c` AS `c0` from `test`.`t_yexe_d` `ref_0` where true)) then 1 else 20 end)) else 1 end) * 53) > 1) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

• However the behavior of tidb is different. tidb may execute sub tasks and raise error.

[zanmato1984]

I'd like to change the sig to planner as this relates to a wrong plan problem as mentioned in the previous comment. @solotzg Please also file a new issue to trace the execution problem (differ from MySQL), probably at moderate severity.