tidb从V2.1.8 升级到V3.0.12之后，升级成功，可以窗口函数不能用？

🪐 TiDB 技术问题
1

问题： Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 91 Server version: 5.7.25-TiDB-v3.0.12 MySQL Community Server (Apache License 2.0)

Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SELECT RANK () OVER (ORDER BY PROFIT) FROM ATCENTER.B_MAJOR_BILL; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 22 near “(ORDER BY PROFIT) FROM ATCENTER.B_MAJOR_BILL” mysql>

另外一台环境直接安装的V3.0.12,可以查询 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 61820 Server version: 5.7.25-TiDB-v3.0.12 MySQL Community Server (Apache License 2.0)

Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SELECT RANK () OVER (ORDER BY PROFIT) FROM ATCENTER.B_MAJOR_BILL; Empty set (0.00 sec)

2

您好：

    1. 请检查tikv，tidb，pd的版本是否都升级到了3.0.12， 使用实例安装服务器目录的/bin/tikv-server ，tidb-server，pd-server -V命令查看，多谢
    2. 请再尝试执行一下，保证没有中文符合等.  麻烦再反馈下结果，多谢
    3.  麻烦反馈表结构,多谢.
3

./pd-server -V
Release Version: v3.0.12
Git Commit Hash: 84c952ab9f3e9dfc5e7fa77566f4354967630ef8
Git Branch: heads/refs/tags/v3.0.12
UTC Build Time: 2020-03-16 11:40:29

./tidb-server -V
Release Version: v3.0.12
Git Commit Hash: 8c4696b3f3408c61dd7454204ddd67c93501609a
Git Branch: heads/refs/tags/v3.0.12
UTC Build Time: 2020-03-16 09:56:22
GoVersion: go version go1.13 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

./tikv-server -V
TiKV
Release Version: 3.0.12
Git Commit Hash: a1c721b43c336be7c890c31229adc332ad6b3336
Git Commit Branch: heads/refs/tags/v3.0.12
UTC Build Time: 2020-03-16 11:38:53
Rust Version: rustc 1.37.0-nightly (0e4a56b4b 2019-06-13)

没有中文，因为表是空表，只是验证的，直接安装的版本可以，升级过来的版本报的这个错；

表结构如下：
| b_major_bill | CREATE TABLE b_major_bill (
eid bigint(20) DEFAULT NULL,
eitime datetime DEFAULT CURRENT_TIMESTAMP,
eutime datetime DEFAULT CURRENT_TIMESTAMP,
serverid bigint(20) DEFAULT NULL,
fundid bigint(20) DEFAULT NULL,
indexkey bigint(20) DEFAULT NULL,
timeslot varchar(64) DEFAULT NULL,
profitrate decimal(22,6) DEFAULT NULL,
profit decimal(20,4) DEFAULT NULL,
profitrankindex bigint(20) DEFAULT NULL,
profitrankpercent decimal(20,4) DEFAULT NULL,
remark varchar(200) DEFAULT NULL,
gainrate decimal(20,4) DEFAULT NULL,
maxgain decimal(20,4) DEFAULT NULL,
maxloss decimal(20,4) DEFAULT NULL,
maximum decimal(20,4) DEFAULT NULL,
totalcount bigint(20) DEFAULT NULL,
gaincount bigint(20) DEFAULT NULL,
losscount bigint(20) DEFAULT NULL,
avgposition decimal(20,4) DEFAULT NULL,
bizdate bigint(20) DEFAULT NULL,
volatility decimal(20,4) DEFAULT NULL,
sharperatio decimal(20,4) DEFAULT NULL,
tradecount bigint(20) DEFAULT NULL,
buycount bigint(20) DEFAULT NULL,
salecount bigint(20) DEFAULT NULL,
gainamt decimal(20,4) DEFAULT NULL,
lossamt decimal(20,4) DEFAULT NULL,
UNIQUE KEY pk_lg_b_marjor_bill (fundid,indexkey,timeslot,serverid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

4

麻烦在升级的集群上拿下 select *from information_schema.cluster_info 信息，感觉是 tidb 没升级成功，报错是报语法错误

5

我用ansible 升级的，提示是成功的；
没有这个系统视图
mysql> select * from information_schema.cluster_info;
ERROR 1146 (42S02): Table ‘information_schema.cluster_info’ doesn’t exist

6

查了官方文档，没有这个表 information_schema.cluster_info

7

@Hacker_tn1kaAuv 抱歉，我记错了，那麻烦查下 select tidb_version();

8

mysql> select tidb_version();
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.12
Git Commit Hash: 8c4696b3f3408c61dd7454204ddd67c93501609a
Git Branch: heads/refs/tags/v3.0.12
UTC Build Time: 2020-03-16 09:56:22
GoVersion: go version go1.13 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

9

附上详细的tidb 错误日志： [2020/04/20 10:44:42.799 +08:00] [ERROR] [misc.go:122] [“syntax error”] [error=“line 1 column 22 near “(ORDER BY PROFIT) FROM ATCENTER.B_MAJOR_BILL;” “] [errorVerbose=“line 1 column 22 near “(ORDER BY PROFIT) FROM ATCENTER.B_MAJOR_BILL;” \ngithub.com/pingcap/errors.AddStack\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174\ github.com/pingcap/errors.Trace\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15\ github.com/pingcap/parser.(*Parser).Parse\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20200303082314-9711ba384af6/yy_parser.go:146\ngithub.com/pingcap/tidb/session.(*session).ParseSQL\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/session/session.go:942\ngithub.com/pingcap/tidb/session.(*session).execute\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/session/session.go:1040\ngithub.com/pingcap/tidb/session.(*session).Execute\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/session/session.go:1022\ngithub.com/pingcap/tidb/server.(*TiDBContext).Execute\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/driver_tidb.go:246\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/conn.go:1203\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/conn.go:919\ngithub.com/pingcap/tidb/server.(*clientConn).Run\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/conn.go:671\ngithub.com/pingcap/tidb/server.(*Server).onConn\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/server.go:394\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1357”] [stack=“github.com/pingcap/tidb/util.SyntaxError\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/util/misc.go:122\ github.com/pingcap/tidb/session.(*session).execute\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/session/session.go:1046\ngithub.com/pingcap/tidb/session.(*session).Execute\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/session/session.go:1022\ngithub.com/pingcap/tidb/server.(*TiDBContext).Execute\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/driver_tidb.go:246\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/conn.go:1203\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/conn.go:919\ngithub.com/pingcap/tidb/server.(*clientConn).Run\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/conn.go:671\ngithub.com/pingcap/tidb/server.(*Server).onConn\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/server.go:394”] [2020/04/20 10:44:42.799 +08:00] [WARN] [conn.go:682] [“dispatch error”] [conn=7801] [connInfo=“id:7801, addr:172.30.68.55:62372 status:10, collation:latin1_swedish_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“SELECT RANK () OVER (ORDER BY PROFIT) FROM ATCENTER.B_MAJOR_BILL;”] [err=”[parser:1064]You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 22 near “(ORDER BY PROFIT) FROM ATCENTER.B_MAJOR_BILL;” \ngithub.com/pingcap/errors.AddStack\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174\ github.com/pingcap/parser/terror.(*Error).GenWithStackByArgs\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20200303082314-9711ba384af6/terror/terror.go:238\ngithub.com/pingcap/tidb/util.SyntaxError\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/util/misc.go:132\ github.com/pingcap/tidb/session.(*session).execute\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/session/session.go:1046\ngithub.com/pingcap/tidb/session.(*session).Execute\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/session/session.go:1022\ngithub.com/pingcap/tidb/server.(*TiDBContext).Execute\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/driver_tidb.go:246\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/conn.go:1203\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/conn.go:919\ngithub.com/pingcap/tidb/server.(*clientConn).Run\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/conn.go:671\ngithub.com/pingcap/tidb/server.(*Server).onConn\ \t/home/jenkins/agent/workspace/tidb_v3.0.12/go/src/github.com/pingcap/tidb/server/server.go:394\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1357”]

10

有可能是升级上来的时候，默认关闭了 WIndowFunction 功能？
试一下 set tidb_enable_window_function = 1

https://pingcap.com/docs-cn/stable/reference/sql/functions-and-operators/window-functions/#窗口函数

11

内部复现了，的确是 set tidb_enable_window_function = 1 后就可以用了。

12

好吧，这块官方升级文档最有有个章节标注下，哪些功能会有调整和关闭；

13

你好，

抱歉影响您的使用，我们会完善文档，为你提供更好的支持！

这边可以在官网提一个 issue 帮助我们一起完善文档，仅需 1min ：

image
image1366×625 84.2 KB