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: | |
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
[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