Bug #86990 Window frame accepts non-integer arg for ROWS

• Submitted: 8 Jul 2017 21:42
• Modified: 2 Oct 2017 17:29
• Reporter: Dag Wanvik
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.2
• OS: Any
• CPU Architecture: Any

[8 Jul 2017 21:42] Dag Wanvik

Description:
prepare p from 'select sum(w) over (rows 3.14 preceding) from t';

3.14 is accepted, but is dubious: the number of rows is always an integral (positive) value.

It works, because 3.14 is converted to an int, but should probably be flagged,
especially since we do not accept an expression in this place, only positive literals,
interval literals or a dynamic marker (?)/

If the frame is specified with RANGE, it should be probably be allowed notwithstanding the
type of the order by expression (?).

How to repeat:
CREATE TABLE t(w INT);
INSERT INTO t VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
SELECT w, SUM(w) OVER (ROWS 3.14 PRECEDING) FROM t;

[8 Jul 2017 22:10] Dag Wanvik

Posted by developer:
 
Using dynamic argument, we can smuggle in worse:

set @arg='x3_5';
prepare p from 'select sum(w) over (rows ? preceding) from t';
mysql> execute p using @arg;
+--------------------------------+
| sum(w) over (rows ? preceding) |
+--------------------------------+
|                              1 |
|                              2 |
|                              3 |
|                              4 |
|                              5 |
|                              6 |
|                              7 |
|                              8 |
|                              9 |
+--------------------------------+

[2 Oct 2017 17:29] Paul DuBois

Posted by developer:
 
Fixed in 8.0.3.

For window functions, a ROW frame accepted noninteger arguments for
the row count.