Bug #106191 user variable not work in where

• Submitted: 18 Jan 2022 5:45
• Modified: 19 Jan 2022 8:49
• Reporter: flyingsand leung
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.27
• OS: Windows
• CPU Architecture: x86

[18 Jan 2022 5:45] flyingsand leung

Description:
the test code work in 8.0.21,return 2 row.
the same test code not work in 8.0.27,return 0 row.

How to repeat:
CREATE TABLE t1 (
  a varchar(10)
);
insert into t1(a) VALUES('a');
insert into t1(a) VALUES('b');
insert into t1(a) VALUES('c');
insert into t1(a) VALUES('d');

SELECT @d := @d + 1 idx
FROM t1,(SELECT @d := 0) t
WHERE @d < 2;

Expected results(8.0.21):
+-----+
| idx |
+-----+
|   1 |
|   2 |
+-----+
2 rows in set (0.01 sec)

Actual results(8.0.27):
Empty set

[18 Jan 2022 5:54] MySQL Verification Team

Hello flyingsand leung,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

[19 Jan 2022 8:49] Erlend Dahl

Duplicate of

Bug#104918 Derived condition pushdown rewrite ignores user variables

(fixed in 8.0.28)