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
[13 Sep 2021 6:44] MySQL Verification Team 
- 8.0.26

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

set optimizer_switch="derived_condition_pushdown=off";

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

Level 	Code 	Message
Warning 	1287 	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning 	1287 	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.

-- 5.7/5.6 - Not repro and even can't set derived_condition_pushdown=off

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

set optimizer_switch="derived_condition_pushdown=off";
Variable 'optimizer_switch' can't be set to the value of 'derived_condition_pushdown=off'

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
[13 Sep 2021 6:55] MySQL Verification Team 
- Lowest version checked 8.0.11

bin/mysql -uroot -S /tmp/mysql_ushastry.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.02 sec)

mysql> use test
Database changed

mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.06 sec)

mysql>  insert into t1 values(100),(200),(300),(400),(500);
Query OK, 5 rows affected (0.11 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 |    3 |
+------+------+
1 row in set (0.00 sec)

mysql> set optimizer_switch="derived_condition_pushdown=off";
ERROR 1231 (42000): Variable 'optimizer_switch' can't be set to the value of 'derived_condition_pushdown=off'
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 (0.00 sec)
[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.
[19 Jan 2022 8:50] Erlend Dahl 
Bug#106191 user variable not work in where

was marked as a duplicate.
[21 Feb 2022 6:39] MySQL Verification Team 
Bug #106522 marked as duplicate of this one
[13 Sep 2023 1:22] Z Sbin 
In the RDS service provided by Huawei Cloud, the latest version (2023-09-13 09:21:52) is 8.0.25, and this bug exists not only in 8.0.26, but also in 8.0.25. We have confirmed to Huawei Cloud that the RDS service sold by Huawei Cloud contains this bug

在华为云提供的RDS服务里, 当前最新版本(2023-09-13 09:21:52)是8.0.25, 这个bug不仅是8.0.26里存在,在8.0.25里也存在. 已向华为云官方证实其销售的RDS服务包含此bug