5.7.25-TiDB-v4.0.12版本如何开启PARTITION BY

#1

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】
5.7.25-TiDB-v4.0.12

【概述】 场景 + 问题概述
使用PARTITION BY会报错:
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 3 column 23 near "( PARTITION BY
语句：SELECT * FROM (SELECT *,ROW_NUMBER() over(PARTITION BY user_id order BY leave_end_time DESC) r FROM base_user_leave )t WHERE r = 1 AND leave_status in (0,1) and IS_DELETED = ‘0’

但是在另一个5.7.25-TiDB-v4.0.8版本上不会报错

如何设置支持partition by语法

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

#2

可以给一下表结构吗？我复现一下。

#3

CREATE TABLE base_user_leave (
id bigint(20) NOT NULL,
user_id bigint(20) NOT NULL COMMENT ‘用户id’,
leave_status varchar(2) COLLATE utf8_general_ci NOT NULL DEFAULT ‘0’ COMMENT ‘请假状态：0-待生效,1-请假中, 2-请假中止, 3-请假结束’,
leave_start_time datetime NOT NULL COMMENT ‘请假开始日期’,
leave_end_time datetime NOT NULL COMMENT ‘请假中止、结束日期’,
remark varchar(255) COLLATE utf8_general_ci DEFAULT NULL,
is_deleted varchar(20) COLLATE utf8_general_ci NOT NULL DEFAULT ‘0’ COMMENT ‘是否删除0-正常,1-为删除’,
create_user_id bigint(20) DEFAULT 0 COMMENT ‘创建用户id’,
create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
update_user_id bigint(20) DEFAULT 0 COMMENT ‘最后更新用户id’,
update_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘最后更新时间’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT=‘用户请假表’;

#4

在 4.0.12 上面没有复现你说的这个问题，麻烦贴下 select tidb_version() 和复现过程吧。

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.12
Edition: Community
Git Commit Hash: 11a9254bec522c1991033a6098f6dccadc6eb7c1
Git Branch: HEAD
UTC Build Time: 2021-04-16 05:40:45
GoVersion: go1.15.6
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

mysql>
mysql>
mysql> SELECT * FROM (SELECT *,ROW_NUMBER() over(PARTITION BY user_id order BY leave_end_time DESC) r FROM base_user_leave )t WHERE r = 1 AND leave_status in (0,1) and IS_DELETED = '0';
Empty set (0.00 sec)
#5

Release Version: v4.0.12-1-g5d483c4a7
Edition: Community
Git Commit Hash: 5d483c4a78b62d5b20347931ea7587b0deda3f9c
Git Branch: hotfix-4.0.12-collation
UTC Build Time: 2021-04-27 02:50:31
GoVersion: go1.16.3
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

复现过程就是执行那个语句报错
SELECT
*
FROM
( SELECT *, ROW_NUMBER ( ) over ( PARTITION BY user_id ORDER BY leave_end_time DESC ) r FROM base_user_leave ) t
WHERE
r = 1
AND leave_status IN ( 0, 1 )
AND IS_DELETED = ‘0’

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 4 column 35 near “( PARTITION BY user_id ORDER BY leave_end_time DESC ) r FROM base_user_leave ) t
WHERE
r = 1
AND leave_status IN ( 0, 1 )
AND IS_DELETED = ‘0’”
时间: 0.005s

#6

我这里也没复现，你这个看起来像是个 hotfix 版本，麻烦使用 release 版本试试吧。