using window function "first_value" get "index out of range" runtime error

[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.

SELECT first_value(p.id) over(PARTITION BY p.m_id ORDER BY p.seq DESC) AS first_id FROM testtable p WHERE p.deleted = 0 AND p.online = 1 AND p.m_id IN (1977409, 1861420);

2. What did you expect to see?

return correct first_id column value (first id in each group)

3. What did you see instead?

for some given m_id set, the sql run into an error:
ERROR 1105 (HY000): runtime error: index out of range

tidb-server log:
[err="runtime error: index out of range
github.com/pingcap/tidb/executor.recoveryHashAgg
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/aggregate.go:322
github.com/pingcap/tidb/executor.(*HashAggExec).fetchChildData.func1
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/aggregate.go:542
runtime.gopanic
/usr/local/go/src/runtime/panic.go:522
runtime.panicindex
/usr/local/go/src/runtime/panic.go:44
github.com/pingcap/tidb/util/chunk.(*column).isNull
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/util/chunk/column.go:71
github.com/pingcap/tidb/util/chunk.Row.IsNull
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/util/chunk/row.go:223
github.com/pingcap/tidb/expression.(*Column).EvalInt
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/expression/column.go:221
github.com/pingcap/tidb/executor/aggfuncs.(*value4Int).evaluateRow
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_value.go:41
github.com/pingcap/tidb/executor/aggfuncs.(*firstValue).UpdatePartialResult
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_value.go:245
github.com/pingcap/tidb/executor.(*rangeFrameWindowProcessor).appendResult2Chunk
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/window.go:410
github.com/pingcap/tidb/executor.(*WindowExec).appendResult2Chunk
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/window.go:155
github.com/pingcap/tidb/executor.(*WindowExec).Next
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/window.go:66
github.com/pingcap/tidb/executor.Next
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/executor.go:190
github.com/pingcap/tidb/executor.(*HashAggExec).fetchChildData
/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/executor/aggregate.go:558
runtime.goexit
/usr/local/go/src/runtime/asm_amd64.s:1337"]

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)

[zimulala]

@chrissata
Thanks for your feedback! We will handle it.

[SunRunAway]

Hi, @chrissata
Could you provide the scheme of testtable and the table data to reproduce, and the git commit hash of tidb (tidb-server -V)?

[chrissata]

Hi, @chrissata
Could you provide the scheme of testtable and the table data to reproduce, and the git commit hash of tidb (tidb-server -V)?

Hi,

The git commit is :

[root@tidb tidb]# ./deploy/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

Sorry I cound't provide exact data to reproduce this error since the data is kind of sensitive. I try to reproduce it using fake data but it works well. This morning I've talked to your colleagues and I guess this might be caused by unkown errors during the upgrading from 2.1.8 to 3.0.1 since after upgrading, several window functions all works abnormally .I tested the upgrading on another 2.1.8 cluster, I even found that it couldm't parse the window function even the process and version command showed that it's 3.0.1 already. I will try to do a query on a new-deployed 3.0.1 cluster and see if this error could reproduce.

Thank u.

[SunRunAway]

I tested the upgrading on another 2.1.8 cluster, I even found that it couldm't parse the window function even the process and version command showed that it's 3.0.1 already.

Hi, sorry for the inconvenience, you may call set @@global.tidb_enable_window_function = 1; and reconnect all the sessions to use window functions when upgrading from 2.1.X

[SunRunAway]

Hi, @chrissata I've reproduced this problem and I'm investigating.

[chrissata]

orry for the inconvenience, you may cal

Hi,
I checked the value of tidb_enable_window_function and it's already 1. And the behavior was the same even after I relogin using the mysql cmdline. I already destroyed this tidb cluster and redeployed the v3.0.1. It works well now. So I believed that all these issues are all caused by upgrading.
But since you've already reproduced this problem and maybe there's some bug there.
Thanks for your support !