window function rank() sometimes lead to tidb-server panic

[chrissata]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.
   1) prepare data
   same mock data set like issue: window function row_number somtimes return wrong row number  #11612
   2) do query

SELECT p.id, p.title, p.seq, p.m_id, p.val, rank() over(PARTITION BY p.m_id ORDER BY p.seqDESC) AS rank_num FROM testtable p WHERE p.deleted = 0 AND p.online = 1 AND p.m_id IN (1000,1001,1002,1003,11004);

2. What did you expect to see?
   rank result works well.

3. What did you see instead?

ERROR 2013 (HY000): Lost connection to MySQL server during query error returned.

tidb-server log:

[2019/08/05 15:12:05.408 +08:00] [ERROR] [conn.go:599] ["connection running loop panic"] [conn=6661] [lastCmd="SELECT p.id, p.title, p.seq, p.m_id, p.val, rank() over(PARTITION BY p.m_id ORDER BY p.seq DESC) AS rank_num FROM testtable p WHERE p.deleted = 0 AND p.online = 1 AND p.m_id IN (1000,1001,1002,1003,1004)"] [err=""index out of range""] [stack="goroutine 62192060 [running]:\ngit.luolix.top/pingcap/tidb/server.(*clientConn).Run.func1(0x2250900, 0xc00af21a70, 0xc00e38b520)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:597 +0xee\npanic(0x1d85fe0, 0x3198a70)\n\t/usr/local/go/src/runtime/panic.go:522 +0x1b5\ngit.luolix.top/pingcap/tidb/server.(*clientConn).writeResultset.func1(0x0, 0x22626c0, 0xc00d720190, 0xc001553778, 0x2250900, 0xc00af21a70, 0xc00e38b520)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:1255 +0x59c\npanic(0x1d85fe0, 0x3198a70)\n\t/usr/local/go/src/runtime/panic.go:522 +0x1b5\ngit.luolix.top/pingcap/tidb/util/chunk.(*column).isNull(...)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/util/chunk/column.go:71\ngit.luolix.top/pingcap/tidb/util/chunk.Row.IsNull(...)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/util/chunk/row.go:223\ngit.luolix.top/pingcap/tidb/util/chunk.cmpInt64(0xc01a693650, 0x1f, 0x4, 0xc00ec882d0, 0x0, 0x4, 0xc02567e414)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/util/chunk/compare.go:69 +0x171\ngit.luolix.top/pingcap/tidb/executor/aggfuncs.(*rowComparer).compareRows(0xc001b67468, 0xc01a693650, 0x1f, 0xc00ec882d0, 0x0, 0xc02567e400)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_rank.go:94 +0xd9\ngit.luolix.top/pingcap/tidb/executor/aggfuncs.(*rank).AppendFinalResult2Chunk(0xc001b67440, 0x2289c80, 0xc01503e690, 0xc01a693530, 0xc01a6935f0, 0x15, 0x400)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_rank.go:59 +0x99\ngit.luolix.top/pingcap/tidb/executor.(*aggWindowProcessor).appendResult2Chunk(0xc01a693560, 0x2289c80, 0xc01503e690, 0xc022e96400, 0x1, 0x20, 0xc01a6935f0, 0x2, 0x1e824e0, 0x0, ...)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/window.go:213 +0xca\ngit.luolix.top/pingcap/tidb/executor.(*WindowExec).appendResult2Chunk(0xc0124222d0, 0xc01a6935f0, 0x0, 0x0)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/window.go:155 +0xc1\ngit.luolix.top/pingcap/tidb/executor.(*WindowExec).Next(0xc0124222d0, 0x2250900, 0xc00af21a70, 0xc01a6935f0, 0x0, 0x0)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/window.go:66 +0x1db\ngit.luolix.top/pingcap/tidb/executor.Next(0x2250900, 0xc00af21a70, 0x2257880, 0xc0124222d0, 0xc01a6935f0, 0x1d, 0x20000015535c0)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/executor.go:190 +0xbd\ngit.luolix.top/pingcap/tidb/executor.(*recordSet).Next(0xc00d720140, 0x2250900, 0xc00af21a70, 0xc01a6935f0, 0x0, 0x0)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/adapter.go:109 +0xb0\ngit.luolix.top/pingcap/tidb/server.(*tidbResultSet).Next(0xc00d720190, 0x2250900, 0xc00af21a70, 0xc01a6935f0, 0x0, 0x0)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/driver_tidb.go:365 +0x51\ngit.luolix.top/pingcap/tidb/server.(*clientConn).writeChunks(0xc00e38b520, 0x2250900, 0xc00af21a70, 0x22626c0, 0xc00d720190, 0xc000003700, 0x2250900, 0xc00af21a70)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:1302 +0x319\ngit.luolix.top/pingcap/tidb/server.(*clientConn).writeResultset(0xc00e38b520,0x2250900, 0xc00af21a70, 0x22626c0, 0xc00d720190, 0xc000000300, 0x0, 0x0, 0x0)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:1268 +0x1a1\ngit.luolix.top/pingcap/tidb/server.(*clientConn).handleQuery(0xc00e38b520, 0x2250900, 0xc00af21a70, 0xc00a46f001, 0xf4, 0x0, 0x0)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:1185 +0x212\ngit.luolix.top/pingcap/tidb/server.(*clientConn).dispatch(0xc00e38b520, 0x2250900, 0xc00af21a70, 0xc00a46f001, 0xf5,0xf5, 0x0, 0x0)\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:895 +0x5c6\ngit.luolix.top/pingcap/tidb/server.(*clientConn).Run(0xc00e38b520, 0x2250900, 0xc00af21a70)\n\t/home/jenkins/workspa"] [stack="github.com/pingcap/tidb/server.(*clientConn).Run.func1\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:599\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:522\ngit.luolix.top/pingcap/tidb/server.(*clientConn).writeResultset.func1\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:1255\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:522\nruntime.panicindex\n\t/usr/local/go/src/runtime/panic.go:44\ngit.luolix.top/pingcap/tidb/util/chunk.(*column).isNull\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/util/chunk/column.go:71\ngit.luolix.top/pingcap/tidb/util/chunk.Row.IsNull\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/util/chunk/row.go:223\ngit.luolix.top/pingcap/tidb/util/chunk.cmpInt64\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/util/chunk/compare.go:69\ngit.luolix.top/pingcap/tidb/executor/aggfuncs.(*rowComparer).compareRows\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_rank.go:94\ngit.luolix.top/pingcap/tidb/executor/aggfuncs.(*rank).AppendFinalResult2Chunk\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_rank.go:59\ngit.luolix.top/pingcap/tidb/executor.(*aggWindowProcessor).appendResult2Chunk\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/window.go:213\ngit.luolix.top/pingcap/tidb/executor.(*WindowExec).appendResult2Chunk\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/window.go:155\ngit.luolix.top/pingcap/tidb/executor.(*WindowExec).Next\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/window.go:66\ngit.luolix.top/pingcap/tidb/executor.Next\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/executor.go:190\ngit.luolix.top/pingcap/tidb/executor.(*recordSet).Next\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/adapter.go:109\ngit.luolix.top/pingcap/tidb/server.(*tidbResultSet).Next\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/driver_tidb.go:365\ngit.luolix.top/pingcap/tidb/server.(*clientConn).writeChunks\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:1302\ngit.luolix.top/pingcap/tidb/server.(*clientConn).writeResultset\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:1268\ngit.luolix.top/pingcap/tidb/server.(*clientConn).handleQuery\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:1185\ngit.luolix.top/pingcap/tidb/server.(*clientConn).dispatch\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:895\ngit.luolix.top/pingcap/tidb/server.(*clientConn).Run\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/conn.go:650\ngit.luolix.top/pingcap/tidb/server.(*Server).onConn\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/server/server.go:440"]

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

mysql> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v3.0.1 |
+--------------------+
1 row in set (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.01 sec)

[zz-jason]

@SunRunAway @lzmhhh123 @lamxTyler PTAL

[SunRunAway]

Hi, @chrissata
I tested on both v3.0.0 and v3.0.1 and cannot reproduce, the SQL you provided runs normally.

mysql> SELECT p.id, p.title, p.seq, p.m_id, p.val, rank() over(PARTITION BY p.m_id ORDER BY p.seq DESC) AS rank_num FROM testtable p WHERE p.deleted = 0 AND p.online = 1 AND p.m_id IN (1000,1001,1002,1003,11004);
+-------+---------+------+------+------+----------+
| id    | title   | seq  | m_id | val  | rank_num |
+-------+---------+------+------+------+----------+
| 31037 | abcde   |  499 | 1000 | 309  |        1 |
| 31038 | abcdef  |  499 | 1000 | 309  |        1 |
| 31039 | abcdefg |  499 | 1000 | 319  |        1 |
| 31035 | abcd    |  391 | 1000 | 399  |        4 |
| 31034 | abc     |  390 | 1000 | 399  |        5 |
| 31036 | abcde   |  377 | 1000 | 309  |        6 |
|  1000 | last    |  330 | 1001 | 330  |        1 |
|  1001 |         |  329 | 1001 | 329  |        2 |
|  1002 |         |  328 | 1001 | 328  |        3 |
|  1003 |         |  327 | 1001 | 328  |        4 |
|  1004 |         |  326 | 1001 | 328  |        5 |
|  1005 |         |  325 | 1001 | 328  |        6 |
|  1006 | x       |  324 | 1001 | 328  |        7 |
|  1007 | x       |  323 | 1001 | 328  |        8 |
|  1008 | x22     |  322 | 1001 | 328  |        9 |
|  1009 | x21     |  321 | 1001 | 328  |       10 |
|  1010 | x20     |  320 | 1001 | 328  |       11 |
|  1011 | x19     |  319 | 1001 | 328  |       12 |
|  1012 | x18     |  318 | 1001 | 328  |       13 |
|  1013 | x17     |  317 | 1001 | 328  |       14 |
|  1014 | x16     |  316 | 1001 | 328  |       15 |
|  1015 | x15     |  315 | 1001 | 328  |       16 |
|  1016 | x14     |  314 | 1001 | 328  |       17 |
|  1017 | x13     |  313 | 1001 | 328  |       18 |
|  1018 | x12     |  312 | 1001 | 328  |       19 |
|  1019 | x11     |  311 | 1001 | 328  |       20 |
|  1020 | x10     |  310 | 1001 | 328  |       21 |
|  1021 | x09     |  309 | 1001 | 328  |       22 |
|  1022 | x08     |  308 | 1001 | 328  |       23 |
|  1023 | x08     |  307 | 1001 | 328  |       24 |
|  1024 | x07     |  307 | 1001 | 328  |       24 |
|  1025 | x06     |  306 | 1001 | 328  |       26 |
|  1026 | x04     |  305 | 1001 | 328  |       27 |
|  1027 | x044    |  304 | 1001 | 328  |       28 |
|  1028 | x03     |  303 | 1001 | 328  |       29 |
|  1029 | x02     |  302 | 1001 | 328  |       30 |
|  1030 | x01     |  301 | 1001 | 328  |       31 |
|  1031 | x00     |  300 | 1001 | 328  |       32 |
|  1032 | x299    |  299 | 1001 | 328  |       33 |
|  1033 | x298    |  298 | 1001 | 328  |       34 |
| 31040 | aa      |  298 | 1003 | 328  |        1 |
| 31041 | aa      |  298 | 1003 | 328  |        1 |
| 31042 | aa      |  298 | 1003 | 328  |        1 |
+-------+---------+------+------+------+----------+
43 rows in set (0.01 sec)

[SunRunAway]

Could you provide your TiDB git hash using tidb-server -V?

[chrissata]

[root@titb-server bin]# ./tidb-server -V
Release Version: v3.0.1
Git Commit Hash: 9e4e8da
Git Branch: HEAD
UTC Build Time: 2019-07-16 01:03:40
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

Our environment used to be v2.1.8 and I upgraded it to 3.0.1 version days ago and this error is found on current env. I was wondering if this error related to the upgrade . Since we tested 3 window functions and they all worked abnormally(first_value , rank and row_number).

[zz-jason]

a minimal reproduce step:

drop table if exists t;
create table t(a bigint, b bigint);
insert into t values(1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10), (1, 11), (1, 12), (1, 13), (1, 14), (1, 15), (1, 16), (1, 17);
insert into t select 2, b from t;
insert into t select 3, b from t;

set @@tidb_max_chunk_size=32;
select a, b, rank() over (partition by a order by b) from t;

[zz-jason]

notice that, if we only insert 34 rows into table t, we can got an incorrect result which only contains 32 rows rather than panic:

drop table if exists t;
create table t(a bigint, b bigint);
insert into t values(1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10), (1, 11), (1, 12), (1, 13), (1, 14), (1, 15), (1, 16), (1, 17);
insert into t select 2, b from t;

set @@tidb_max_chunk_size=32;
select a, b, rank() over (partition by a order by b) from t; -- only 32 rows are returned