Understanding MySQL 8.0 Derived Condition Pushdown Optimization and Its Impact on User Variables

Starting with MySQL 8.0.22, a new optimizer_switch option called derived_condition_pushdown was introduced. When this option is turned ON , the optimizer may push outer WHERE conditions that reference user variables down into derived tables, which can lead to results that differ from expectations.

Simple introduction : The term “derived condition pushdown” literally means pushing conditions down to derived tables. When enabled, MySQL can rewrite queries such as

SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > constant;

into

SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt;

This reduces the number of rows returned by the derived table and speeds up the query.

Changing the optimizer_switch (values can be ON or OFF ):

set optimizer_switch='derived_condition_pushdown=on';  -- session level
set global optimizer_switch='derived_condition_pushdown=on';  -- global for new sessions
set persist optimizer_switch='derived_condition_pushdown=on';  -- persist to mysqld-auto.cnf

Test environment : MySQL 8.0.23.

When derived_condition_pushdown=ON

Test query 1:

set optimizer_switch='derived_condition_pushdown=on';
set @r=0;
select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;
explain select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;
set @r=1;
select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;
explain select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;

The results show that the optimizer rewrites the query to evaluate the derived table first and then apply the WHERE filter, causing the user variable @r to retain its previous value and produce unexpected rows.

Test query 2 demonstrates the same effect with a slightly different syntax.

When derived_condition_pushdown=OFF the original query is executed without rewriting, so the derived table is evaluated after the WHERE condition, yielding the correct result.

Notes :

Versions prior to MySQL 8.0.22 do not have the optimizer_switch parameter; the behavior is equivalent to derived_condition_pushdown=off .

Enabling the option in MySQL 8.0.22+ can cause queries that use user variables in the outer WHERE clause to behave unexpectedly.

Solutions :

Disable the feature permanently: set persist optimizer_switch='derived_condition_pushdown=off';

For a single session, set it to off before running the problematic query: set optimizer_switch='derived_condition_pushdown=off';

Rewrite the SQL so that the outer WHERE does not reference user variables, e.g.: select * from (select @r id,@i num from (select @r := 603014203924416,@i := 0) vars ) a where id<>0 ;

Assign the user variable value before the query and then use it without the derived table condition. select @r := 603014203924416,@i := 0 ; select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;

These methods ensure that the query returns the expected results regardless of the optimizer's push‑down behavior.