window function: frame end cannot be UNBOUNDED PRECEDING.

[SunRunAway]

Bug Report

Please answer these questions before submitting your issue. Thanks!

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

SELECT PERCENT_RANK() OVER w1 AS 'percent_rank', fieldA, fieldB FROM ( SELECT `pk` AS fieldA, `col_time_key` AS fieldB FROM `table50_int_autoinc` ) as t WINDOW w1 AS ( ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED PRECEDING);

2. What did you expect to see?

ERROR 3585 (HY000): Window 'w1': frame end cannot be UNBOUNDED PRECEDING.

3. What did you see instead?

+--------------+--------+----------+
| percent_rank | fieldA | fieldB   |
+--------------+--------+----------+
|            0 |      1 | 00:20:06 |
|            0 |      2 | 23:40:13 |
|            0 |      3 | 07:27:19 |
|            0 |      4 | NULL     |
|            0 |      5 | 07:04:48 |
|            0 |      6 | 00:20:07 |
|            0 |      7 | NULL     |
|            0 |      8 | 17:11:55 |
|            0 |      9 | 00:20:04 |
|            0 |     10 | NULL     |
|            0 |     11 | 23:35:08 |
|            0 |     12 | NULL     |
|            0 |     13 | 00:20:01 |
|            0 |     14 | NULL     |
|            0 |     15 | NULL     |
|            0 |     16 | 11:57:39 |
|            0 |     17 | 22:24:24 |
|            0 |     18 | 00:20:07 |
|            0 |     19 | 00:20:05 |
|            0 |     20 | 19:48:01 |
|            0 |     21 | 00:20:02 |
|            0 |     22 | NULL     |
|            0 |     23 | 03:31:17 |
|            0 |     24 | 00:20:01 |
|            0 |     25 | 15:32:36 |
|            0 |     26 | 00:20:04 |
|            0 |     27 | 00:20:07 |
|            0 |     28 | 00:20:01 |
|            0 |     29 | 20:56:35 |
|            0 |     30 | 00:20:07 |
|            0 |     31 | 18:38:58 |
|            0 |     32 | NULL     |
|            0 |     33 | NULL     |
|            0 |     34 | 01:25:20 |
|            0 |     35 | 00:20:02 |
|            0 |     36 | 14:24:09 |
|            0 |     37 | NULL     |
|            0 |     38 | 04:14:08 |
|            0 |     39 | 00:20:05 |
|            0 |     40 | 09:06:10 |
|            0 |     41 | NULL     |
|            0 |     42 | 05:12:37 |
|            0 |     43 | 00:20:07 |
|            0 |     44 | 00:20:04 |
|            0 |     45 | NULL     |
|            0 |     46 | 20:29:21 |
|            0 |     47 | 00:20:01 |
|            0 |     48 | 17:49:45 |
|            0 |     49 | NULL     |
|            0 |     50 | 05:23:21 |
+--------------+--------+----------+

mysql> show warnings;                                                                                                                                                  +-------+------+----------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                        |
+-------+------+----------------------------------------------------------------------------------------------------------------+
| Note  | 3599 | Window function 'PERCENT_RANK' ignores the frame clause of window 'w1' and aggregates over the whole partition |
+-------+------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

[zuoRambo]

/assign

[zuoRambo]

tidb/planner/core/logical_plan_builder.go

Lines 3101 to 3103 in 8c20289

	if end.Type == ast.Preceding && end.UnBounded {
	return nil, ErrWindowFrameEndIllegal.GenWithStackByArgs(getWindowName(spec.Name.O))
	}

It looks like the issue has been resolved?

I find WindowFuncPercentRank has been involved in noFrameWindowFuncs.

Ref link mysql window-functions-frames

[SunRunAway]

Yes, you're right. Thanks for your investigation.

Because the function PERCENT_RANK should use the entire partition even if a frame is specified, TiDB does not check the original frame clause and just ignore it while MySQL checks the window frame syntax then ignore it.
TiDB's current implementation is more efficient, and MySQL's implementation is even more wired., but the compatibility should also be reconsidered.
@zz-jason @lamxTyler PTAL and give some advice.

[SunRunAway]

Hi, @zuoRambo
After some discussion, we think it's better to handle noFrameWindowFuncs after checking the correctness of the frame.
PTAL again, thanks.