Details

Type: Task

Status: Closed (View Workflow)

Priority: Major

Resolution: Fixed

Fix Version/s: 10.2.2

Component/s: Optimizer  Window functions

Labels:
Description
Spec draft
Place in the optimizer
Window functions are evaluated on the result set after the WHERE, GROUP BY and HAVING have been applied.
 If we are operating after a temptablebased grouping operation, we can read its output (and apply HAVING on the fly)
 if we are operating after a Join operation, or nontemptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.
Basic idea about operation
Let's start with one window function:
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)

Window function is evaluated for each row of the result set. It is a function of row's partition, we need to look at the rows of the resultset that are ordered according to order_expr. $window_spec specifies at which rows we need to look. Sometimes it's certain preceding rows, sometimes it's certain following rows (and sometimes it's both?)
Execution can be done as follows:
 sort resultset rows by (part_expr, order_expr).
 scan each partition (either in reverse or forward order) and compute the value of window function on the fly.
Unresolved questions:
 How to handle the context. If a window function depends on preceding and following rows, how do we keep track of those?
 Whether we should use files or temp.tables for storing the sorted resultset (we will always have to store it, because filesort doesn't support pullbased reading of its resultset)
Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
SELECT

window_func1 OVER (PARTITION BY part_expr1 ORDER BY order_expr1)

window_func2 OVER (PARTITION BY part_expr2 ORDER BY order_expr2)

FROM ... WHERE ... etc

The query should be evaluated as follows:
 sort the result according to part_expr1/order_expr1, compute values of window_func1
 sort the result according to part_expr2/order_expr2, compute values of window_func2
Links
 a tutorial http://sqlschool.modeanalytics.com/advanced/windowfunctions.html
 SQL standard draft: see 5CD202Foundation200601.pdf
 pg. 62  4.15.3 window functions
 pg. 204  <window function>
Attachments
Issue Links
 includes

MDEV9198 Window functions: parser, error checking, and name resolution
 Closed

MDEV9204 Window functions: locate the place where to put window function execution calls
 Closed

MDEV9525 Window functions: Support for aggregate_func(DISTINCT ...) OVER (...)
 Open

MDEV9526 Compute Aggregate functions as window functions
 Closed

MDEV9676 RANGEtype frames for window functions
 Closed

MDEV9719 Query with window function crashes with psprotocol
 Closed

MDEV9724 Window functions: Frame Exclusion support
 Closed

MDEV9727 Window functions: datetime arithmetic for RANGEtype frames
 Open

MDEV9736 Window functions: multiple cursors to read filesort result
 Open

MDEV9740 Window functions: catch invalid window frame specs
 Closed

MDEV9741 Window functions: support being in a subquery
 In Progress

MDEV9746 Window functions: CUME_DIST function
 Closed

MDEV9753 Window functions: reject window frame definition where it is not allowed
 Closed

MDEV9754 Window functions: assertion failure in with psprotocol
 Closed

MDEV9755 Buildbot shows a crash in JOIN::make_aggr_tables_info() on windows
 Closed

MDEV9780 Window functions: interplay between window function and other constructs
 Closed

MDEV9787 Window functions: HAVING and GROUP BY
 Closed

MDEV9841 Window functions: embedded server fails to start due to error code conflicts
 Closed

MDEV9847 Window functions: crash with big_tables=1
 Closed

MDEV9848 Window functions: reuse sorting and/or scanning
 Open

MDEV9877 Window functions: wrong sort criteria is used
 Closed

MDEV9892 Window functions: add a counter
 Closed

MDEV9897 Window functions: crash when ORDER BY clause uses an expression
 Closed

MDEV9911 NTILE must return an error when parameter is not stable
 Closed

MDEV9935 Window functions: assertion failure with empty OVER() clause
 Closed

MDEV10023 Window functions: big window frame handling
 Closed

MDEV17191 Named WINDOW in window functions
 Closed
 is blocked by

MDEV8646 Reengineer the code for postjoin operations
 Closed

MDEV9543 Parsing, name resolution and optimization for window functions.
 Closed
 relates to

MDEV8091 Simple window functions
 Closed

MDEV9894 Assertion `0' failed in Window_func_runner::setup
 Closed

MDEV9895 Assertion `n_rows > 0' failed in Frame_cursor* get_frame_cursor
 Closed

MDEV5535 cannot reopen temporary table
 Closed

MDEV10855 Window functions: condition pushdown through the PARTITION BY clause
 Closed