Bug #117132 drived_merge optimization error

Submitted: 8 Jan 2025 8:18 Modified: 8 Jan 2025 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

[8 Jan 2025 8:18] gang chen
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
[8 Jan 2025 8:21] gang chen
I accidentally wrote it wrong, but after adding set_var hint, the result is correct.
[8 Jan 2025 9:19] MySQL Verification Team
Thank you for confirming.
Closing the report as per your last comment.

regards,
Umesh
[8 Jan 2025 10:08] gang chen
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
[8 Jan 2025 10:08] gang chen
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
[8 Jan 2025 10:08] gang chen
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
[8 Jan 2025 10:08] gang chen
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
[8 Jan 2025 10:08] gang chen
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
[8 Jan 2025 10:08] gang chen
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
[8 Jan 2025 10:08] gang chen
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
[8 Jan 2025 10:08] gang chen
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
[8 Jan 2025 10:08] gang chen
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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.059 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.