WL#8084: Condition pushdown for materialized derived tables

Affects: Server-8.0   —   Status: Complete 

For a query of the form SELECT * FROM (SELECT i, j FROM t1) as dt WHERE i > 10; 
, if the derived table is materialized and not merged, then MySQL currently 
materializes the entire  table "t1" and then qualifies the rows with the WHERE 
condition. However in this case, the outer where condition can be pushed down to 
derived table "dt" .

The transformation of the above query to SELECT * FROM (SELECT i, j FROM t1 
WHERE i > 10) as dt  could be done to reduce the number of rows that need to be 
processed. The goal of this worklog is to be able to pushdown such conditions 
from outer select to a materialized derived table if possible.

In simple queries like the above, MySQL tries to merge the derived table into 
the outer select. However for queries where the derived table cannot be merged; 
For ex if the derive table has aggregation; In such cases, the above 
optimization should improve the query performance if any of the outer where 
conditions are pushed into the derived table.

A where condition can be pushed fully or partially to the derived table

For ex:
SELECT i,j FROM (SELECT i FROM t1 GROUP BY i) as dt, t2 WHERE i > 2 and j < 3;

This should be transformed to
SELECT i,j FROM (SELECT i FROM t1 WHERE i > 2 GROUP BY i) as dt WHERE j < 3;

A where condition from the outer select is attached to the where clause or the 
having clause of derived table depending on certain conditions.

If the derived table does not have any aggregations or window functions, it is 
safe to attach the pushed condition to the where clause of the derived table.

Ex: SELECT * FROM (SELECT i,j FROM t1) as dt  WHERE i > 10;

After condition pushdown -
    SELECT * FROM (SELECT i,j FROM t1 WHERE i > 10) as dt;

If the derived table has group by, but not window functions, then it is safe to 
attach to the having condition.

Ex:
SELECT * FROM (SELECT i,j, SUM(k) as SUM FROM t1 GROUP BY i,j ) as dt  WHERE SUM 
> 10;

After condition pushdown -
SELECT * FROM (SELECT i,j, SUM(k) as SUM FROM t1 GROUP BY i,j HAVING SUM > 10) 
as dt;

However if the fields in the condition are actually group by columns, it is safe 
to push the condition past group by into the where clause.

Ex:
SELECT * FROM (SELECT i,j, SUM(k) as SUM FROM t1 GROUP BY i,j ) as dt  WHERE i > 
10;

After condition pushdown -
SELECT * FROM (SELECT i,j, SUM(k) as SUM FROM t1 WHERE i > 10 GROUP BY i,j ) as 
dt;

SELECT * FROM (SELECT i,j, SUM(k) as SUM FROM t1 GROUP BY i,j ) as dt  WHERE i > 
10 AND SUM > 10;  

After condition pushdown -
SELECT * FROM (SELECT i,j, SUM(k) as SUM FROM t1 WHERE i > 10 GROUP BY i,j 
HAVING SUM > 10) as dt;

Similarly, if the derived table has window functions, then it is safe to push 
past window functions and to the having clause of derived table iff the 
condition has columns that are used in partition clause of the window functions 
present.

Ex:
SELECT * FROM (SELECT i,j, SUM(k) OVER (PARTITION BY j) as SUM FROM t1 ) as dt  
WHERE j > 10 ;  

After condition pushdown -
SELECT * FROM (SELECT i,j, SUM(k) OVER (PARTITION BY j) as SUM FROM t1 WHERE j > 
10) as dt;

SELECT * FROM (SELECT i,j, MIN(k) as MIN , SUM(k) OVER (PARTITION BY i) as SUM 
FROM t1 GROUP BY i,j ) as dt  WHERE i > 10 AND MIN < 3;  

After condition pushdown -
SELECT * FROM (SELECT i,j, MIN(k) as MIN , SUM(k) OVER (PARTITION BY i) as SUM 
FROM t1 WHERE i > 10 GROUP BY i,j HAVING MIN < 3) as dt;

The worklog does not aim to push conditions down to derived table if the derived 
table has UNIONS. This will be addressed as part of WL#13730.


Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.