MySQL Bugs: #97063: Wrong behavior of '=' condition when working with complex subqueries

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

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.

A fiddle that demos this issue: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=06fd5558a7cba4073816292d1eb143d8.

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.

fix version: 8.14.0+->8.0.14+

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.

Hi Mr. Jack,

Thank you for your feedback.

This is indeed a genuine Optimiser bug.

Verified as reported !!

Sinisa Milivojevic, 

Glad to know it's not my own mistake to bother you.

Thank you for your really quick confirmation!

You are truly welcome .....

Will the bug be fixed in 8.0.18 release?

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.

OK I got it. Thanks.

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.

Thank you, Jon .....

Jon,

Can you confirm this also fixes 97461(https://bugs.mysql.com/bug.php?id=97461)?

Thanks.

For any checking, we will have to wait that 8.0.19 is first closed for any further patches.