TiDB v3.0.1无法成功执行一条Window Function SQL

把测试环境从v2.1.x升级到v3.0.1之后, 尝试执行一条含有Window Function调用的SQL, 得到如下错误:

>show variables like '%version%';
+-------------------------+---------------------------------------------+
| Variable_name           | Value                                       |
+-------------------------+---------------------------------------------+
| version                 | 5.7.25-TiDB-v3.0.1                          |
| version_comment         | MySQL Community Server (Apache License 2.0) |
| version_compile_machine | x86_64                                      |
| version_compile_os      | osx10.8                                     |
| protocol_version        | 10                                          |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2                       |
| innodb_version          | 5.6.25                                      |
+-------------------------+---------------------------------------------+
7 rows in set (0.01 sec)

>show variables like '%window%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| tidb_enable_window_function | 1     |
+-----------------------------+-------+
1 row in set (0.00 sec)

>select ENAME,
    ->        DEPTNO,
    ->        count(*) over() as cnt
    ->   from EMP
    -> order by DEPTNO;
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 3 column 22 near "() as cnt
  from EMP
order by DEPTNO"

在我的Macbook上, 按照Github上的一个指南以Standalone模式运行了一个v3.0.0-rc1版本的docker, 上述SQL却得以顺利执行.

tidb> use sample_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
tidb> show variables like '%version%';
+-------------------------+---------------------------------------------+
| Variable_name           | Value                                       |
+-------------------------+---------------------------------------------+
| version_compile_machine | x86_64                                      |
| version_compile_os      | osx10.8                                     |
| innodb_version          | 5.6.25                                      |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2                       |
| version_comment         | MySQL Community Server (Apache License 2.0) |
| version                 | 5.7.25-TiDB-v3.0.0-rc.1-412-g5611acd37      |
| protocol_version        | 10                                          |
+-------------------------+---------------------------------------------+
7 rows in set (0.01 sec)

tidb> show variables like '%window%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| tidb_enable_window_function | 1     |
+-----------------------------+-------+
1 row in set (0.01 sec)

tidb> select ENAME,
    ->        DEPTNO,
    ->        count(*) over() as cnt
    ->   from EMP
    -> order by DEPTNO;
+--------+--------+------+
| ENAME  | DEPTNO | cnt  |
+--------+--------+------+
| CLARK  |     10 |   14 |
| KING   |     10 |   14 |
| MILLER |     10 |   14 |
| FORD   |     20 |   14 |
| ADAMS  |     20 |   14 |
| JONES  |     20 |   14 |
| SCOTT  |     20 |   14 |
| SMITH  |     20 |   14 |
| MARTIN |     30 |   14 |
| TURNER |     30 |   14 |
| WARD   |     30 |   14 |
| JAMES  |     30 |   14 |
| ALLEN  |     30 |   14 |
| BLAKE  |     30 |   14 |
+--------+--------+------+
14 rows in set (0.01 sec)

若要重现上述问题, 须创建一个名为EMP的表, 并准备少量数据. 可以从这里下载相关的SQL文件.

从 2.1 版本升级上来无法执行 window function 的这个问题,有两种办法解决:

set @@global. tidb_enable_window_function = 1; 然后断开 session,重连后应该就可以了

或者在当前 session 执行 set @@ tidb_enable_window_function = 1; 然后再执行 window function 的 sql

3赞

多谢回复. 执行 set global tidb_enable_window_function = 1; 并断开连接; 然后重连之后确实可以顺利执行上述SQL了. 请问: 为什么会这样呢? 升级后检查变量, 明明可以看到 tidb_enable_window_function = 1 哦.

这个应该是一个 bug,我们调查一下