window function: Window 'w': frame start or end is negative, NULL or of non-integral type

[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 DENSE_RANK() OVER w1 AS 'dense_rank', fieldA, fieldB FROM ( SELECT `col_int` AS fieldA, `col_date_key` AS fieldB FROM `table30_int_autoinc` ) as t WINDOW w1 AS ( ORDER BY fieldA , fieldB ASC ROWS BETWEEN 1250951168 FOLLOWING AND 1250951168 PRECEDING);

2. What did you expect to see?

ERROR 3586 (HY000): Window 'w1': frame start or end is negative, NULL or of non-integral type

3. What did you see instead?

+------------+--------+------------+
| dense_rank | fieldA | fieldB     |
+------------+--------+------------+
|          1 | -31556 | 2003-09-16 |
|          2 |  -1835 | NULL       |
|          3 |   -111 | NULL       |
|          4 |     -9 | 2005-08-12 |
|          5 |     -9 | 2005-10-05 |
|          6 |     -1 | NULL       |
|          7 |     -1 | 0000-00-00 |
|          7 |     -1 | 0000-00-00 |
|          7 |     -1 | 0000-00-00 |
|          8 |     -1 | 2002-07-15 |
|          9 |     -1 | 2002-07-27 |
|         10 |      0 | NULL       |
|         10 |      0 | NULL       |
|         11 |      0 | 0000-00-00 |
|         12 |      0 | 2000-12-16 |
|         13 |      0 | 2002-11-13 |
|         14 |      1 | NULL       |
|         14 |      1 | NULL       |
|         15 |      1 | 0000-00-00 |
|         16 |      1 | 2000-08-20 |
|         17 |      1 | 2005-02-27 |
|         18 |      1 | 2005-08-13 |
|         19 |      1 | 2006-02-22 |
|         20 |      1 | 2006-03-28 |
|         21 |      1 | 2006-12-19 |
|         22 |      1 | 2007-04-25 |
|         23 |     13 | NULL       |
|         24 |     13 | 2001-11-21 |
|         25 |     43 | 2008-06-27 |
|         26 |  15727 | 2008-10-01 |
+------------+--------+------------+
30 rows in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                      |
+-------+------+--------------------------------------------------------------------------------------------------------------+
| Note  | 3599 | Window function 'DENSE_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

[SunRunAway]

Ping @zuoRambo , any update?

[zuoRambo]

@SunRunAway pong~ Ongoing...

[zuoRambo]

Sorry & SOS, I need help!

I read the code about PlanBuilder, when the PB buildSelect and detect hasWindowFuncField. PB groupWindowFuncs before buildWindowFunctions. What's wrong with the handler stack?

tidb/planner/core/logical_plan_builder.go

Lines 2966 to 2969 in 8c20289

	numRows, isNull, isExpectedType := getUintFromNode(b.ctx, boundClause.Expr)
	if isNull || !isExpectedType {
	return nil, ErrWindowFrameIllegal.GenWithStackByArgs(getWindowName(spec.Name.O))
	}

Map noFrameWindowFuncs defines the functions that needn't have frame specifications. So we the warnings.

[SunRunAway]

Hi, I've tried functions which is not in noFrameWindowFuncs, it still occurs the same problem.

In MySQL8,

mysql> SELECT FIRST_VALUE(fieldA) OVER w1 AS 'dense_rank', fieldA, fieldB FROM ( SELECT `col_double_unsigned` AS fieldA, `col_double_unsigned` AS fieldB FROM `table30_int_autoinc` ) as t WINDOW w1 AS ( ORDER BY fieldA , fieldB ASC ROWS BETWEEN 1250951168 FOLLOWING AND 1250951168 PRECEDING);
ERROR 3586 (HY000): Window 'w1': frame start or end is negative, NULL or of non-integral type

In TiDB,

mysql> SELECT FIRST_VALUE(fieldA) OVER w1 AS 'dense_rank', fieldA, fieldB FROM ( SELECT `col_double_unsigned` AS fieldA, `col_double_unsigned` AS fieldB FROM `table30_int_autoinc` ) as t WINDOW w1 AS ( ORDER BY fieldA , fieldB ASC ROWS BETWEEN 1250951168 FOLLOWING AND 1250951168 PRECEDING);
+------------+-------------+-------------+
| dense_rank | fieldA      | fieldB      |
+------------+-------------+-------------+
|       NULL |        NULL |        NULL |
|       NULL |        NULL |        NULL |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |           0 |           0 |
|       NULL |  0.00000001 |  0.00000001 |
|       NULL |  0.00000001 |  0.00000001 |
|       NULL |  0.00000001 |  0.00000001 |
|       NULL | 0.999999999 | 0.999999999 |
|       NULL |    1.098781 |    1.098781 |
|       NULL |    1.098781 |    1.098781 |
|       NULL |    1.098781 |    1.098781 |
|       NULL |    1.098781 |    1.098781 |
+------------+-------------+-------------+
30 rows in set (0.00 sec)