MySQL Bugs: #88381: Predicate cannot be pushed down "past" window function

Bug #88381	Predicate cannot be pushed down "past" window function
Submitted:	7 Nov 2017 9:10	Modified:	12 Aug 2020 1:01
Reporter:	Lukas Eder	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	8.0.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Window functions

Description:
In the current implementation, window functions act as an "optimisation fence" for the predicate pushdown transformation, even if a predicate could "obviously" (I haven't done the math) be pushed "past" the window function, as it (partially) matches the function's PARTITION BY clause.

Without this optimisation, window functions in derived tables can turn into a dangerous beast.

How to repeat:
This query here can be taken as an example, running against the Sakila database:

SELECT
  customer_id,
  payment_date,
  amount,
  cumulative_amount
FROM (
  SELECT
    customer_id,
    payment_date,
    amount,
    SUM(amount) OVER (
      PARTITION BY customer_id
      ORDER BY payment_date, payment_id
    ) cumulative_amount
  FROM payment
) inlined
WHERE customer_id IN (1, 2, 3)
AND payment_date 
  BETWEEN DATE '2005-05-25'
  AND     DATE '2005-05-29'
ORDER BY customer_id, payment_date;

Most databases are capable of pushing down the "customer_id IN (1, 2, 3)" predicate into the derived table and "past" the window function, because it does not affect the function's calculations, being a predicate on the PARTITION BY clause. If there's an index on payment(customer_id), then applying this predicate push down transformation drastically increases performance.

All of the other databases that I've tested (DB2, Oracle, PostgreSQL, and SQL Server) implement this optimisation. Note that in principle, the range predicate could also be pushed down partially, but none of the databases I've tested do that. More details in this blog post:

https://blog.jooq.org/2017/11/06/how-to-avoid-excessive-sorts-in-window-functions/

Suggested fix:
https://youtu.be/uNpcKtxGtTs?t=54s :-)

Hi Lukas, your observation is correct, and we know about this limitation.
Obviously, it something we'd like to improve on.

Hi!

Thank you for your report as it is a significant one.

Since our developer has confirmed that this is a very desirable feature, I am verifying this report as the verified feature request.

Thank you.

Posted by developer:
 
This relies on a well known deficiency, cf WL#8084 Condition pushdown for materialized derived tables.

This is fixed in MySQL 8.0.22 by WL#8084.

See same for docs info.

Closed.

Thank you, Jon.