MySQL Bugs: #97461: Wrapping `select (value)` with `select * from` gives wrong result

Bug #97461	Wrapping `select (value)` with `select * from` gives wrong result
Submitted:	3 Nov 2019 14:51	Modified:	5 Nov 2019 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

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.

I confirm: it's a duplicate of bug 97603 (same cause, same fix).

Thank you, Guilhem !!!!