MySQL Bugs: #104918: Derived condition pushdown rewrite ignores user variables

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

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`.

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.

Hello casa zhang,

Thank you for the report and contribution.

regards,
Umesh

- 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

- 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)

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.

Bug#106191 user variable not work in where

was marked as a duplicate.

Bug #106522 marked as duplicate of this one