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)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0, 8.0.26
• OS: Any
• 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.