MySQL Bugs: #117132: drived_merge optimization error

Bug #117132	drived_merge optimization error
Submitted:	8 Jan 8:18	Modified:	8 Jan 10:08
Reporter:	gang chen (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When nondeterministicfunctions are used in derived tables, the drived_merge configuration will result in incorrect query results

How to repeat:
with a as (
select sysdate() as val
from (values row(1)) a)
select 
a.val,sleep(1),a.val
from a;

with a as (
select uuid() as val
from (values row(1)) a)
select 
a.val,a.val
from a;

with a as (
select rand() as val
from (values row(1)) a)
select 
a.val,a.val
from a;

The two val values in the above query are not equal

with a as (
select sysdate() as val
from (values row(1)) a)
select  /*+ set_var(optimizer_switch='derived_merge=off')*/
a.val,sleep(1),a.val
from a;

with a as (
select uuid() as val
from (values row(1)) a)
select  /*+ set_var(optimizer_switch='derived_merge=off')*/
a.val,a.val
from a;

with a as (
select rand() as val
from (values row(1)) a)
select /*+ set_var(optimizer_switch='derived_merge=off')*/
a.val,a.val
from a;

The two val values in the above query are not equal

Suggested fix:
Do not automatically merge when there are nondeterministicfunctions in the derived table

I accidentally wrote it wrong, but after adding set_var hint, the result is correct.

Thank you for confirming.
Closing the report as per your last comment.

regards,
Umesh

I think the official should prevent drived_merge when non deterministic functions are included in the derived table, rather than requiring developers to write hints

I think the official should prevent drived_merge when non deterministic functions are included in the derived table, rather than requiring developers to write hints

I think the official should prevent drived_merge when non deterministic functions are included in the derived table, rather than requiring developers to write hints

I think the official should prevent drived_merge when non deterministic functions are included in the derived table, rather than requiring developers to write hints

I think the official should prevent drived_merge when non deterministic functions are included in the derived table, rather than requiring developers to write hints

I think the official should prevent drived_merge when non deterministic functions are included in the derived table, rather than requiring developers to write hints

I think the official should prevent drived_merge when non deterministic functions are included in the derived table, rather than requiring developers to write hints

I think the official should prevent drived_merge when non deterministic functions are included in the derived table, rather than requiring developers to write hints

I think the official should prevent drived_merge when non deterministic functions are included in the derived table, rather than requiring developers to write hints