MDEV-9197 adds support for pushdown of conditions into mergeable VIEWs (or derived tables).

Suppose the child query

• uses a window function
• the window function has a PARTITION BY col1 clause

Then, the condition on col1 can be pushed down through the window function.

Example:

select * from (

  select

    name,

    rank() over (partition by dept

                 order by incidents desc) as R

  from

    staff

) as TBL

where dept='Support'

Here, current execution will compute incidents across all departments. However, we are only interested in dept='Support'. PARTITION BY dept allows pushing the dept='Support' condition to the inner query's WHERE condition.

Initial mention of this possible feature:
https://www.percona.com/live/data-performance-conference-2016/sessions/window-functions-mariadb , slide #66.

CC: cvicentiu, igor, sanja.