[MDEV-6115] window functions as in the SQL standard - Jira

Sergei Golubchik created issue - 2014-04-15 20:35

Sergei Golubchik made changes - 2014-04-17 19:53

Field	Original Value	New Value
Labels		optimizer

Daniel Bartholomew made changes - 2014-05-16 12:44

Assignee

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2014-05-27 16:23

Fix Version/s		10.1.1 [ 16100 ]
Fix Version/s	10.1.0 [ 12200 ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 38931 ]

MariaDB v2 [ 42505 ]

Sergei Petrunia made changes - 2014-08-07 16:12

Fix Version/s		10.2.0 [ 14601 ]
Fix Version/s	10.1 [ 16100 ]

Sergei Petrunia made changes - 2015-01-30 22:45

Assignee

Sergei Petrunia [ psergey ]

Igor Babaev [ igor ]

Sergei Petrunia made changes - 2015-01-30 22:45

Assignee

Igor Babaev [ igor ]

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2015-02-11 23:33

Assignee

Sergei Petrunia [ psergey ]

Igor Babaev [ igor ]

Sergei Petrunia made changes - 2015-03-17 17:20

Description

windowing functions as in the SQL standard

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h3. Basic idea about operation

Let's start with one window function.

SELECT

Sergei Petrunia made changes - 2015-03-17 17:26

Description

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h3. Basic idea about operation

Let's start with one window function.

SELECT

Sergei Petrunia made changes - 2015-03-17 17:32

Description

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h3. Basic idea about operation

Let's start with one window function.

SELECT

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h3. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind those?

Sergei Petrunia made changes - 2015-03-17 17:32

Description

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h3. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind those?

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind those?

Sergei Petrunia made changes - 2015-03-17 17:35

Description

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind those?

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind those?

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

Sergei Petrunia made changes - 2015-03-17 17:36

Description

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind those?

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind 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 pull-based reading of its resultset)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

Sergei Petrunia made changes - 2015-03-17 17:37

Description

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind 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 pull-based reading of its resultset)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind 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 pull-based reading of its resultset. I)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

Sergei Petrunia made changes - 2015-03-17 17:37

Description

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind 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 pull-based reading of its resultset. I)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind 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 pull-based reading of its resultset)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

Sergei Petrunia made changes - 2015-03-17 17:38

Description

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 in mind 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 pull-based reading of its resultset)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 pull-based reading of its resultset)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

Sergei Petrunia made changes - 2015-03-17 18:03

Description

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 pull-based reading of its resultset)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 pull-based reading of its resultset)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

h2. Links
* a tutorial http://sqlschool.modeanalytics.com/advanced/window-functions.html
*

Sergei Petrunia made changes - 2015-05-04 19:17

Link

This issue relates to ~~MDEV-8091~~ [ ~~MDEV-8091~~ ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 42505 ]

MariaDB v3 [ 66745 ]

Sergei Golubchik made changes - 2015-10-30 20:35

Summary

windowing functions as in the SQL standard

window functions as in the SQL standard

Sergei Golubchik made changes - 2015-10-30 20:35

Description

windowing functions as in the SQL standard

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 pull-based reading of its resultset)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

h2. Links
* a tutorial http://sqlschool.modeanalytics.com/advanced/window-functions.html
*

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 pull-based reading of its resultset)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

h2. Links
* a tutorial http://sqlschool.modeanalytics.com/advanced/window-functions.html
*

Sergei Golubchik made changes - 2015-11-17 17:31

Fix Version/s

10.2 [ 14601 ]

Sergei Golubchik made changes - 2015-11-26 16:07

Link

This issue relates to ~~MDEV-5535~~ [ ~~MDEV-5535~~ ]

Sergei Petrunia made changes - 2015-11-26 17:01

Description

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 pull-based reading of its resultset)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

h2. Links
* a tutorial http://sqlschool.modeanalytics.com/advanced/window-functions.html
*

h1. Spec draft

h2. 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 temptable-based grouping operation, we can read its output (and apply HAVING on the fly)
* if we are operating after a Join operation, or non-temptable based grouping, we will need to store the output of previous operation in a temptable and then work from there.

h2. Basic idea about operation

Let's start with one window function:

{noformat}
func_name(arg) OVER (PARTITION BY part_expr ORDER BY order_expr $window_spec)
{noformat}

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 pull-based reading of its resultset)

h2. Multiple window functions
A query may use multiple window functions which use different PARTITION BY / ORDER BY expressions:
{noformat}
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
{noformat}

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

h2. Links
* a tutorial http://sqlschool.modeanalytics.com/advanced/window-functions.html
* SQL standard draft: see 5CD2-02-Foundation-2006-01.pdf
** pg. 62 - 4.15.3 window functions
** pg. 204 - <window function>

Sergei Petrunia made changes - 2015-11-27 01:34

Link

This issue is blocked by ~~MDEV-8646~~ [ ~~MDEV-8646~~ ]

Sergei Petrunia made changes - 2015-11-27 01:59

Link

This issue includes ~~MDEV-9198~~ [ ~~MDEV-9198~~ ]

Sergei Petrunia made changes - 2015-11-27 18:37

Link

This issue includes ~~MDEV-9204~~ [ ~~MDEV-9204~~ ]

Sergei Petrunia made changes - 2016-02-06 13:17

Link

This issue includes MDEV-9525 [ MDEV-9525 ]

Sergei Petrunia made changes - 2016-02-06 23:54

Link

This issue includes ~~MDEV-9526~~ [ ~~MDEV-9526~~ ]

Igor Babaev (Inactive) made changes - 2016-02-10 00:38

Link

This issue is blocked by ~~MDEV-9543~~ [ ~~MDEV-9543~~ ]

Sergei Petrunia made changes - 2016-03-02 16:33

Link

This issue includes ~~MDEV-9676~~ [ ~~MDEV-9676~~ ]

Sergei Petrunia made changes - 2016-03-13 09:26

Link

This issue includes ~~MDEV-9719~~ [ ~~MDEV-9719~~ ]

Sergei Petrunia made changes - 2016-03-14 13:59

Link

This issue includes ~~MDEV-9724~~ [ ~~MDEV-9724~~ ]

Sergei Petrunia made changes - 2016-03-14 19:07

Link

This issue includes MDEV-9727 [ MDEV-9727 ]

Sergei Petrunia made changes - 2016-03-15 21:11

Link

This issue includes MDEV-9736 [ MDEV-9736 ]

Sergei Petrunia made changes - 2016-03-16 09:21

Link

This issue includes ~~MDEV-9740~~ [ ~~MDEV-9740~~ ]

Sergei Petrunia made changes - 2016-03-16 09:26

Link

This issue includes MDEV-9741 [ MDEV-9741 ]

Sergei Petrunia made changes - 2016-03-16 12:26

Link

This issue includes ~~MDEV-9746~~ [ ~~MDEV-9746~~ ]

Sergei Petrunia made changes - 2016-03-17 15:03

Link

This issue includes ~~MDEV-9753~~ [ ~~MDEV-9753~~ ]

Sergei Petrunia made changes - 2016-03-17 16:00

Link

This issue includes ~~MDEV-9754~~ [ ~~MDEV-9754~~ ]

Sergei Petrunia made changes - 2016-03-17 18:31

Link

This issue includes ~~MDEV-9755~~ [ ~~MDEV-9755~~ ]

Sergei Petrunia made changes - 2016-03-23 11:09

Link

This issue includes ~~MDEV-9780~~ [ ~~MDEV-9780~~ ]

Sergei Petrunia made changes - 2016-03-24 15:02

Link

This issue includes ~~MDEV-9787~~ [ ~~MDEV-9787~~ ]

Sergei Petrunia made changes - 2016-03-31 10:26

Link

This issue includes ~~MDEV-9841~~ [ ~~MDEV-9841~~ ]

Sergei Petrunia made changes - 2016-03-31 16:07

Link

This issue includes ~~MDEV-9847~~ [ ~~MDEV-9847~~ ]

Sergei Petrunia made changes - 2016-03-31 16:09

Link

This issue includes MDEV-9848 [ MDEV-9848 ]

Sergei Petrunia made changes - 2016-04-06 14:44

Link

This issue includes ~~MDEV-9877~~ [ ~~MDEV-9877~~ ]

Sergei Petrunia made changes - 2016-04-10 07:04

Link

This issue includes ~~MDEV-9892~~ [ ~~MDEV-9892~~ ]

Elena Stepanova made changes - 2016-04-10 08:35

Link

This issue relates to ~~MDEV-9894~~ [ ~~MDEV-9894~~ ]

Elena Stepanova made changes - 2016-04-10 08:59

Link

This issue relates to ~~MDEV-9895~~ [ ~~MDEV-9895~~ ]

Sergei Petrunia made changes - 2016-04-10 16:11

Link

This issue includes ~~MDEV-9897~~ [ ~~MDEV-9897~~ ]

Sergei Petrunia made changes - 2016-04-14 16:29

Link

This issue includes ~~MDEV-9911~~ [ ~~MDEV-9911~~ ]

Elena Stepanova made changes - 2016-04-14 20:07

Component/s

Optimizer - Window functions [ 13502 ]

Sergei Petrunia made changes - 2016-04-17 11:42

Link

This issue includes ~~MDEV-9935~~ [ ~~MDEV-9935~~ ]

Sergei Petrunia made changes - 2016-05-04 13:24

Link

This issue includes ~~MDEV-10023~~ [ ~~MDEV-10023~~ ]

Sergei Petrunia made changes - 2016-09-21 11:05

Link

This issue relates to ~~MDEV-10855~~ [ ~~MDEV-10855~~ ]

Geoff Montee (Inactive) made changes - 2018-07-20 22:46

Link

This issue relates to MDEV-16795 [ MDEV-16795 ]

Alexander Bienemann (Inactive) made changes - 2018-09-13 17:03

Link

This issue includes ~~MDEV-17191~~ [ ~~MDEV-17191~~ ]

Sergei Golubchik made changes - 2019-03-04 17:29

Fix Version/s		10.2.2 [ 22013 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:22

Workflow

MariaDB v3 [ 66745 ]

MariaDB v4 [ 132324 ]

MariaDB Server

window functions as in the SQL standard

Details

Description

Spec draft

Place in the optimizer

Basic idea about operation

Multiple window functions

Links

Attachments

Issue Links

Activity

People

Dates

Git Integration