Bug #97461 Wrapping `select (value)` with `select * from` gives wrong result
Submitted: 3 Nov 14:51 Modified: 5 Nov 8:31
Reporter: Lu Jack Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.14+ OS:Any
Assigned to: CPU Architecture:Any

[3 Nov 14:51] Lu Jack
Description:
This bug is first asked at https://stackoverflow.com/questions/58673194/is-this-a-mysql-bug.

This bug may be related to another bug I reported earlier: https://bugs.mysql.com/bug.php?id=97063.

I was running this query against sakila database on MySQL 8.0.17:

```
select * from film _1 where (
	exists (
		select * from film_actor where (
			select * from (
				select film_id=_1.film_id and actor_id=1
			) _2
		)
	)
);
```

This gives `0` results. But if I run this:

```
select * from film _1 where (
	exists (
		select * from film_actor where (
			select film_id=_1.film_id and actor_id=1
		)
	)
);
```

I got `19` results, which is expected.

Basically I wrapped the `where` condition with `select * from` and suddenly got the wrong result.

To my understanding, wrapping any table value with `select * from` should give identical result.

How to repeat:
I've created this fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1bfd092299983f558ecffda049803de3.
[5 Nov 8:31] Guilhem Bichot
I confirm: it's a duplicate of bug 97603 (same cause, same fix).
[5 Nov 13:09] Sinisa Milivojevic
Thank you, Guilhem !!!!