LIMIT propagation into UNION ALL Sub-Queries

Detail name	Value
Changelog Number	10747
Type	Improvement
Status	Open

Description

The LIMIT clause in queries is not used to optimize the pre-processing of sub-queries if sub-queries need to be materialized in temporary tables. It could be used to reduce the amount of rows to process.

Example

The sub-query executes a UNION ALL with all rows in the table and then the main query just limits the output rows to 1.
A propagation of the LIMIT clause could reduce the number of rows required to process by the sub-query.

create or replace table t1 (a int);
insert into t1 values (1),(2),(3),(4);

create or replace table t2 (a int, b int);
insert into t2 values (1,1),(2,2),(3,3),(4,4);

SELECT *
FROM
(
    SELECT a FROM t1
    UNION ALL
    SELECT a FROM t2
)
LIMIT 1;

Improvement

Where possible, the LIMIT clause will be propagated to sub-queries to reduce the amount of rows that are materialized. In the example above, the LIMIT will also be applied in the underlying subqueries.