把测试环境从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文件.