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:
None 
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
Description:
This bug is first asked at https://stackoverflow.com/questions/58156882/why-why-does-not-work-in-this-sql-query.

I have a query against the sakila database in MySQL 8(You can easily setup one from https://hub.docker.com/r/1maa/sakila).

This query contains a '=' condition and complex subqueries. When I replace a subquery with a seemingly equivalent one, the result changes. If I replace '=' with 'in' in the original query, it returns the second result again.

I say 'equivalent' in the sense that:

A query in form:
```
select a from t
```
should replace transparently by:
```
select a from (
	select * from t
)
```

In the actual query, t involves a where clause referencing a table alias at toplevel, but I think it should not affect.

How to repeat:
First setup a sakila db on MySQL 8.0.14 or above.

Then run following query:
```
select * from store _0 where (
    select address_id from (
        select * from address _3 where (
            address_id = (
                select address_id from (
                    select * from store _1
                    where store_id=_0.store_id
                ) _2
            )
        )
    ) _4
);
```

The result is an empty table.

Then replace the innermost
```
                select address_id from (
                    select * from store _1
                    where store_id=_0.store_id
                ) _2
```
with
```
                select address_id from store _1
                where store_id=_0.store_id
```
and run again. This time it returns 2 rows, which is expected.

Undo the replace, and replace `=` with `in` in `address_id = ..` condition, it returns 2 rows again.
[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.