Bug #104918 Derived condition pushdown rewrite ignores user variables
Submitted: 13 Sep 2021 3:12 Modified: 21 Sep 2021 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 2021 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 2021 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 2021 6:35] MySQL Verification Team
Hello casa zhang,

Thank you for the report and contribution.

regards,
Umesh
[21 Sep 2021 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 and the Tencent team for the contribution.

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

Closed.