Bug #104918 Derived condition pushdown rewrite ignores user variables
Submitted: 13 Sep 3:12 Modified: 21 Sep 22:40
Reporter: casa zhang (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0, 8.0.26 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, regression

[13 Sep 3:12] casa zhang
Description:
Derived condition pushdown rewrite seems like it ignores the situation query block has user variables.

How to repeat:
mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(100),(200),(300),(400),(500);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from (select c1, (@rownum_r := @rownum_r + 1) as r from t1, (select @rownum_r := 0) as b) as q where q.c1 = 300;
+------+------+
| c1 | r |
+------+------+
| 300 | 1 |
+------+------+
1 row in set, 2 warnings (0.00 sec)

when turns off the derived_condition_pushdown switch in optimzier_switch, it got the right result.

mysql> set optimizer_switch="derived_condition_pushdown=off";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from (select c1, (@rownum_r := @rownum_r + 1) as r from t1, (select @rownum_r := 0) as b) as q where q.c1 = 300;
+------+------+
| c1 | r |
+------+------+
| 300 | 3 |
+------+------+
1 row in set, 2 warnings (0.00 sec)

Suggested fix:
In `TABLE_LIST::can_push_condition_to_derived(THD *thd)`, we can check the whether `Query_block` has user variables, add a member variables `has_user_vars` to `Query_block`.
[13 Sep 3:18] casa zhang
Derived condition pushdown rewrite ignores user variables

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-bugfix-Derived-condition-pushdown-rewrite-ignores-us.patch (application/octet-stream, text), 1.95 KiB.

[13 Sep 6:35] MySQL Verification Team
Hello casa zhang,

Thank you for the report and contribution.

regards,
Umesh
[21 Sep 22:40] Jon Stephens
Documented fix as follows in the MySQL 8.0.28 changelog:

    When a condition was pushed down, the result of evaluating
    assignments to user variables in the SELECT list of the subquery
    were sometimes affected. For this reason, we now prevent
    condition pushdown for statements with assignments to user
    variables.

    Our thanks to Casa Zhang for the contribution.

Also noted this new restriction at http://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html.

Closed.