P1-[4.0 Bug Hunting]-[Window Function]-`over` function is weird

[zhangysh1995]

Bug Report

1. What did you do?

mysql> create table t(a int, b int);                                                                                                                                               Query OK, 0 rows affected (0.00 sec)   

mysql> insert into t values(1,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(4,5);
Query OK, 1 row affected (0.00 sec)

mysql> select (select sum(a) over ())  from t ;
+-------------------------+
| (select sum(a) over ()) |
+-------------------------+
|                       1 |
|                    NULL |
+-------------------------+
2 rows in set (0.00 sec)

mysql> select sum(a) over ()  from t ;
+----------------+
| sum(a) over () |
+----------------+
|              5 |
|              5 |
+----------------+
2 rows in set (0.00 sec)

2. What did you expect to see?

mysql> select (select sum(a) over ())  from t ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '())  from t' at line 1

mysql> select sum(a) over ()  from t ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()  from t' at line 1

3. What did you see instead?

The results don't make any sense.

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

Reproducible on master branch:

commit 38298c2e3e8ae6438b2b9caa696586087ea58a4d (HEAD -> master, origin/master, origin/HEAD)
Author: Zhuomin(Charming) Liu <lzmhhh123@gmail.com>
Date:   Fri May 22 17:40:10 2020 +0800

mysql> select tidb_version();                                                                                                                                                      
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$--------------------------------------------------------+
| tidb_version()
                                                        |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$--------------------------------------------------------+
| Release Version: None
Edition: None
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[shuke987]

/bug P1

[wwar]

Window functions are a MySQL 8.0 feature. Both these queries actually work in MySQL 8, but the result of the first query is different, which I believe is a bug:

drop table if exists t;
create table t(a int, b int); 
insert into t values(1,2),(4,5);
select (select sum(a) over ())  from t ;
select sum(a) over ()  from t ;

..

mysql [localhost:8020] {msandbox} (test) > select (select sum(a) over ())  from t ;
+-------------------------+
| (select sum(a) over ()) |
+-------------------------+
|                       1 |
|                       4 |
+-------------------------+
2 rows in set (0.00 sec)

mysql [localhost:8020] {msandbox} (test) > select sum(a) over ()  from t ;
+----------------+
| sum(a) over () |
+----------------+
|              5 |
|              5 |
+----------------+
2 rows in set (0.00 sec)