Window Function BEWEEN clause behaviour is inconsistent with Mysql When frame_end is less than or equal to frame_start

[DQinYuan]

Bug Report

Similar with issue#11011，but not the same. This issue still exist after issue#11011 fixed.

1. What did you do?
   If possible, provide a recipe for reproducing the error.

create table temp (val int);

insert into temp values(1),(1),(2),(2);

SELECT val, SUM(val) OVER w AS 'sum' FROM temp WINDOW w AS (ROWS BETWEEN  CURRENT ROW AND  0 PRECEDING);

SELECT val, SUM(val) OVER w AS 'sum' FROM temp WINDOW w AS (ROWS BETWEEN  CURRENT ROW AND  1 PRECEDING);

2. What did you expect to see?

In mysql 8.0.16, it will return error when frame_end is less than or equal to frame_start

mysql> create table temp (val int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into temp values(1),(1),(2),(2);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT val, SUM(val) OVER w AS 'sum' FROM temp WINDOW w AS (ROWS BETWEEN  CURRENT ROW AND  0 PRECEDING);
ERROR 3586 (HY000): Window 'w': frame start or end is negative, NULL or of non-integral type
mysql> SELECT val, SUM(val) OVER w AS 'sum' FROM temp WINDOW w AS (ROWS BETWEEN  CURRENT ROW AND  1 PRECEDING);
ERROR 3586 (HY000): Window 'w': frame start or end is negative, NULL or of non-integral type

3. What did you see instead?

In tidb,

• When frame_end == frame_start，it will become frames with one row
• When frame_end < frame_start，it will all return NULL

mysql> create table temp (val int);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into temp values(1),(1),(2),(2);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT val, SUM(val) OVER w AS 'sum' FROM temp WINDOW w AS (ROWS BETWEEN  CURRENT ROW AND  0 PRECEDING);
+------+------+
| val  | sum  |
+------+------+
|    1 |    1 |
|    1 |    1 |
|    2 |    2 |
|    2 |    2 |
+------+------+
4 rows in set (0.00 sec)

mysql> SELECT val, SUM(val) OVER w AS 'sum' FROM temp WINDOW w AS (ROWS BETWEEN  CURRENT ROW AND  1 PRECEDING);
+------+------+
| val  | sum  |
+------+------+
|    1 | NULL |
|    1 | NULL |
|    2 | NULL |
|    2 | NULL |
+------+------+
4 rows in set (0.00 sec)

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

mysql> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.3
Git Commit Hash: 836982c617fbaa42d74616eb9ba9ed4cae46c46b
Git Branch: HEAD
UTC Build Time: 2019-08-30 02:42:42
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[SunRunAway]

@lzmhhh123 PTAL

[lzmhhh123]

@DQinYuan Thanks for your report. I'll fix in several days.