Bug #97063 | Wrong behavior of '=' condition when working with complex subqueries | ||
---|---|---|---|
Submitted: | 30 Sep 2019 10:26 | Modified: | 18 Nov 2019 10:32 |
Reporter: | Lu Jack | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.0.14+ | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | =, alias, subquery |
[30 Sep 2019 10:26]
Lu Jack
[30 Sep 2019 13:32]
Lu Jack
A fiddle that demos this issue: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=06fd5558a7cba4073816292d1eb143d8.
[2 Oct 2019 12:00]
MySQL Verification Team
Hi Mr. Jack, Thank you for your bug report. First of all, we do not follow the discussions that you are referencing, nor do we use "Sakila" database. Hence, therefore, send us a complete repeatable test case, which will show a bug that you are reporting. If you are reporting several bugs, then please start with one. Also, we do not have a version 8.14.0, so please do try first 8.0.18, before you reply to us.
[2 Oct 2019 14:14]
Lu Jack
fix version: 8.14.0+->8.0.14+
[2 Oct 2019 14:18]
Lu Jack
Sinisa Milivojevic, Sorry, it should be 8.0.14+, not 8.14.0+. I've fixed it. See my comment above. I attached a link on dbfiddle for you to re'pro. Thanks.
[2 Oct 2019 14:27]
MySQL Verification Team
Hi Mr. Jack, Thank you for your feedback. This is indeed a genuine Optimiser bug. Verified as reported !!
[2 Oct 2019 14:49]
Lu Jack
Sinisa Milivojevic, Glad to know it's not my own mistake to bother you. Thank you for your really quick confirmation!
[3 Oct 2019 11:35]
MySQL Verification Team
You are truly welcome .....
[3 Oct 2019 14:31]
Lu Jack
Will the bug be fixed in 8.0.18 release?
[4 Oct 2019 11:46]
MySQL Verification Team
Hi, Definitely not in 8.0.18, since it should come out relatively soon. We will not know when will it be fixed, since scheduling of bug fixing in Development is an internal process to which we truly do not have access. In any case, when it is fixed, this page will be updated and you will get the e-mail. Neither you nor we will know what is happening with any bug, until it is fixed.
[4 Oct 2019 15:57]
Lu Jack
OK I got it. Thanks.
[7 Nov 2019 2:08]
Jon Stephens
Documented fix as follows in the MySQL 8.0.19 changelog: An inner scalar subquery containing an outer reference did not return the same result using a nested set of SELECT expressions on the right hand side as when using a single SELECT that was equivalent. Closed.
[7 Nov 2019 13:19]
MySQL Verification Team
Thank you, Jon .....
[18 Nov 2019 10:32]
Lu Jack
Jon, Can you confirm this also fixes 97461(https://bugs.mysql.com/bug.php?id=97461)? Thanks.
[18 Nov 2019 13:18]
MySQL Verification Team
For any checking, we will have to wait that 8.0.19 is first closed for any further patches.