Bug #106522 8.025 causes differences in execution results

• Submitted: 21 Feb 2022 2:57
• Modified: 21 Feb 2022 6:39
• Reporter: Baolin Huang
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.25
• OS: Any
• CPU Architecture: Any

[21 Feb 2022 2:57] Baolin Huang

Description:
After upgrading to 8.0.25,  sql execution result changed.

 

How to repeat:
-- prepare
create table a(id int);
insert into a values(1),(2),(3),(4);

-- execute on 8.0.25
mysql> select * from  a, (SELECT @id := (2)) b WHERE @id > 1;
Empty set, 1 warning (0.00 sec)

--execute on 8.0.22
mysql> select * from  a, (SELECT @id := (2)) b WHERE @id > 1;
+------+------------+
| id   | @id := (2) |
+------+------------+
|    1 |          2 |
|    2 |          2 |
|    3 |          2 |
|    4 |          2 |
+------+------------+
4 rows in set  1 warning (0.00 sec)

Suggested fix:
Has 8.0.25 changed in handling derived tables？

[21 Feb 2022 3:25] Baolin Huang

Found a similar bug
https://bugs.mysql.com/bug.php?id=104918 

Confirmed it's the same problem

[21 Feb 2022 6:39] MySQL Verification Team

Hello Baolin Huang,

Thank you for the report and test case.
This issue is no longer reproducible in 8.0.28(most likely fixed after Bug #104918 ). Marking this as a duplicate of Bug #104918.

regards,
Umesh